MySQL Encrypted Connections

This guide will generate a certificate authority plus client and server keys for encrypting traffic to a MySQL server.

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

# Generate server cert. Should have different settings from CA
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Generate client cert. Should have different settings from CA
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# Verify all is in working order
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

# Ensure permissions are set
chown -R mysql:mysql *.pem
chmod 600 client-key.pem server-key.pem ca-key.pem

Edit Config #

Add to /etc/mysql/my.cnf

[mysqld]
bind-address = 0.0.0.0

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

# Requires encrypted connections globally
# require_secure_transport = ON


[client]
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/client-cert.pem
ssl-key=/etc/certs/client-key.pem

Restart the server for changes to take effect.

sudo service mysql restart

Verify Connections #

You should see SSL settings be on and the certificate paths being used in the below commands.

# Log in to MySQL
mysql -u root -p

SHOW VARIABLES LIKE '%ssl%';
STATUS;