MySQL replication is setup already. Now I need to add SSL so the communication between server and client will be secure.

1. generate CA certificate and server key/cert on Master

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

Server Key/Cert
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem -out server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

2. enable SSL on master
add the following to both [client] and [mysqld]

ssl-ca=/var/lib/mysql/ssl/ca-cert.pem
ssl-cert=/var/lib/mysql/ssl/client-cert.pem
ssl-key=/var/lib/mysql/ssl/client-key.pem

3. generate client key/cert on Slave

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

4. enable SSL on slave
repeat 2, but on slave server and using keys/cert of slave, restart db with skip-slave-start

5. start slave

mysql>change master to master_host=’masterdb’, master_user=’repliuser’, master_password=’pass’,master_log_file=’db1-bin.xxxxx’, master_log_pos=98, MASTER_SSL=1, MASTER_SSL_CA=’ssl/ca-cert.pem’;
start slave

6. setup User permission to use SSL only
User “GRANT” to setup user allow SSL connection only

Check if SSL is on in mysqld

mysql> show variables like ‘%have_ssl%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_ssl | YES |
+—————+——-+

Check if Client is using SSL:

>mysql
SHOW STATUS LIKE ‘Ssl_cipher’;