secure MySQL-replication with ssl 1


To secure replication over ssl, mysql must support ssl.

mysql -u root -p
show variables like '%ssl%';

If the result looks like this ssl is not configured yet.

have_openssl DISABLED
have_ssl DISABLED
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key

First, the directory for the ssl-keystore is created on both servers.
mkdir -p /etc/mysql/ssl
chown mysql.mysql /etc/mysql/ssl
chmod 750 /etc/mysql/ssl

After that the certificates are created on a server. For server1 and server2 any name can be used.


cd /etc/mysql/ssl

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

openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server1-key.pem -out server1-req.pem
openssl rsa -in server1-key.pem -out server1-key.pem
openssl x509 -req -in server1-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server1-cert.pem

openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server2-key.pem -out server2-req.pem
openssl rsa -in server2-key.pem -out server2-key.pem
openssl x509 -req -in server2-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server2-cert.pem

The following files are copied to the second server to /etc/mysql/ssl:

ca-cert.pem
ca-key.pem
server2*

where server2* are not needed on server1.

Adjust the my.cnf to enable ssl.

[client]
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/etc/mysql/ssl/ca-cert.pem

[mysqld]
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server1-cert.pem
ssl-key=/etc/mysql/ssl/server1-key.pem

After restarting the mysqld show variables like '%ssl%';

have_openssl YES
have_ssl YES
ssl_ca /etc/mysql/ssl/ca-cert.pem
ssl_cert /etc/mysql/ssl/server1-cert.pem
ssl_cipher DHE-RSA-AES256-SHA
ssl_key /etc/mysql/ssl/server1-key.pem

To use for replication ssl,
STOP SLAVE;
CHANGE MASTER TO MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/ssl/ca-cert.pem', MASTER_SSL_CIPHER='DHE-RSA-AES256-SHA';
START SLAVE;

must be invoked on both servers.

SHOW SLAVE STATUS \G shows

Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/ssl/ca-cert.pem
Master_SSL_Cipher: DHE-RSA-AES256-SHA

You can also force a connection for the repliaktion-slave over ssl:
GRANT USAGE ON *.* TO 'rep_user'@'%' REQUIRE SSL;
flush privileges;


Leave a comment

Your email address will not be published. Required fields are marked *

I confirm

One thought on “secure MySQL-replication with ssl