Setting up SSL on MySQL database servers

I was trying to get SSL to work on a server. I had read up on these posts:

http://serverfault.com/questions/451487/configured-mysql-for-ssl-but-ssl-is-still-disabled
http://dba.stackexchange.com/questions/91514/mysql-ssl-setup-failed
https://www.percona.com/blog/2013/06/22/setting-up-mysql-ssl-and-secure-connections/
http://xmodulo.com/enable-ssl-mysql-server-client.html

I added my certs to the file system and added entries to my.cnf like this:

[mysqld]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

However, SSL still wasn't working. Looking at the error log I kept seeing this:

2017-01-10 11:46:45 8738 [Warning] Failed to setup SSL
2017-01-10 11:46:45 8738 [Warning] SSL error: SSL_CTX_set_default_verify_paths failed
2017-01-10 11:46:45 8738 [Note] RSA private key file not found: /path/to/mysql//private_key.pem. Some authentication plugins will not work.
2017-01-10 11:46:45 8738 [Note] RSA public key file not found: /path/to/mysql//public_key.pem. Some authentication plugins will not work.

When I log into MySQL and show variables related to SSL, I would see this:

mysql> show global variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | DISABLED                       |
| have_ssl      | DISABLED                       |
| ssl_ca        | /path/to/ca.pem                |
| ssl_capath    |                                |
| ssl_cert      | /path/to/server-cert.pem       |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /path/to/server-key.pem        |
+---------------+--------------------------------+
9 rows in set (0.00 sec)

Some one suggested checking that the MySQL user can correctly view the file like this:

sudo -u mysql cat /path/to/ca.pem

That command was successful on older servers but not on my new servers. I checked the permissions on the certs and those looked fine. Finally I checked the permissions on the directory which the certs were in. That was the problem!

I ran this to correct:

chown -R mysql:mysql /path/to/security/
chmod -R 644 /path/to/security/
chmod 755 /path/to/security/

Restarted MySQL.

Then when I tried to logon, I started getting this error:

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

Read up on that here:

http://stackoverflow.com/questions/31413031/mysql-error-2026-hy000-ssl-connection-error-error00000001lib0func0re

I told the client to skip ssl like this:

mysql -uUSERNAME -p  --skip-ssl

This worked but I actually didn't want to have to do that so I removed these entries from my.cnf and restarted:

[client]
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem


mysql> show global variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /path/to/ca.pem                |
| ssl_capath    |                                |
| ssl_cert      | /path/to/server-cert.pem       |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /path/to/server-key.pem        |
+---------------+--------------------------------+

Works!