Phil Bayfield

There are many posts out there on how to setup both MySQL replication and self signed SSL certificates but not really any that combine the 2 very well. This post is partly for my own reference and partly for anyone else who is interested in the subject.

To enable SSL replication both the client and server require signed certificates, these could be a purchased certificate from a common CA but really as they are only going to be used for private communication there is no need and a self signed certificate will do the job.

Please note, that for the section of this post which discusses the MySQL replication, I am assuming that you already know how to setup replication, including ensuring data is consistent on master/slave. The focus of his article is to document the additional configuration required to setup replication with SSL.

For the purpose of this example I will be configuring the following hosts:

Creating the certificates:

The first thing to do is to create a certificate authority in order to sign the certificates, this is based on the Ubuntu Documentation with a few changes.

First, create some directories to organise things and change into the new CA directory:

Create a certificate database:

Create a configuration for the CA:

You will need to change the user in red with your Linux username and the CA info in green.

Now generate the CA certificate with the following:

You will be prompted for a passphrase:

We now have a certificate and key which we can use to sign the certificates. The next stage is to create certificates for each of our 2 hosts (or more, if more than 2 hosts are being configured in the replication setup).

Create a config file for each host:

Change the bits in green to your company name, the commonName must be the host name of the machine, this is important or the certificate will be invalid.

The next step is to create a certificate signing request (CSR) and private key for each of our hosts:

You will be prompted for a passphrase again:

Repeat for slave (and additional hosts):

Now in their current form the keys we have just generated will require the passphrase every time we start the server, this is far from ideal as MySQL will have to be manually started every time the server is rebooted (intentionally or not).

We can create an unencrypted version of the keys so that the passphrase is not required for server starts:

Finally, we can sign the certificate requests for each host with our CA:

Configuring the servers:

We will need to copy 3 files to each machine:

ca.pem – The CA certificate

On the master:

master.crt.pem – The master certificate
master.key.pem or master.dckey.pem – Either the encrypted or decrypted version of the master private key

On the slave:

slave.crt.pem – The slave certificate
slave.key.pem or slave.dckey.pem – Either the encrypted or decrypted version of the slave private key

These files should be copied into /etc/mysql on the respective hosts.

On the master, create a user for replication:

Note that the addition of the ‘REQUIRE SSL’ at the end of the grant statement, this means insecure connections from the slave will not be possible.

Create the configuration on the master for replication:

Also we need to add the SSL configuration:

Restart MySQL to apply the configuration:

or (in newer versions with upstart):

Then configure the slave for replication:

Restart MySQL, and everything should be up and running.

From the master we should see something like this:

And on the slave:

Note: The status info above I copy/pasted from a live replication setup and replaced with values that you can expect to see, values will likely differ (especially log file positions).