MySQL SSL setup

2014-04-25T10:53:00Z.

The traffic between MySQL client and MySQL server is not encrypted by default, which is not acceptable for applications that handle sensitive data such as login credentials and monetary transaction details. You can configure MySQL programs to make use of SSL to protect the data being transmitted between the client and server.

This article covers configuring MySQL client (mysql) and MySQL server (mysqld) to make use of SSL.

Make sure you have OpenSSL installed on the computer.

Generate CA key and certificate

First generate CA key and certificate, which will be used to sign keys and certificates of server and client:


mkdir -p /etc/mysql/certificates

cd /etc/mysql/certificates

openssl genrsa 2048 > mysql-ca-key.pem

openssl req -new -x509 -nodes -days 3600 -key mysql-ca-key.pem \
-out mysql-ca-cert.pem

Note: When generating keys and certificates of CA, server and client, make sure the Common Name values are different, otherwise OpenSSL would complain.

Generate and sign server key and certificate


openssl req -newkey rsa:2048 -days 3600 -nodes \
-keyout mysql-server-key.pem -out mysql-server-req.pem

openssl rsa -in mysql-server-key.pem -out mysql-server-key.pem

openssl x509 -req -in mysql-server-req.pem -days 3600 \
-CA mysql-ca-cert.pem -CAkey mysql-ca-key.pem -set_serial 01 \
-out mysql-server-cert.pem

Generate and sign client key and certificate


openssl req -newkey rsa:2048 -days 3600 -nodes \
-keyout mysql-client-key.pem -out mysql-client-req.pem

openssl rsa -in mysql-client-key.pem -out mysql-client-key.pem

openssl x509 -req -in mysql-client-req.pem -days 3600 \
-CA mysql-ca-cert.pem -CAkey mysql-ca-key.pem -set_serial 01 \
-out mysql-client-cert.pem

Verify generated certificates


openssl verify -CAfile mysql-ca-cert.pem mysql-server-cert.pem \
mysql-client-cert.pem

Configure MySQL server SSL options

Open MySQL configuration file /etc/mysql/my.cnf, edit the server configuration to add the following lines under [mysqld] section:


ssl-ca=/etc/mysql/certificates/mysql-ca-cert.pem

ssl-cert=/etc/mysql/certificates/mysql-server-cert.pem

ssl-key=/etc/mysql/certificates/mysql-server-key.pem

Configure MySQL client SSL options

Open MySQL configuration file /etc/mysql/my.cnf, edit the client configuration to add the following lines under [client] section:


ssl-ca=/etc/mysql/certificates/mysql-ca-cert.pem

Make sure the CA certificate is readable by user who will run mysql program to connect to MySQL server.

Restart MySQL server and client. Now MySQL server accepts connection over SSL and MySQL client connects to MySQL server over SSL by default.

Home / Articles