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.
# USE DIFFERENT COMMON NAMES FOR ALL CERTS!!!!
#Make directory
mkdir -p /etc/mysql/newcerts && cd /etc/mysql/newcerts
#Generate CA Certs
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
#Generate Server Certs
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
#Generate Client Certs
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
#Verify the Certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Place Certificates on MySQL Servers
Place the certificates on each MySQL Server participating in replication. Make sure you create the folder /etc/mysql/certs on each server first.
#On each server
mkdir -p /etc/mysql/certs
#Copy certs to all servers using rsync or scp (See Example Below)
scp /etc/mysql/newcerts/ca-cert.pem root@10.0.1.234:/etc/mysql/certs/
scp /etc/mysql/newcerts/server-cert.pem root@10.0.1.234:/etc/mysql/certs/
scp /etc/mysql/newcerts/server-key.pem root@10.0.1.234:/etc/mysql/certs/
scp /etc/mysql/newcerts/client-cert.pem root@10.0.1.234:/etc/mysql/certs/
scp /etc/mysql/newcerts/client-key.pem root@10.0.1.234:/etc/mysql/certs/[/code]
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.
#Configure Master
mkdir -p /data/mysql/binlog/ && chown mysql:mysql /data/mysql/binlog/
#Edit my.cnf
ssl
ssl-cipher=AES128-SHA
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
server-id=10
log-slave-updates=0
log_bin=/data/mysql/binlog/mysql-bin.log
log_bin_index=/data/mysql/binlog/mysql-bin.log.index
expire_logs_days=7
max_binlog_cache_size=128M
binlog_format=row
On the slave server, your configuration should look something like this.
#Configure Slave
mkdir -p /data/mysql/relaylog/ && chown mysql:mysql /data/mysql/relaylog/
#Edit my.cnf
ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
server-id=20
master-connect-retry=60
log-slave-updates=1
relay_log = /data/mysql/relaylog/mysql-relay-bin
relay_log_index = /data/mysql/relaylog/mysql-relay-bin.index
relay_log_space_limit = 1000M
replicate-ignore-db=mysql,ndbinfo,information_schema,performance_schema
Finalize Configuration and Activate Replication
Now comes the fun part, actually making the replication happen.
Start the master server, create the slave user, and execute a mysqldump if you haven’t already.
#Create Slave User on Master server
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;
FLUSH PRIVILEGES;
#do a MySQL Backup
# 1. Flush Tables with Read Lock;
# 2. In a new SSH session, Execute Backup with mysqldump, xtrabackup, or ndb_mgm if using MySQL Cluster
# 3. show master status \G
# 4. Unlock tables;
Start the slave, restore the backup and configure the Master settings.
#First, restore the backup of the master. Then, execute a CHANGE MASTER command, paying special attention to the SSL settings.
CHANGE MASTER TO MASTER_HOST='10.0.1.234', MASTER_PORT=3306, MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=123, MASTER_SSL=1, MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem', MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem';
#Activate the slave
START SLAVE;
#Check Slave Status
SHOW SLAVE STATUS\G
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!