This is an old revision of the document!

MySQL - Troubleshoot MySQL SSL Replication Problems

If errors like the following are seen:

ERROR 2026 (HY000): SSL connection error: protocol version mismatch
ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation

Mismatches usually because you're trying to authentication with your client certificates. Using the –ssl-ca flag is sufficient.

mysql -utransmed_app -p --ssl-ca=/etc/mysql-ssl/chain-cert.cer -h dest.example.com

You MUST use a chain cert.

ERROR 2003 (HY000): Can’t connect to MySQL server on 'example.com' (111)

Some MySQL selections don't support the PKCS#8 format.

—–BEGIN PRIVATE KEY—– This occurs when keys are generated with OpenSSL 1.0+. To fix this issue you simply convert the key to PKCS#1 format:

1 openssl rsa -in pkcs8-key.pem -out pkcs1-key.pem You should now see:

1 —–BEGIN RSA PRIVATE KEY—– Keep in mind you can’t just simply insert “RSA” into the PKCS#8 format. It won’t work! They’re different formats altogether. You can verify the certs/keys:

1 openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem Additional troubleshooting tips: Make sure both servers have SSL enabled. Make sure the master_ssl_ca has the entire CA chain or it won’t work!

1 2 3 4 # /etc/my.cnf ssl-ca = /etc/mysql-ssl/chain-cert.pem ssl-cert = /etc/mysql-ssl/STAR_example_net.pem ssl-key = /etc/mysql-ssl/wildcard-cert.pem

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> show variables like “%ssl%”; +—————+——————————————————-+

Variable_name

Value

+—————+——————————————————-+

have_openssl	YES
have_ssl	YES
ssl_ca	/etc/mysql-ssl/COMODO-chained.pem
ssl_capath
ssl_cert	/etc/mysql-ssl/STAR_example_net.pem
ssl_cipher
ssl_crl
ssl_crlpath
ssl_key	/etc/mysql-ssl/wildcard-cert.pem

+—————+——————————————————-+ If you run into this error: “Slave failed to initialize relay log info structure from the repository” you just need to run “RESET SLAVE;” Make sure your firewalls have Port 3306 (or whatever port you’re using) open. Make sure secure_auth is on:

1 2 3 4 5 6 show variables like “secure_auth”; +—————+——-+

Variable_name

Value

+—————+——-+

secure_auth

ON

+—————+——-+ Make sure you’re granting the correct permissions:

1 GRANT REPLICATION SLAVE ON *.* TO slave_user@slave.example.net IDENTIFIED BY 'SecretPassw0rd' REQIURE SSL; You should have master_ssl set to 1:

1 2 3 4 5 6 7 8 9 10 change master to

master_host='master.example.com', 
master_user='slave=user',
master_password='SecretPassw0rdr',
master_log_file='mysql-bin.000297', 
master_log_pos=601743376, 
master_ssl=1,
master_ssl_ca='/etc/mysql-ssl/cert-chain.pem',
master_ssl_cert='/etc/mysql-ssl/STAR_example_net.pem',
master_ssl_key='/etc/mysql-ssl/wildcard-cert.pem'