(Resolved) MySQL connection error: certificate verify failed

The SSL connection error: error:0A000086:SSL routines::certificate verify failed error is usually encountered when establishing an SSL connection to a MySQL server. I was configuring the replication between two MySQL servers running with MySQL version 8.0. After configuring the replication, the “SHOW SLAVE STATUS” command on the slave instance shows me the following error:

Last_IO_Error: error connecting to master ‘[email protected]:3306’ – retry-time: 60 retries: 3 message: SSL connection error: error:0A000086:SSL routines::certificate verify failed

Then I tried to connect the Master server from the slave using the command line, with the client certificate. Again I received the following error with the connection:

mysql -h 192.168.1.100 -u repl_user -p --ssl-ca=/etc/mysql/certs/ca.pem --ssl-cert=/etc/mysql/certs/client-cert.pem --ssl-key=/etc/mysql/certs/client-key.pem 

Output
Enter password:
ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed

Possible Causes

This error can be occurred due to several reasons. Here are some possible causes:

• The MySQL server’s SSL certificate is not trusted by the client because it is self-signed or not signed by a certificate authority (CA) that is trusted by the client.
• The MySQL server’s SSL certificate has expired.
• The MySQL server’s SSL certificate is not properly configured.
• The client is using an old version of the MySQL client library that does not support the server’s SSL certificate.

Solution

1. Check if both system clocks are synchronized.
2. Next verify the client and server certificate with the CA file and make sure everything is OK. Use the following command to verify the certificates:

   openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem 
   
   server-cert.pem: OK
   client-cert.pem: OK
   

3. Make sure to set a different “Common Name (FQDN)” for the CA certificate and the master/client certificate.
4. Check the state of the SSL/TLS variables by typing. Make sure the correct certificate is used by the server.

   SHOW VARIABLES LIKE '%ssl%'; 
   

   Output
   +-------------------------------------+----------------------------------+
   | have_openssl                        | YES                              |
   | have_ssl                            | YES                              |
   | ssl_ca                              | /etc/mysql/certs/ca-cert.pem     |
   | ssl_capath                          |                                  |
   | ssl_cert                            | /etc/mysql/certs/server-cert.pem |
   | ssl_cipher                          |                                  |
   | ssl_crl                             |                                  |
   | ssl_crlpath                         |                                  |
   | ssl_fips_mode                       | OFF                              |
   | ssl_key                             | /etc/mysql/certs/server-key.pem  |
   | ssl_session_cache_mode              | ON                               |
   | ssl_session_cache_timeout           | 300                              |
   +-------------------------------------+----------------------------------+
   27 rows in set (0.01 sec)
   

5. Finally make sure that you are using the correct database username, hostname, and password to connect.

Conclusion

In conclusion, the `SSL connection error: error:0A000086:SSL routines::certificate verify failed error` can occur when establishing an SSL connection to a MySQL server for several reasons, including an untrusted or expired SSL certificate, a misconfigured SSL certificate, or an outdated MySQL client library. To resolve this error, you can import the server’s SSL certificate into the client’s trust store, renew the SSL certificate, check the server’s SSL configuration, or upgrade the MySQL client library to a newer version that supports the server’s SSL certificate.