====== 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. 

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

<WRAP notice>
You MUST use a chain cert.
</WRAP>

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

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

<code>
-----BEGIN PRIVATE KEY-----
</code>

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

<code bash>
openssl rsa -in pkcs8-key.pem -out pkcs1-key.pem
</code>

You should now see:

<code>
-----BEGIN RSA PRIVATE KEY-----
</code>

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:

<code bash>
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
</code>


===== 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!

<file bash /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
</file>


<code mysql>
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                      |
+---------------+-------------------------------------------------------+
</code>

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:

<code mysql>
show variables like "secure_auth";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| secure_auth   | ON    |
+---------------+-------+
</code>

Make sure you’re granting the correct permissions:

<code mysql>
GRANT REPLICATION SLAVE ON *.* TO slave_user@slave.example.net IDENTIFIED BY 'SecretPassw0rd' REQIURE SSL;
</code>

You should have master_ssl set to 1:

<code mysql>
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'
</code>