We recently began using MySQL replication to handle our database backups. The reason we aren’t able to use mysqldump is because of the sheer size of our database. Our database consists of close to 1 million tables. Percona XtraBackup is able to back this up efficiently, but takes quite a while to run and causes a lot of disk io on the production server.
The replicated instance resides on a separate physical server in a separate physical location. Everything works perfectly, except one thing… All the data is being transmitted in plain text! This document will walk you through installing SSL support on your server. It assumes you already have a master-slave setup running or know how to set one up. We are using CentOS 6.5 64bit with MySQL 5.6.10.
We will begin by creating a new self-signed CA certificate. As of this post, the requirement for new SSL is 2048bit. Anything 1024bit or less is considered to be obsolete. Once you run the second command, you will be prompted for some basic information about your company.
openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
Next, we will create two sets of keys – one set for the master and one for the slave. This is done in three steps. First we create the request, remove the password, then sign it with our CA certificate from step 1.
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem openssl rsa -in server-key.pem -out server-key.pem openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem openssl rsa -in client-key.pem -out client-key.pem openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
On the master server, you will need to define the certificate files in the my.cnf configuration file. I recommend creating a directory /etc/mysql and placing them in there. Optimally, this folder should only be accessible by the mysql user to ensure security of your certificates.
mkdir /etc/mysql chown mysql:mysql /etc/mysql chmod 700 /etc/mysql vim /etc/my.cnf
In your MySQL configuration file, add the lines below in the [mysqld] block.
[mysqld] ssl-ca=/etc/mysql/ca-cert.pem ssl-cert=/etc/mysql/server-cert.pem ssl-key=/etc/mysql/server-key.pem
Restart MySQL on the master.
service mysql restart
Finally, it’s time to update the slave and start the replication process. Since this guide assumes you already have replication running, use “stop slave “command to stop the replication. If you just completed your replication setup and it’s not yet running, parts of this won’t apply to you. Next, use the “show slave status” command to grab the filenames and positions of both the binary logs and the relay logs. Next, you’ll want to issue your “change master” command to enable SSL. As before, recommend placing your SSL client certifies in /etc/mysql. The example below shows the CHANGE MASTER command we used. Be sure to edit the filenames and log positions to match your instance!
mkdir /etc/mysql chown mysql:mysql /etc/mysql chmod 700 /etc/mysql
STOP SLAVE; SHOW SLAVE STATUS; CHANGE MASTER TO MASTER_HOST='192.168.0.102', MASTER_USER='replicate', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysqld-log-bin.000041', MASTER_LOG_POS=529549091, RELAY_LOG_FILE = 'backup-relay-bin.000007', RELAY_LOG_POS = 399786118, MASTER_SSL=1, MASTER_SSL_CA='/etc/mysql/ca-cert.pem', MASTER_SSL_CERT='/etc/mysql/client-cert.pem', MASTER_SSL_KEY='/etc/mysql/client-key.pem';
Now you’re ready to begin replication again and ensure it’s working. As long as you have a strong SSL certificate, there’s no reason to worry about the NSA sniffing your packets =)
START SLAVE; SHOW SLAVE STATUS;