Running MySQL Replication Over SSL

This is the first part in a set of posts about setting up a DR solution for a client. First of all I had to figure out replication over SSL as the link between the data centers is outside of my control and I am paranoid 🙂

The master and slave where setup as usual, bog standard 5.5m3 installs.

Generate the SSL certs on the Master and copy them over to the slave

On the Master and the Slave

mkdir -p /etc/mysql/ssl && cd /etc/mysql/ssl

On Master

openssl genrsa 2048 > ca-key.pem

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

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

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

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

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

scp /etc/mysql/newcerts/ca-cert.pem root@
scp /etc/mysql/newcerts/client-cert.pem root@
scp /etc/mysql/newcerts/client-key.pem root@

vi /etc/my.cnf

server-id = 1
log_bin = /var/lib/mysql/mysql-bin.log

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user‘@’%’ IDENTIFIED BY ‘slave_password‘ REQUIRE SSL;
GRANT USAGE ON *.* TO ‘slave_user‘@’%’ REQUIRE SSL;

Restart MySQL

On Slave

vi /etc/my.cnf


Restart MySQL

CHANGE MASTER TO MASTER_HOST=’′, MASTER_USER=’slave_user‘, MASTER_PASSWORD=’slave_password‘, MASTER_LOG_FILE=’mysql-bin.000001’, MASTER_LOG_POS=3096416, MASTER_SSL=1, MASTER_SSL_CA = ‘/etc/mysql/ssl/ca-cert.pem’, MASTER_SSL_CERT = ‘/etc/mysql/ssl/client-cert.pem’, MASTER_SSL_KEY = ‘/etc/mysql/ssl/client-key.pem’;

Running MySQL Replication Over SSL

3 thoughts on “Running MySQL Replication Over SSL

  1. fabio says:

    Thanks for the article, my question is: How (and if is it possible) set up a replica “single master” -> “multi slave” over ssl?


  2. admin says:

    Yes just set the other slaves up the same as the first one. Basically you can do anything you would normally the only difference is that you encrypt the newtwork traffic


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s