(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 ‘repl@192.168.1.100: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.

Possible Solutions

1. Ensure that the system clocks of both the client and server are synchronized.
2. Next, confirm that the client and server certificates are properly verified with the CA file. Utilize 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. Be certain to assign different “Common Names (FQDN)” for all the CA certificate, master ssl certificate and the client ssl certificates.
4. Examine the status of the SSL/TLS variables by entering the following command, and ensure that the server is using the correct certificate:.

   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)
   

   Review the result of the above command. Make sure that have “have_openssl” and “have_ssl” is set to “Yes”. Also insure that the “ssl_ca”, “ssl_cert” and “ssl_key” path is correctly set. These settings can be found in mysqld.conf file:

   ssl ssl-ca=/etc/mysql/certs/ca-cert.pem ssl-cert=/etc/mysql/certs/server-cert.pem ssl-key=/etc/mysql/certs/server-key.pem

   1 2 3 4

   ssl ssl-ca=/etc/mysql/certs/ca-cert.pem ssl-cert=/etc/mysql/certs/server-cert.pem ssl-key=/etc/mysql/certs/server-key.pem

5. Lastly, verify that the correct database username, hostname, and password are being used for the connection.

To get the detailed instructions about MySQL replication setup, visit: https://tecadmin.net/how-to-set-up-mysql-master-slave-replication/

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.