MySQL SSL required connection Ubuntu solutions

I went to implement MySQL replication for a client this evening and ran into some interesting issues that I haven't ran into before. Guess it has been a while since I had to set it up for a client.   So this post is for notes for me or someone else who might need to do this in the future. The normal installation replication installation works great but if you are going to enable ssl connections this is where the things can get a bit more complex.

The first thing to find out is if you have your SSL setup correct, try doing:
And verify the SSL is enabled and build in, in my case everything looked good:

mysql> SHOW VARIABLES LIKE '%ssl%';

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_key       = /etc/mysql/certs/server-key.pem\";

This looks correct, so the next thing to figure out is where the error log file is located;

mysql> SHOW VARIABLES LIKE '%error_log%';

log_error     =  ./mysql-bin.err
or something like
log_error     = /var/log/mysql/error.log

Now that you know where the error log is at you can see why it is failing.

In my case the error was this:

2016-12-06 23:21:33 32695 [Warning] Failed to setup SSL
2016-12-06 23:21:33 32695 [Warning] SSL error: SSL_CTX_set_default_verify_paths failed

I love that it is a "Warning".   It is totally broken, but we will list it as a Warning...

Well, this can be caused by several things:

1. No permissions to the files in the folder, use chmod/chown to give perms.
2. SELinux blocking it, disable selinux or grant permissions via SELinux
3. AppArmor blocking it.  (this was my case)

Edit the /etc/apparmor.d/usr.sbin.mysqld file.

You'll see something like this in the file:

/etc/mysql/*.pem r,
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/* r,
/etc/mysql/*.cnf r,
---> /etc/mysql/certs/*.pem r,  <---
/usr/lib/mysql/plugin/ r,

Add the ---> line <---, make sure it matches your path to where you are storing the certs.  Then restart mysql. After restarting the server, I then got this error: SSL error: Unable to get private key from '/etc/mysql/certs/server-key.pem' 2016-12-06 23:53:32 21728 [Warning] Failed to setup SSL 2016-12-06 23:53:32 21728 [Warning] SSL error: Unable to get private key Ok, this one threw me for a while.  The files are fully readable by MySQL.  The issue ends up being incompatibilities between SSL libraries in use.  OpenSSL 1.0x vs yaSSL The key file will start like this:

-----BEGIN PRIVATE KEY-----

If you used OpenSSL to generate the keys;  OpenSSL creates keys in PKCS#8 with a SHA256 digest.  Of course yaSSL which is (normally) used by MySQL doesn't support either, and want PKCS#1.  So despite having the files fully readable, MySQL is telling you it can't figure out how to "get the private key" out of the file.  Once you know the issue, it has a simple solution:

openssl rsa -in server-key.pem -out server-key.pem

when you are done with this command the beginning of the file should look like this:

-----BEGIN RSA PRIVATE KEY-----

Again, the internal format is different, so don't try and just change the text and insert the "RSA" into it -- it will look like it works until something try's to connect using SSL.

Once you have this done, restart mysql again and you should be good to go.