Setting up SSL for MySQL server

I needed to set up SSL for MySQL on RHEL because the remote backup solution I chose, MySQL-zrm requires secure connections.


Most of the information about creating self-signed certificates and keys is here on this page. You can use the script in example 2, but I discovered a couple of things:


First, the script creates both server and client certs and keys. You only need to do one or the other, not both, to have secure connections. The advantage of client certs and keys is that you can use them in GRANT statements. I chose to have only server certs and keys. So I truncated the script after the commands related to the server.


Secondly, you might want add the option -days N, where N is the number of days of validity, to the openssl req and ca commands, to suit your needs.


Finally, you need to edit the source argument in the cp command to the actual location of the template openssl.cnf on your system. For my RHEL system, it is /etc/pki/tls/openssl.cnf. For RHEL 6, you also need to edit the replace command because the CA directory in openssl.cnf has changed from ./demoCA to /etc/pki/CA.


Put the script in a directory say /etc/mysql, and run it from the directory with ./mkcert.sh. It will create a subdirectory openssl for the results. You will be prompted twice for cert attributes, once for the CA and once for the server cert.


When you have finished, add these lines to /etc/my.cnf in the mysqld section:

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

You should chown, chmod and chcon (SELinux) the contents of /etc/mysql for security. I did:

chown -R mysql:mysql /etc/mysql
chmod -R g-w,o= /etc/mysql
chcon -R -u system_u -r object_r -t mysqld_etc_t /etc/mysql

Then restart the mysql service and look in /var/log/mysqld.log for any errors re the certs. If no errors,  you can check if SSL is available with mysql:

mysql> show variables like '%ssl%';

Answer for have_ssl should be YES. If not, check the file paths.


If you need to start all over, it suffices to delete the directory /etc/mysql/openssl.