Oct 22, 2018

How to Set Up SSL Certificates and Keys for MySQL

The easier way is to use mysql_ssl_rsa_setup.

Exmaple:

SHELL> mysql_ssl_rsa_setup --datadir=/usr/db-ssl/ --verbose

The mysql_ssl_rsa_setup utility is available to make it easier to manually generate SSL/RSA certificate and key files. Autodiscovery of SSL/RSA files at startup is expanded to apply to all servers, whether compiled using OpenSSL or yaSSL. (This means that auto_generate_certs need not be enabled for autodiscovery to occur.)

Or use openssl to manually generate them:

# Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem


# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem


# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
shell> openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem


After generating the certificates, verify them:

shell> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

Add the following to my.cnf
my.cnf
[client]
ssl-ca=$DIR/ca-cert.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem
[mysqld]
ssl-ca=$DIR/ca-cert.pem
ssl-cert=$DIR/server-cert.pem
ssl-key=$DIR/server-key.pem

A client can connect securely like this:

shell> mysql --ssl-ca=ca-cert.pem
To require that a client certificate also be specified, create the account using the REQUIRE X509 option. Then the client must also specify the proper client key and certificate files or the server will reject the connection:

shell> mysql --ssl-ca=ca-cert.pem \
       --ssl-cert=client-cert.pem \
       --ssl-key=client-key.pem

To prevent use of SSL and override other SSL options, invoke the client program with --ssl=0 or a synonym (--skip-ssl, --disable-ssl):

shell> mysql --ssl=0
A client can determine whether the current connection with the server uses SSL by checking the value of the Ssl_cipher status variable. The value is nonempty if SSL is used, and empty otherwise. For example:
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)
For the mysql client, an alternative is to use the STATUS or \s command and check the SSL line:
mysql> \s
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
...
Or:
mysql> \s
...
SSL: Not in use
...