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;