My place to record ideas and encounters with technology

Securing MySQL Replication Traffic

If you ever find the need to replicate MySQL traffic up to a service such as Amazon AWS or want to replicate from site-to-site without establishing a VPN tunnel, you can secure the traffic using SSL. I have found this to be especially helpful in the case of configuring AWS instances for replication.

The current MySQL server RPM packages available from Percona and others have SSL support built in, you simply need to configure it.

Create Certificates

You’ll need to have OpenSSL installed to generate these certificates.

Make sure you utilize a unique common name (CN) on each of the generated certificates, otherwise they will not work!

UPDATE! : Make sure the MySQL User has permissions to walk the SSL Certificate folder!!! This would be a chmod +x on the folder. Granting the execute bit on a folder allows the user or group to ‘walk’ the folder, IE, see it’s contents! chown/chmod 750 the folder, chmod 640 the certs/keys, and chown root:mysql the keys as well.

Edit Configuration Files (my.cnf)

On the master server, make the following edits to my.cnf. You should be familiar with configuring MySQL replication before attempting to add SSL to the configuration. Your server-id and other variables will likely vary depending on your environment and how you have your existing replication set up.

At this point, you have configured MySQL replication with SSL. You should further protect your replication traffic with firewall settings limiting connections only from authorized source IP’s.

Setting Auto Increments

On another note, if you are looking to configure MySQL multi-master replication, whether it be MySQL or MySQL Cluster, you need to know how to make use of autoincrement values to prevent row collision.

These values are specified in my.cnf as auto_increment_increment and auto_increment_offset. The increment value should be set to the number of instances you intend to run, for example, auto_increment_increment=2 for a co-master situation. On the first server, you would set auto_increment_offset=1 and the second server auto_increment_offset=2.

In a MySQL Cluster environment with multiple master clusters, you would follow the same suit. Set the increment to the number of master clusters. In a circular replication, three master cluster deployment, you would set auto_increment_increment=3 on all mysqld nodes in all three clusters. In cluster one, you would set all mysqld nodes with auto_increment_offset=1. Cluster two would utilize auto_increment_offset=2, and cluster three would utilize auto_increment_offset=3.

The auto_increment_increment and auto_increment_offset values can be changed on the fly using SET GLOBAL. Make sure you set these values correctly and you utilize the auto_increment function on non-unique tables!