mysql master master replication with ssl encryption

Lets assume the two servers to be serverA and serverB

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Note:

The common name must be different for two certificates, or it will not work.

Create the Certificates for ServerA :

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout servera-key.pem > servera-req.pem

openssl x509 -req -in servera-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > servera-cert.pem

Now Create the Certificates for ServerB:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout serverb-key.pem > serverb-req.pem

openssl x509 -req -in serverb-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > serverb-cert.pem

Copy ca-* and each server’s respective keys  to each server.

Now Configure each Server:

ServerA:

GRANT REPLICATION SLAVE ON . TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;

If the user already exists:

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

Flush privileges;

Add the following parameters under [mysqld] section of /etc/my.cnf

vi /etc/my.cnf

ssl ssl-ca=/path/to/ca-cert.pem ssl-cert=/path/to//servera-cert.pem ssl-key=/path/to//servera-key.pem

ServerB:

GRANT REPLICATION SLAVE ON . TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL; Flush privileges;

**Add the following parameters under [mysqld] section of my.cnf :**

``

vi /etc/my.cnf ssl ssl-ca=/path/to/ca-cert.pem ssl-cert=/path/to/serverb-cert.pem ssl-key=/path/to//serverb-key.pem

Adjust your other replication parameters as usual, or see my post for mysql master master replication for information on how to setup master/master replication

Now on each server run the following command on mysql console :

CHANGE MASTER TO MASTER_HOST=’master_ip’, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE=’log_file_name’, MASTER_LOG_POS=’master_log_position’, MASTER_SSL=1, MASTER_SSL_CA = ‘/path/to/ca-cert.pem’, MASTER_SSL_CERT = ‘/path/to/servera(b)-cert.pem’, MASTER_SSL_KEY = ‘/path/to/servera(b)-key.pem’;

start slave; show slave status\G;

You now have the ssl encrypted mysql replication.

**Issues & Troubleshooting : **

The main issue with SSL based replication / connection is the problems with certificates. You should carefully follow the steps to make sure that SSL replication  works. In case the replication does not work , here are few things to check :

- Make sure SELINUX is disabled , or configure it to let mysql have ssl enabled.
- Verify that MySQL has SSL Enabled  You  can verify that by :

 mysql –e “show variables like ‘%ssl%’”

- Use packet capture utility like ngrep to verify that there is no SSL handshake problem. If you see bad Handshake in the capture, your SSL Certificates are not right.

For any other issues, or suggestions please shoot me an email at [amit@amitnepal.com](mailto:amit@amitnepal.com)  and I will get back to you at my earliest convenience.

Thank you

Amit