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.
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=MyProdDB1pmm-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=$serverUnsuccessful 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_IDENTITYSuccessful 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=perfschemaConnection 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 -tlsTLS 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.crtTLS 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.pemRegister 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