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 simply convert the key to PKCS#1 format:
openssl rsa -in pkcs8-key.pem -out pkcs1-key.pem
You should now see:
-----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:
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!
- /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
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:
show variables like "secure_auth"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | secure_auth | ON | +---------------+-------+
Make sure you’re granting the correct permissions:
GRANT REPLICATION SLAVE ON *.* TO slave_user@slave.example.net IDENTIFIED BY 'SecretPassw0rd' REQIURE SSL;
You should have master_ssl set to 1:
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'