MySQL is a popular open-source relational-database management system. One of its core features is replication, and in this article I will be showing how to configure a master and slave MySQL instance, and then configure replication from master to slave over SSL. Encryption will help protect the replication from snooping. This type of replication has many uses, for example: disaster-recovery scenarios whereby the slave can be switched to a master role in the case of a master outage, for performance where all reads can take place on the slave with writes and updates occurring on the master, and so on. Replication can be configured without encryption, but encrypting with SSL is preferred as part of a defence-in-depth strategy - it’s an extra layer of security.

This article already presumes a good working knowledge of MySQL. The master server is centosa with IP address 10.1.1.150, and is running a minimal installation of CentOS 6.4 x86_64. The slave, centosb, is running the same OS and has IP address 10.1.1.151. MySQL will be installed from the latest current stable RPMs available at dev.mysql.com, rather than using the upstream versions. The latest stable version available at the time of writing is 5.6.14.

This article will cover the configuration of an SSL-encrypted replicated environment from scratch - it does not cover the migration of an existing replicated configuration to an SSL-encrypted replicated configuration, or the migration of any existing data to a new slave.

MySQL Installation

Perform all installation steps on both servers. I downloaded the RPM bundle from dev.mysql.com: MySQL-5.6.14-1.el6.x86_64.rpm-bundle.tar. Let’s first take a look at the contents of the tar file:

The RPM files that we will install are as follows:

RPM File	Description
MySQL-shared-5.6.14-1.el6.x86_64.rpm	MySQL Shared Libraries
MySQL-devel-5.6.14-1.el6.x86_64.rpm	MySQL Development Files (headers etc.)
MySQL-client-5.6.14-1.el6.x86_64.rpm	MySQL Client
MySQL-server-5.6.14-1.el6.x86_64.rpm	MySQL Server
MySQL-shared-compat-5.6.14-1.el6.x86_64.rpm	MySQL Compatibility Libraries

I will not install the embedded or test RPMs. Unbundle the tar file to /usr/local/src:

Install the following pre-requisite packages and their dependencies with yum:

If you attempt to install now, you’ll see a bunch of RPM conflicts during the preparation phase:

The conflicts are with the stock mysql-libs package. A great deal of software is linked against the MySQL libraries, for example:

hence there are a few important dependencies if we try to remove it:

To resolve this, first force removal of the mysql-libs package using the dreaded --nodeps rpm option:

Next, install the MySQL shared libraries RPMs:

Check that dynamic linking hasn’t been broken:

You can see we are now using the correct version of the library, and so can proceed with the installation of the remaining RPMs:

As well as InnoDB initialisation (under /var/lib/mysql), there are some other important things to note. First, a random MySQL root user password is set and saved to /root/.mysql_secret on each node. This allows us to connect, and as indicated above, issue a SET PASSWORD command to update the root password. We should also run mysql_secure_installation to secure the MySQL installation. Let’s start by updating the root password. Start MySQL server on both machines:

Connect to the MySQL instance using the temporary password:

Update the password for root@localhost:

Now we can run mysql_secure_installation:

I skipped changing the root password as we have just done that. However, all other security measures are allowed to complete, including the removal of anonymous users, disallowing root-login remotely, and dropping the test database. With the privilege tables reloaded, we are good to continue with the configuration of the MySQL master and slave.

Before proceeding with the configuration of replication and the creation of our SSL certificates, let’s check the MySQL installation. You will see that, when installed in this way via official RPMs, a mysql user and group are created:

A quick check of the process table, and we can see that the MySQL datadir is /var/lib/mysql, and the error log is at /var/lib/mysql/<hostname>.err, which are sensible defaults:

These defaults are inherited from /etc/init.d/mysql, and can be overridden in my.cnf, which by default will be located at /etc/my.cnf.

MySQL SSL Support

The first thing to verify is whether our MySQL installation has SSL support. Perform the following query:

As you can see, SSL support is compiled in, but disabled. Let’s create a basic /etc/my.cnf to enable this:

Excellent - we now have SSL support available.

In a Production environment, we’d look at obtaining SSL certificates from a trusted authority. For the purposes of this tutorial, we’ll create our own CA and generate self-signed certificates. We need two certificates, one for the MySQL server, and one for the MySQL client (in our case, the replication user will connect as a client).

Generating SSL Certificates

On both servers, create a directory structure to hold the certificates, and ensure that it is locked down appropriately:

Before starting, you’ll need to install the latest OpenSSL 0.9.8 version - at the time of writing that’s 0.9.8y. The 1.0.* versions have issues (or, MySQL has issues with them) and you’ll see plenty of “ERROR 2026 (HY000): SSL connection error: protocol version mismatch” messages and lose a lot of hair.

Download the latest 0.9.8 version from the OpenSSL website and install gcc if you don’t already have it:

Extract the tarball:

And configure. Note that the configure script is named config - not the standard configure:

Then make and make install:

Let’s check it worked:

So that we make sure the correct version is used for certificate generation, alias openssl to the new binary:

Now, we can proceed.

First, we’ll create our CA. Generate a new key and certificate, valid for 9999 days (again - only do this in a TEST environment!):

Verify:

Next, we need to create the server certificate. This will be the certificate used by the master MySQL instance. Start with the CSR:

Sign the CSR, and create the certificate:

Verify:

Finally, repeat the process for the client certificate:

We are left with the following files:

Fix the ownership and permissions on the files:

As per this post, change all occurrences of:

to

And

to

In the *.key files, else MySQL will not recognise them as valid keys and you’ll see errors such as

in the MySQL error log.

Finally, transfer the following files to the slave:

• /etc/mysql/ssl.crt/ca.mysqlreplication.tokiwinter.com.crt
• /etc/mysql/ssl.crt/client.mysqlreplication.tokiwinter.com.crt
• /etc/mysql/ssl.crt/server.mysqlreplication.tokiwinter.com.crt
• /etc/mysql/ssl.key/client.mysqlreplication.tokiwinter.com.key
• /etc/mysql/ssl.key/server.mysqlreplication.tokiwinter.com.key

And ensure that ownership and permissions are set as above.

Firewall

At this point, it’s prudent to make sure that the slave MySQL instance can reach the master instance on the appropriate port, 3306/tcp by default. The default CentOS 6.4 iptables ruleset is:

Let’s grant our slave access:

Test access from the slave:

The “Host … is not allowed to connect to this MySQL server” message is expected as we haven’t GRANTed access yet. It does prove that our iptables rule is working, so save the config on the master:

MySQL Configuration

On the master server, add the following to the [mysqld] section of /etc/my.cnf:

Restart MySQL:

Check the SSL variables for correctness:

On the slave server, append the following to /etc/my.cnf:

And add the following to the [mysqld] section:

Restart MySQL:

Back on the master, add the following to the [mysqld] section:

We could specify further parameters here, such as binlog-do-db if we were only interested in replicating a subset of the databases, along with replicate-do-db on the slave.

Restart MySQL once again:

Verify that this server is now keeping a binary log:

We can now create a database on the master server:

On the slave, set server-id to 2 and restart:

Next, back on the master, GRANT REPLICATION SLAVE privileges to a new “replicate” user. Note that the subject and issuer fields from the client.mysqlreplication.tokiwinter.com certificate are required by the grant, and will be checked upon connection:

In this example, we only REQUIRE SSL - you can also REQUIRE SUBJECT and REQUIRE ISSUER if desired. On the slave, use CHANGE MASTER TO to connect to the master. Use the values obtained from the earlier invocation of SHOW MASTER STATUS\G on the master to populate the statement.

If you run a SHOW SLAVE STATUS\G on the slave, you should now see the slave operating as expected, with both the IO and SQL threads active:

Create a database on the master:

On the slave, you’ll see the database created:

MySQL replication over SSL is now completed. Check that the service is set to start automatically at boot:

Troubleshooting

Be careful when transporting the certificates between servers as SELinux contexts may change depending on how you do it. If they end up being tmp_t or similar, change them back with chcon: