Mysql – Does replication with SSL encryption work on MySQL 5.5

encryptionMySQLmysql-5.5replication

I am trying to set up replication with slave on Amazon EC2 and master in our local environment. The setup went fine without SSL encryption. But when I set up the replication to be SSL encrypted, it is failing to connect to Master.

MySQL version on Master : Server version: 5.5.19-log MySQL Community Server (GPL)
MySQL version on Slave : Server version: 5.5.14-log MySQL Community Server (GPL)

I have SSL encrypted replication set up and going fine where the Master's MySQL version is Server version: 5.1.56-log Source distribution. But it is failing for the other Master machine which has MySQL 5.5.

So my question is, does SSL encrypted replication work for MySQL 5.5?

Anyone here faced the same problem? Please throw some light on how to get around this problem. Note that I have changed the permissions for my SSL certificates used in Slave machine to be mysql instead of root (Both failed anyway)

Thanks.

Best Answer

To use SSL for encrypting the transfer of the binary log required during replication, both the master and the slave must support SSL network connections. If either host does not support SSL connections (because it has not been compiled or configured for SSL), replication through an SSL connection is not possible.

Setting up replication using an SSL connection is similar to setting up a server and client using SSL. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave.

For more information on setting up a server and client for SSL connectivity refer this link

Please follow the steps mentioned here:

http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

I was searching in bugs.mysql.com related to MySQL 5.5 but there is nothing reported w.r.t "replication with SSL" for v5.5. You can mention exact error message here for analysis.

Related Solutions

MySQL 5.1.66 SSL connection error ERROR 2026 (HY000)

Try making certificate files owned by mysql user and not readable by others.

You can try also with a fixed cipher:

mysql ... --ssl-cipher=AES128-SHA

And for the change master:

CHANGE MASTER TO ... MASTER_SSL_CIPHER='AES128-SHA'

Mysql – Breaking Semisynchronous Replication in MySQL 5.5

The one thing you can probably do is to increase the sensitivity of the acknowledgement

Please look for this variables in /etc/my.cnf

[mysqld]
rpl_semi_sync_master_timeout=5000

When rpl_semi_sync_master_timeout (Default is 10000 or 10 seconds) is set to 5000, Replication should switch a Master from SemiSync to Async if no acknowledgement is received from the Slave in 5 seconds (5000 milliseconds). You may want to lower this even less than 5000.

You may need to check your network performance. As long as you not doing geographic distance replication, you should the following: On a Separate NIC, use a crossover cable over 192.168.xx.xx so as not have faster replication response. Also, check the switch for dropped packets.

You should not have to restart the server every time. As a quick-and-dirty band-aid, make up this SQL script:

STOP SLAVE IO_THREAD;
SELECT SLEEP(5);
START SLAVE IO_THREAD;

Simply write a cronjob to execute these commands every 5 minutes. Again, this is a band-aid. Otherwise, due diligence on the networking side is in order.

Best Answer

Related Solutions

MySQL 5.1.66 SSL connection error ERROR 2026 (HY000)

Mysql – Breaking Semisynchronous Replication in MySQL 5.5

Related Question