Configuring MySQL over SSL for client connections

Useful for setting up encrypted connections between client and server.  Parts of this can be substituted if you wished to get a certificate issued from a trusted CA.

1. Check that SSL has been compiled in MySQL on the server:

SHOW VARIABLES LIKE ‘have_openssl’;
or
mysql –ssl –help
If it says disabled or yes then its fine.  If it says no then an SSL enabled version of MySQL needs to be installed.

2. Create your own Certification Authority (CA) if you do not already have one:

openssl req -x509 -new -days 9999 -newkey rsa:2048 -nodes \
-keyout ca-key.pem -out ca-cert.pem
Don’t use the same common name for the CA and the server/client certs or you will run into problems

3. Create the server certificate request

openssl req -new -newkey rsa:2048 -nodes -keyout server-key.pem -out server-csr.pem

(optional) Remove the passphrase from the key

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

Sign this server request with the CA key to make a proper server certificate.

openssl x509 -req -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial \
-CAserial ca-srl.txt -in server-csr.pem -out server-cert.pem

4. Create the client certificate request

openssl req -new -newkey rsa:2048 -nodes -keyout client-key.pem -out client-csr.pem

(OPTIONAL) Remove a passphrase from the key

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

Sign this server request with the CA key to make a proper server certificate

openssl x509 -req -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial \
-CAserial ca-srl.txt -in client-csr.pem -out client-cert.pem

5. Add the server certificates to my.cnf and restart MySQL

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

SHOW VARIABLES LIKE ‘have_openssl’; should now show YES!

6. Copy the ca-cert.pem to the client machine.  Assuming that the appropriate remote privileges are configured, you should now be able to connect like so:

mysql –ssl –ssl-ca=/etc/mysql/cacert.pem -hhostname -uusername -p
or
mysql –ssl-cipher=DHE-RSA-AES256-SHA:AES128-SHA -hhostname -uusername -p

Don’t assume that a successful connection is encrypted.  Type \s at the MySQL prompt to check.  It should show SSL:Cipher in use is DHE-RSA-AES256-SHA or similar.

If your getting a “ERROR 2026 (HY000): SSL connection error” then there’s probably an issue with your certificate generation – maybe try again!

The MySQL man page for SSL is pretty handy to be honest.

You can leave a response, or trackback from your own site.

One Response to “Configuring MySQL over SSL for client connections”

Leave a Reply