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 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;