MySQL Replication with SSL

From OptionC

Table of contents
1 Installing the prerequisites
2 Creating Keys, Certificates and other fun SSL Stuff

2.1 Create your own Certification Authority (CA)
2.2 Create a server certificate
2.3 Create the client certificates
2.4 If you ever need to swap slave/master

3 Configure SSL login constraints
4 Test Set-up
5 Configure Replication
6 Issues
7 Recommended Reading
[edit]

Installing the prerequisites

MySQL Supports SSL as of 4.0.x, replication with SSL as of 4.1.x and yaSSL as of 5.0.10. As I'm using Debian, and the Debian MySQL binaries do not come with SSL compiled in, I followed this guide for compiling from scratch.

  • MySQL Secure Replication over SSL on Debian Sarge. (http://www.terryburton.co.uk/blog/2005/06/mysql-secure-replication-over-ssl-on.html) This actually walks you through all the steps, not just compilation, but since it was my first time (or I'm very slow), I needed more information on the post-compilation side. Also, a few simple things I noticed:
    • I had to run the install step twice because I was too lazy to get the dependencies in the right order
    • The switch is actually "--with-openssl" not "--with-ssl"
    • You probably don't need libmysqlclient14-dev_4.1.11a-4_i386.deb (if you do, you'd know0
  • If you are using something other than Debian, here's the requirements for compiling (http://dev.mysql.com/doc/mysql/en/secure-requirements.html)
  • If you need to check if ssl is enabled (or if the compilation worked) 
SHOW VARIABLES LIKE 'have_openssl';
[edit]

Creating Keys, Certificates and other fun SSL Stuff

(Most of this is in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt)

[edit]

Create your own Certification Authority (CA)

Change to the mysql ssl directory (you may need to create it) 

mkdir /etc/mysql/ssl
cd /etc/mysql/ssl/

Create your own Certification Authority (CA) if you do not already have one (e.g. for signing web or mail server certificates) 

openssl req -x509 -new -days 9999 -newkey rsa:2048 -nodes \
 -keyout ca-key.pem -out ca-cert.pem
[edit]

Create a server certificate

Create the server certificate request 

openssl req -new -newkey rsa:2048 -nodes -keyout server-key.pem -out server-csr.pem

(optional) Remove the passphrase from the key 

openssl rsa -in server-key.pem -out server-key.pem

Sign this server request with the CA key to make a proper server certificate. 

openssl x509 -req -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial \
 -CAserial ca-srl.txt -in server-csr.pem -out server-cert.pem

Adjust the following lines (such as the paths) in /etc/mysql/my.cnf as necessary (note to self - the server keys go in the [mysqld] section, the client keys go in the [client] section): 

[mysqld]
ssl-key=/etc/mysql/ssl/server-key.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-ca=/etc/mysql/ssl/ca-cert.pem
[edit]

Create the client certificates

Create the client certificate request 

openssl req -new -newkey rsa:2048 -nodes -keyout client-key.pem -out client-csr.pem


(OPTIONAL) Remove a passphrase from the key 

openssl rsa -in client-key.pem -out client-key.pem

Sign this server request with the CA key to make a proper server certificate 

openssl x509 -req -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial \
 -CAserial ca-srl.txt -in client-csr.pem -out client-cert.pem

Copy the client files client-key, client-cert and ca-cert.pem to $PATH on the client machine and adjust your ~/.my.cnf or /etc/mysql/my.cnf: 

[client]
ssl-ca=$PATH/ca-cert.pem
ssl-key=$PATH/client-key.pem
ssl-cert=$PATH/client-cert.pem
[edit]

If you ever need to swap slave/master

Then you'll need to set up the keys and configuration files both ways. Both servers need to have server/client keys and the sections for them in /etc/mysql/my.cnf

[edit]

Configure SSL login constraints

This is the sample from ls /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt. It grants more permissions than are needed for replication. (Don't just limit to a subject because without checking the issuer - whom's key only we have - anybody could fake the subject line rendering the whole thing nearly useless.) 

mysql> GRANT all ON *.* TO "replicate" REQUIRE \
 subject "/C=DE/ST=NRW/L=Aachen/CN=Foo Bar/emailAddress=foobar@example.com" and \
 issuer "/C=DE/ST=NRW/L=Aachen/CN=Christian Hammers/emailAddress=ch@debian.org";

Here's another example 

mysql> grant replication slave, super, reload, select on *.* to 'replicate'@'%' \
identied by 'password' require SSL;

And a third one (the one we use, since we aren't sure of the IP information, and may want to swap master/slave): 

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicate@'%' \
 IDENTIFIED BY 'password' require SSL;

After creating the account, verify that the settings are correct: 

mysql> SHOW GRANTS FOR replicate;

Regardless, end with: 

FLUSH PRIVILEGES;

Repeat for client/slave if you ever need to make the slave the master.

[edit]

Test Set-up

Before moving on to replication, it's a good idea to make sure that the SSL stuff works. A simple test (granted that you have a valid user, and have configured mysql to allow remote logins) is something like: 

mysql --ssl -h MySQL_SERVER -u SSL_CLIENT -p

Should let you log in. If not, make sure that all your paths are correct, that you've restarted the mysql daemon on both machines, etc.

[edit]

Configure Replication

On Server:

1) Change /etc/mysql/my.cnf 

server-id=1
log-bin=/var/log/mysql/mysql-bin.log

2) Get data to slave

If you have two new servers, skip all this and just restart the MySQL daemon.

I was using MyISAM and had the option of shutting down the server for a bit. If you do not, see the section on this in High Performance MySQL (http://dev.mysql.com/books/hpmysql-excerpts/ch07.html) (and best of luck) 

/etc/init.d/mysql stop
tar -cvf master.tar /var/lib/mysql/*
/etc/init.d/mysql start

Check to make sure you have something like "mysql-bin.001" in /var/log/mysql. This means that you have a copy of the data and the log of every transaction since the copy was made.

Copy the data to the client and untar it into /var/lib/mysql, making sure that you don't overwrite the "mysql" table or you'll have fun recreating your user IDs.

(Another option is "load data from master;" which I haven't gotten to work.)


On Client:

1) Change /etc/mysql/my.cnf (server-id for the slave has to be a unique number greater than 1) 

server-id=2 
log-bin=/var/log/mysql/mysql-bin.log

/etc/init.d/mysql restart

2) Set master. Note that this information can be put in /etc/mysql/my.cnf. However, after first starting the slave it is written to /var/lib/mysql/master.info (along with other useful information), and that is considered the "current" server (so if you change it in /etc/mysql/my.cnf and restart, it doesn't change the master). As such, using the mysql interface is probably least confusing. 

mysql> change master to
 master_host='master_hostname',
 master_user='replicate',
 master_password='password',
 master_ssl=1;

3) Start slave 

mysql> start slave;

4) /var/log/daemon.log should have something like this at the end 

Oct  4 07:47:51 slave mysqld[3390]: 051004  7:47:51 [Note] Slave SQL 
 thread initialized, starting replication in log 'FIRST' at position 0, 
 relay log './slave-relay-bin.000001' position: 4
Oct  4 07:47:51 slave mysqld[3390]: 051004  7:47:51 [Note] Slave I/O 
 thread: connected to master 'replicate@master_hostname:3306',  
 replication started in log 'FIRST' at position 4
[edit]

Issues

I had a lot of issues that seem to be based on the Password Hashing Changes from 4.0 to 4.1 (http://dev.mysql.com/doc/mysql/en/password-hashing.html), although some of them seemed to show up in rather odd ways. Keep in mind that once you've upgraded to 4.1, pre-4.1 clients may not be able to connect because they can't use the longer password hash. The most basic solution is to run the server with --old-passwords.

Of course, even having done a test run to work through the kinks, when this went live I still had problems. The final one was this error message every time I tried to start the slave. 

Oct 11 05:05:08 slave mysqld[19472]: 051011  5:05:08 [Note] Slave SQL 
 thread initialized, starting replication in log 'FIRST' at position 0, 
 relay log './slave-relay-bin.000001' position: 4
Oct 11 05:05:08 muber mysqld[19472]: 051011  5:05:08 [ERROR] Slave I/O thread: 
 error connecting to master 'replicate@master:3306': Error: 
 'Access denied for user 'repliate'@'slave' (using password:YES)'  
 errno: 1045  retry-time: 60  retries: 86400

After double, triple and quadruple-checking my configuration files, I went on-line, and after much aimless wandering, encountered this thread on the mysql forums:

It goes through a lot of steps for trying to troubleshoot that problem; the one that made me think was somebody who said he had changed the password for the replication user to be only 12 characters. So I changed the replication user to be an "old" user that still had the 16-byte hash, and it worked.

Another possibility is that the certificates aren't readable by the 'mysql' user, which is what the replication runs as. This can happen even if the earlier mysql command line test succeeded (as it ran as a different user).

One other thing I've noticed: it won't work if you don't specify replication options on one single line, be sure to execute : 

 CHANGE MASTER TO MASTER_SSL_CA='/etc/mysql/ssl/ca-cert.pem',MASTER_SSL_CERT='/etc/mysql/ssl/server-cert.pem',MASTER_SSL_KEY='/etc/mysql/ssl/server-key.pem',
 MASTER_SSL=1,MASTER_USER='USER',MASTER_PASSWORD='PASS';
[edit]

Recommended Reading

Retrieved from "http://www.option-c.com/xwiki/MySQL_Replication_with_SSL"