Enabling SSL with MySQL Replication in CentOS 6 / RHEL 6

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.


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.

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.

In your MySQL configuration file, add the lines below in the [mysqld] block.

Restart MySQL on the master.

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!

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 =)

 

Leave a Reply