Replication of Mysql 5.0 Using SSL

This is developed using master and slave mysql 5.0.26 server on RHEL 4. The Mysql was the binary build from http://dev.mysql.com/downloads/mysql/5.0.html.

I will go back and forth between master server and client server, so It is a good idea to set up vncserver or SSH in both server.

First install mysql server, client, Shared libraries, Shared compatibility libraries,Headers and libraries using rpm.

This blog is for replication, so I will not explain to you in depth on how to install mysql.

Now consider that all mysql installation for master and client server went well. we go to master server.

By default SSL is not enable, but included in mysql 5. how to check:

mysql>SHOW VARIABLES LIKE 'have_openssl';

You either get 'YES' or 'DISABLED', if you get DISABLED then you will need to configure this. explain later below.

The first part of this blog, I am going to document the replication without SSL support. This is good enough if your servers is in the same room and you just want to replicate. and the second part is when you want more secure connection between your servers, i.e. your Master server is on your headquarter and you want to put the slave in another location in your WAN.

Setting Replication no SSL

I use the manual on how to set up the replication from

http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

Setting in Master server (State of server: not online)

Key point not to be overlooked;

* When I set this one both server are not online, meaning only root access is allowed. Just to make sure that no boady change anything when we setup this for the first time

* setting up the user in master for replication only. this is to make sure that this account will only be use for replication only
in master:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'your.client.domain' IDENTIFIED BY 'your_slave_user_password';

if you want to force using ssl (additional security feature) use this

mysql> GRANT REPLICATION SLAVE ON *.* TO 'secure_replication_user'@'your.client.domain' IDENTIFIED BY 'your_slave_user_password' require SSL;

* flush the table to make sure that every changes in implemented

mysql> FLUSH TABLES WITH READ LOCK;

I add read lock just to make sure that no body accssing this server while I am configuring.

* Capture the master data and duplicate the data in master to client server. this is important so the client has the exact data as in master for innitial setup. You have 2 choice on how to do this.
You maybe want to skip the mysql table whne you do both method, if you have different user table of different version of mysql
1. Using mysqldump, to dump all databases you have in your master and run the result in client.

in master:

shell>mysqldump -u root -p > master_data_dump.sql

Considering that you don't have the same database name in client as in master server
ftp or transfer with your own method the master_data_dump.sql to client server

in client:

shell>cd /path/to/dir/of/file/above
shell>mysql -u root -p
shell>source master_data_dump.sql

or do

shell> mysql -u root -p < master_data_dump.sql

waiiittt... until... its finish......

if no error occur, now you have the same data in client as in master. I find this is the safest way of porting innodb tables from master to client

2. make a binary snapshot. I am using tar to do the binary snapshot (archive) of mysql data directory. You also don't need to include any log files in the archive, or the master.info or relay-log.info files.

in master:

shell> cd /path/to/mysql/data/dir
shell> tar -cvf /tmp/mysql-snapshot.tar .

The above command will archive everyhting in mysql data directory. use this instead if you want to include only 1 database

shell> tar -cvf /tmp/mysql-snapshot.tar ./this_db

in client:
unpacking the archives

shell> cd /path/to/mysql/data/dir
shell> tar -xvf /tmp/mysql-snapshot.tar

Assuimng that you have the same data in master and client except maybe from the one you exclude. do this:
mysql > SHOW MASTER STATUS;

* make sure your server-id in my.cnf is different between master and client. by default server-id=1 in master and server-id=2 in client. if you have more than 1 client just increment that number. if you omit this the master can refuse any connection from client

* execute this
in client:
mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name_or_master_IP',
MASTER_PORT =3306,
MASTER_USER='replication_user',
MASTER_PASSWORD='your_slave_user_password';

check your mysql error log, you should get similar to this

061101 10:26:46 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './development-relay-bin.000001' position: 4
061101 10:26:46 [Note] Slave I/O thread: connected to master 'replication_user@master_host_name_or_master_IP:3306', replication started in log 'FIRST' at position 4

Now you are replicating your master server into client server, CONGRATULATION! :)

Take look at the comment here http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html for other problem that may occur.

Setting up replication with SSL

Now you should have your server talking to each other, exchanging data to each other. You wonder, if this connection is open to anyone to read then I really have huge hole in security. All password protection, firewall are useless.
So you want more secure connection between those to. Luckily mysql 5 have SSL build in. Basically all client connection to master will be encrypted, along the way. I am not saying that this is 100% secure, but this is adding the sense of security in your mind :) and for those security freaks.

I have found a good tutorial here http://www.option-c.com/xwiki/MySQL_Replication_with_SSL.
they pretty much lay out everything you need to know on how to add ssl for your replication.
I will just point several thing you don't want to miss

* Create your own Certification Authority (CA), if you have your certificate created by vendor, then ask for that vendor CA. if not you can do this in your master server

shell>cd /path/to/put/your/master-ssl/thingy
shell>openssl req -x509 -new -days 9999 -newkey rsa:2048 -nodes -keyout ca-key.pem -out ca-cert.pem

Fill out all the question with your information

Create a server certificate

Create the server certificate request
shell>openssl req -new -newkey rsa:2048 -nodes -keyout server-key.pem -out server-csr.pem

Remove the passphrase from the key
shell>openssl rsa -in server-key.pem -out server-key.pem

Sign this server request with the CA key to make a proper server certificate.
shell>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

* make sure that your certificate files are not readable by others. I change permision to -600 for those files. Change the owner or the group of the file to mysql

* change the master's my.cnf and add these lines

[mysqld]
ssl-key=/path/to/put/your/master-ssl/thingy/server-key.pem
ssl-cert=/path/to/put/your/master-ssl/thingy/server-cert.pem
ssl-ca=/path/to/put/your/master-ssl/thingy/ca-cert.pem

* now go to client server. move the master's ca-cert.pem to client server. the CA certifificate of master server has to be the same as client server.

Create the client certificates

shell> cd /path/to/put/your/client-ssl/thingy/

Create the client certificate request
shell>openssl req -new -newkey rsa:2048 -nodes -keyout client-key.pem -out client-csr.pem

Remove a passphrase from the key
shell>openssl rsa -in client-key.pem -out client-key.pem

Sign this server request with the CA key to make a proper server certificate
shell>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

*As always change the permission of those certificate to something more secure like 600 and change the ownership of the file to mysql.

* change the client my.cnf add these file

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

don't forget this

[mysqld]
master-ssl
master-ssl-ca=/path/to/ca-certificate/ca-cert.pem
master-ssl-key=/path/to/client-key/client-key.pem
master-ssl-cert=/path/to/client-certificate/client-cert.pem

as you might come accross with error mention belows.

* Add user in master server to use SSL like I mention above.

* test the connection

shell>mysql --ssl -h master_host_name_or_master_IP -u secure_replication_user -p

Reff::
* delete master.info file in client
* start mysql client server
* do the following in client server

mysql>stop slave;
mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name_or_master_IP',
MASTER_PORT =3306,
MASTER_USER='secure_replication_user',
MASTER_PASSWORD='your_slave_user_password',
MASTER_SSL=1;
mysql>start slave;

* if you ever change your certificates you have to redo Reff:: in client server and manke sure that you CA cert in client is the same with master's CA cert

Please add any commentif you find this usefull or you have addition to this

Addition:

I exclude the mysql table to replicate in client my.cnf
replicate-ignore-table = mysql.user
setting the replay in my.cnf client's
relay-log=development-relay-bin



error occur:

1. trying to connect to master server from slave server

shell>mysql --ssl -h master_host_server -u secure_replication_user_slave -p

ERROR 2026 (HY000): SSL connection error

try: check the date of the certificate. In my case the certificate was begin at 5 pm and I tried to connect at 2pm, so the server gave me this error. wait until the cert can be use. restart both Mysql Server (master and slave).

%>openssl x509 -in ca-cert.pem -dates -noout

since the error message is not realy helpful to debug the error, most likely this happend because of problem with your certificates. make sure slave CA cert is the same as the master ca cert.
Don't forget to put the certificates info in slave my.cnf

2. error account accessing master server

I found below error have 2 solution that you want to try. If you have mysql 4.0 maybe you have OLD_PASSWROD problem. Change the user password in master server so it is comply with mysql OLD_PASSWORD. if you have mysql 5 setup then maybe the answer below.

061031 16:21:07 [ERROR] Slave I/O thread: error connecting to master 'sec_repl_user@10.1.2.43:3306': Error: 'Access denied for user 'sec_repl_user'@'10.1.2.5' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400

when you test the connection manually using

shell> mysql --ssl -h 10.1.2.43 -u sec_repl_user -p

the connection established and you are log in to master server but if you start the slave above error occur. This is happend because you don't have certificate information in master.info file.

the remedy:
make sure you have this line in your my.cnf

[client]
ssl-ca=/path/to/ca-certificate/ca-cert.pem
ssl-key=/path/to/client-key/client-key.pem
ssl-cert=/path/to/client-certificate/client-cert.pem

[mysqld]
master-ssl
master-ssl-ca=/path/to/ca-certificate/ca-cert.pem
master-ssl-key=/path/to/client-key/client-key.pem
master-ssl-cert=/path/to/client-certificate/client-cert.pem

the second part(mysqld) was not in my.cnf and I got the error.
you can also set the master-ssl directive inside CHANGE MASTER command in mysql.

so I think the first part let me in to master if I use command line, but the second part is needed for the replication slave
make sure you do this after restarting your mysql client server
- delete master.info file in client
- stop slave;
- do the CHANGE MASTER thing
- start slave;

3. Can not connect to master at all.

Make sure your network admin open port 3306 (or what ever port you use for mysql) in your network WAN or LAN firewall.