Home MySQL Encrypted Connections
Post
Cancel

MySQL Encrypted Connections

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[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.

1
sudo service mysql restart

Verify Connections

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

1
2
3
4
5
# Log in to MySQL
mysql -u root -p

SHOW VARIABLES LIKE '%ssl%';
STATUS;
This post is licensed under CC BY 4.0 by the author.