Configure PMM2 for Azure MySQL Database with SSL

Managed database systems comes with their own quirks. I recently encountered one for Azure MySQL. Although solution was simple, it took significant time. Sharing so that one’s time can be saved.

Azure MySQL with SSL

Registering AzureDB without SSL in PMM

If you are registering normal mysql database you will run below command & it will be registered.

server=db-server.mysql.database.azure.com
monitoruser=monitor_mysql@db-server
monitorpwd=xxxxxxxx
server_name=MyProdDB1
pmm-admin add mysql --username=$monitoruser --password=$monitorpwd --host=$server --service-name=$server_name --query-source=perfschema

DB connectivity with ssl

As per the link provided below you can download the generic certificate to connect to the azure database. Using that you can connect to mysql even with verify_ca mode too.

Successful connection with SSL
mysql --user=$monitoruser --password=$monitorpwd --host=$server
Unsuccessful connection with verify ca SSLmysql --user=$monitoruser --password=$monitorpwd --host=$server --ssl-mode=VERIFY_CA
ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY
Successful connection with verify ca SSL
mysql --user=$monitoruser --password=$monitorpwd --host=$server --ssl-mode=VERIFY_CA --ssl-ca=azure-ca.crt

Error Registering SSL DB

for SSL you need to supply the parameter -tls, however that also throw error.

Error trying to connect without SSL
pmm-admin add mysql --username=$monitoruser --password=$monitorpwd --host=$server --service-name=$server_name --query-source=perfschema
Connection check failed: Error 9002: SSL connection is required. Please specify SSL options and retry..Error trying to connect with SSL
pmm-admin add mysql --username=$monitoruser --password=$monitorpwd --host=$server --service-name=$server_name --query-source=perfschema -tls
TLS is on. You must also define tls-ca, tls-cert and tls-key flags.Error trying to connect with SSL & azure provided certificate
pmm-admin add mysql --username=$monitoruser --password=$monitorpwd --host=$server --service-name=$server_name --query-source=perfschema -tls --tls-ca=azure-ca.crt
TLS is on. You must also define tls-ca, tls-cert and tls-key flags.

Generate new SSL

Based on bug raised, I found that we need ssl client key & client certificate generated seperately. I used below command to generate new files. I have highlighted the one I used later.

mysql_ssl_rsa_setup --datadir ssl/
ls ssl/
-rw------- 1 nirav nirav 1679 Jun 17 14:52 ca-key.pem
-rw-r--r-- 1 nirav nirav 1107 Jun 17 14:52 ca.pem
-rw-r--r-- 1 nirav nirav 1107 Jun 17 14:52 client-cert.pem
-rw------- 1 nirav nirav 1679 Jun 17 14:52 client-key.pem
-rw------- 1 nirav nirav 1675 Jun 17 14:52 private_key.pem
-rw-r--r-- 1 nirav nirav 451 Jun 17 14:52 public_key.pem
-rw-r--r-- 1 nirav nirav 1107 Jun 17 14:52 server-cert.pem
-rw------- 1 nirav nirav 1679 Jun 17 14:52 server-key.pem

Register Azure DB with SSL

Now same above command with client key files working successfully.

Successful register with SSL & azure provided certificate
pmm-admin add mysql --username=$monitoruser --password=$monitorpwd --host=$server --service-name=$server_name --query-source=perfschema -tls --tls-ca=azure-ca.crt --tls-cert=client-cert.pem --tls-key=client-key.pem

Reference:

Working as Cloud Architect & Software enthusiastic