Managing SSL certificates for MySQL can be somewhat of a pain, especially when setting it up on multiple machines.
I looked around on the web, and didn’t really run into any mentions of how to do this for multiple machines, so I dug into it a bit more.
If you’ve created the certificates before, you’ll know you get prompted for various bits of information during a couple of the steps (country, state, email).
However, this can be by-passed by using the -batch option with the openssl command.
So, to set this up for multiple servers, just loop through the following 5 commands for your servers:
cd C:\mysql\certs openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 3600 -batch -key ca-key.pem > ca-cert.pem openssl req -newkey rsa:2048 -days 3600 -batch -nodes -keyout server-key.pem > server-req.pem openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
This will create the following 5 files:
ca-cert.pem, ca-key.pem, server-cert.pem, server-key.pem, and server-req.pem
Those are all that is needed to set up SSL for MySQL.
Then, just add the following lines to the [mysqld] section of your my.cnf/my.ini files:
ssl-ca = C:\mysql\certs\ca-cert.pem ssl-cert = C:\mysql\certs\server-cert.pem ssl-key = C:\mysql\certs\server-key.pem
Ensure you have an SSL user created, and then start up mysqld.
You can connect with the following command:
mysql -ussluser -p --ssl-key=
mysql> status; -------------- mysql Ver 14.14 Distrib 5.5.16, for Win32 (x86) Connection id: 1 Current database: Current user: ssluser@localhost SSL: Cipher in use is DHE-RSA-AES256-SHA Using delimiter: ; Server version: 5.5.16-log MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: cp850 Conn. characterset: cp850 TCP port: 3430 Uptime: 8 sec Threads: 1 Questions: 4 Slow queries: 0 Opens: 33 ... --------------
Hope this helps.