MySQL Replication with SSL
|Table of contents|
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';
Creating Keys, Certificates and other fun SSL Stuff
(Most of this is in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt)
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
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
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
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
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/emailAddressemail@example.com" and \ issuer "/C=DE/ST=NRW/L=Aachen/CN=Christian Hammers/emailAddressfirstname.lastname@example.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:
Repeat for client/slave if you ever need to make the slave the master.
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.
1) Change /etc/mysql/my.cnf
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.)
1) Change /etc/mysql/my.cnf (server-id for the slave has to be a unique number greater than 1)
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: 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: 051004 7:47:51 [Note] Slave I/O thread: connected to master 'replicate@master_hostname:3306', replication started in log 'FIRST' at position 4
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: 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: 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:
- replication user denied access (http://forums.mysql.com/read.php?26,11637,11637#msg-11637)
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';
- From the official manual
- Setting Up SSL Certificates for MySQL (http://dev.mysql.com/doc/mysql/en/secure-create-certs.html)
- Replication FAQ (http://dev.mysql.com/doc/mysql/en/replication-faq.html) Especially the final one, about switching from slave to master and back
- How to Set Up Replication (http://dev.mysql.com/doc/mysql/en/replication-howto.html)
- High Performance MySQL Chapter 7: Replication (http://dev.mysql.com/books/hpmysql-excerpts/ch07.html)
- mysql+heartbeat (http://www.karkomaonline.com/article.php?story=2004012416185184)
- ssh tunnel with mysql (or other ways of securing port 3306) (http://www.webmasterworld.com/forum40/1010.htm)