Chris on MySQL

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=

Connection Output:

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.

Tags: batch, how to set up ssl for mysql, how to setup ssl for multiple servers, install openssl unattended, MySQL SSL, openssl, openssl -batch, setup ssl for multiple mysql instances, SSL, unattended openssl

This entry was posted on Wednesday, January 25th, 2012 at 7:05 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.