SSL in MySQL connections

Last week I wanted to figure out how to use SSL certificates in MySQL connections. This is well-documented on the MySQL Web site, but here are a few wrinkles I experienced while figuring out how to get this working (this was with MySQL5 on CentOS5 and RHEL5).

After creating the certificate authority (CA) certificate/keyfile pair, you can specify them in the mysqld section of /etc/my.cnf:

ssl-ca=/etc/pki/CA/ca-cert.pem
ssl-cert=/etc/pki/tls/certs/mysql-server-cert.pem
ssl-key=/etc/pki/tls/private/mysql-server-key.pem

When making certificates for a client connecting locally (e.g., ssluser@localhost), it's important to supply localhost as the "common name" when prompted by openssl. (Yes, it's probably pretty silly to use SSL for a connection over the loopback interface, but you might be in this situation if you were testing.)

If you want to specify the CA (whose signature must appear in client certificates) when setting up a MySQL user (as you might when using the require x509 syntax), the fields should be separated by backslashes ('/'): grant usage on *.* to ssluser@localhost require issuer '/C=GB/ST=Berkshire/L=Newbury/O=My Company Ltd/CN=www.example.com/emailAddress=webmaster@example.com';
The following command will more-or-less correctly format the issuer for the grant statement:

openssl x509 -text -in /path/to/ca-cert.pem | grep Issuer \
| cut -d':' -f2 | sed -e 's/, /\//g'

Using issuer and subject items imply x509, and it's an error to try using x509 and issuer.

Depending on the require clause in the grant statement, you can use one or more of the following to connect to the SSL-enabled server:

1. mysql -u ssluser -p


2. mysql -u ssluser --ssl-ca=ca-cert.pem -p


3. mysql -u ssluser --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -p

If you use require none or omit the require clause, you can use any of the three connection commands. If you use require ssl, you can use #2 or #3. And if you use require x509, you have to use #3 (note that #3 includes the --ssl-ca option). After connecting, type status (or just \s) and make sure that the SSL item says something encryptiony (mine says Cipher in use is DHE-RSA-AES256-SHA).

Unless client certificates are really necessary (extra client-level authentication), it's probably adequate just to use require ssl and to have the client provide the CA certificate (this appears to provide as high a level of encryption as the client certificate does). But note that you still need to generate the server certificate and key, even if you're not using client certificates.