[MySQL] Replication over SSL

Prerequisites

• Mysqld 5.1 or superior
• Openssl 0.9.8e or superior
• an already running mysql replication

Check SSL support

mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.01 sec)

DISABLED means mysqld has ssl support but it’s just not enabled. If you have NO instead then you don’t have SSL support.

Generate certificates

Create a /etc/mysql/certs directory on both master and slave node.

Generate a key and a CA certificate:

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

Generate a server key and certificate:

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

And finally a client key and certificate:

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

Copy ca-cert.pem, client-cert.pem and client-key.pem to the slave node.

Mysqld configuration

On the master node add into /etc/mysql/my.cnf into the [mysqld] section:

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

Restart mysqld.

On the slave node, add the following lines:

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

Restart mysqld.

Last step: tell to replication process to use exclusively a SSL connection:

GRANT USAGE ON *.* TO 'slave_user'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;