This is a subject which has been blogged about at length, however now that I’ve got this working I thought I’d blog about the key things I found here to make sure that not only do I get it right in future but also provide a reference to others who are trying to fix the same issues.

The most important part of the SSL configuration is the creation of the SSL certificates.

If you’re using a third-party CA (verisign/comodo etc.) then use their CA key and certificates however if you’re not willing to pay for certificates that are never going be seen in public, then you can easily create your own CA with the following command:

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

The next part is to create the client keys (you can use these same keys on all of the clients machines that will connect):

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

Finally, you need to create a server key for each of the servers that will be in the replication chain.  To make this clear, if we setup replication for three servers named pinky, perky and posie (don’t ask…) then we would need to run the following command:

for a in pinky perky posie;

do

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout $a-key.pem > $a-req.pem;

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

done

This will use your CA certificate that you generated in the first step to create server certificates for each server which can then be copied over to the relevant systems.

Configuration of MySQL is relatively easy. Copy the generated certificates and the CA certificate into a subdirectory under /etc/mysql (I chose /etc/mysql/certs) and then edit your my.cnf file and add the following (where “<servername>” is replaced with the hostname of the server used to generate the certificates – pinky, perky and posie in our case):

[mysqld]

ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/<servername>-cert.pem
ssl-key=/etc/mysql/certs/<servername>-key.pem

Now restart MySQL and configure replication with the following commands:

Master:

GRANT REPLICATION SLAVE ON *.* TO ‘ssl_replication_user’@’%’ IDENTIFIED BY ‘password’ REQUIRE SSL;

Slave:

SLAVE STOP;

CHANGE MASTER TO
MASTER_HOST=’192.168.0.100′,
MASTER_USER=’ssl_replication_user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000014′,
MASTER_LOG_POS=217
MASTER_SSL=1;

SLAVE START;

SHOW SLAVE STATUSG

This assumes that you had replication working previously unencrypted and took the values of “SHOW SLAVE STATUSG” before running the above to ensure the correct LOG_FILE/LOG_POS are used in the “CHANGE MASTER” statement.

The “REQUIRE SSL” statement on the master ensures that even if the password  is correct, replication will not take place unless the SSL Certificates match.

I’ll be setting up some more servers using this method over the coming days and I’ll update this post if required.