This blog post is a step by step tutorial on how to set up MySQL Replication between AWS regions. This is an essential part of our disaster recovery plan at Engine Yard. A previous blog post gives a higher level overview on disaster recovery plan for rails apps.
We’re going to use AWS on our examples because that’s what we use at Engine Yard. However, the commands should work on any hosting provider. If you want to move your Ruby on Rails application to AWS or discuss setting up a disaster recovery environment, contact us through the form below for a free consultation.
Let’s say our Production environment is on US East 1. We will set up a disaster recovery (DR) environment on a different region, like US West 2. For simplicity, we’ll have 1 app instance and 1 database instance on each of these environments.
We’ll set up the DR db instance as a replica of the Production db instance. Your data will be replicated from US East 1 to US West 2. When you failover to US West 2, the DR db instance is upto date and you can start using the DR environment.
Installation
For this tutorial, we will use Ubuntu 16.04 LTS on AWS. You can use ami-cd0f5cb6 and ami-6e1a0117 on US East 1 and US West 2, respectively.
Install the following on both Production and DR db instances.
Percona XtraBackup
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtrabackup-24
MySQL
sudo apt-get install mysql-server
qpress
sudo apt-get install qpress
MySQL Config
The config on the Production db instance. Put the following on /etc/mysql/conf.d/logbin.cnf.
[mysqld]
server-id = SERVER_ID
log-bin
The config on the DR db instance. Put the following on /etc/mysql/conf.d/replication.cnf.
[mysqld]
server-id = SERVER_ID
read-only
log-slave-updates = 1
binlog_format = mixed
Replace SERVER_ID with an ID unique to the instances.
After saving the config files, restart mysql with sudo systemctl restart mysql
.
Replication User
Create the ‘replication’ user on the Production db instance.
MASTER_PASSWORD=root
mysql -uroot -p$MASTER_PASSWORD -e 'GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY '$MASTER_PASSWORD';'
Generate an SSH Keypair
The DR db instance needs to connect to the Production db instance and vice versa through SSH.
Do not use a passphrase.
ssh-keygen -t rsa -b 2048 -f dr_key
Do the following on both Production and DR db instances.
- Add the contents of the public key to /home/ubuntu/.ssh/authorized_keys. Change
ubuntu
to the name of a user on your instance. - Put the private key on the /home/ubuntu/.ssh/ directory. Permissions should be 600.
Create Backup
Create a backup of the Production db instance and load it to the DR db instance using XtraBackup.
We’ll run all succeeding commands on the DR db instance. Let’s set a few variables so you can copy and paste the commands.
# MySQL password on Production and DR instances. Use the same password for convenience
MASTER_PASSWORD=root
# MySQL datadir. /var/lib/mysql is used on Ubuntu 16.04 LTS
DATADIR=/var/lib/mysql
# Hostname of your Production db instance
PRODUCTION_DB=ec2-54-226-254-46.compute-1.amazonaws.com
# Hostname of your DR db instance
DR_DB=ec2-54-187-177-205.us-west-2.compute.amazonaws.com
Stop MySQL on the DR db instance and delete the contents of the datadir directory
sudo systemctl stop mysql
sudo bash -c 'rm -rf $DATADIR/*'
Then, run this long command
ssh -i /home/ubuntu/.ssh/dr_key -p 22 -o StrictHostKeyChecking=no -o TCPKeepAlive=yes ubuntu@$PRODUCTION_DB 'sudo innobackupex --user root --password $MASTER_PASSWORD --slave-info --safe-slave-backup --compress --stream=xbstream /doesntneedtoexist | ssh -i /home/ubuntu/.ssh/dr_key -o StrictHostKeyChecking=no -o TCPKeepAlive=yes ubuntu@$DR_DB 'sudo xbstream -x -C $DATADIR''
It’s one command which does the following:
- SSH from the DR db instance to the Production db instance
- Run innnobackupex on the Production db instance
- The result of the backup is piped to the second SSH command that connects back to the DR instance
- Run xbstream on the DR db instance
This command assumes the following:
- The ubuntu user exists on both db instances
- The ubuntu user can run
sudo innobackupex
without a password
Start MySQL. We’ll run the commands as the root user.
sudo su
DATADIR=/var/lib/mysql
cd $DATADIR && for bf in `find . -iname '*.qp'`; do qpress -df $bf $(dirname $bf) && rm $bf; done
innobackupex --apply-log --use-memory=1G $DATADIR
chown -R mysql:mysql $DATADIR
systemctl start mysql
exit
SSH Tunnel
On the DR db instance, we will set the MASTER
to the Production db instance. Instead of using the IP address and port of the Production db instance, we will create an SSH tunnel from DR to Production.
We use an SSH tunnel because port 3306 on the Production db instance is only accessible from the Production instances. We use a Security Group to remove access from the outside world.
On the DR db instance, create the SSH tunnel.
ssh -i ~/.ssh/dr_key -f -p 22 -N -L 13306:127.0.0.1:3306 ubuntu@$PRODUCTION_DB
PRODUCTION_DB is the public hostname of the Production db instance that we set above.
Still on the DR db instance, test the tunnel by connecting to localhost and port 13306 (notice 13306 not 3306).
mysql -h 127.0.0.1 --port 13306 -u root -p
Even though you are connecting to localhost, you are actually connecting to the Production db instance through the SSH tunnel. Use the db password of the Production db instance.
Configure Replication
Stop replication if it’s running and set the password.
mysql -u root -p$MASTER_PASSWORD -e 'STOP SLAVE'
Get the current log file and position. This is where replication will start.
master_log_file=`sudo cat $DATADIR/xtrabackup_binlog_info | awk '{print $1}'`
master_log_pos=`sudo cat $DATADIR/xtrabackup_binlog_info | awk '{print $2}'`
mysql -u root -p$MASTER_PASSWORD -e 'CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=13306, MASTER_USER='replication', MASTER_PASSWORD='$MASTER_PASSWORD', MASTER_LOG_FILE='$master_log_file', MASTER_LOG_POS=$master_log_pos ;'
Start replication.
mysql -u root -p$MASTER_PASSWORD -e 'START SLAVE'
To verify if replication has caught up,
mysql -u root -p$MASTER_PASSWORD -e 'SHOW SLAVE STATUSG'
You should see
Master_Host: 127.0.0.1
Master_User: replication
Master_Port: 13306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Failover
When you failover to the DR environment, remove the replication.cnf we added earlier.
sudo rm /etc/mysql/conf.d/replication.cnf
Disable read-only on MySQL and stop replication.
sudo rm $DATADIR/master.info
sudo rm $DATADIR/relay-log.info
mysql -u root -p$MASTER_PASSWORD -e 'STOP SLAVE;'
mysql -u root -p$MASTER_PASSWORD -e 'RESET SLAVE ALL;'
mysql -u root -p$MASTER_PASSWORD -e 'SET GLOBAL read_only = 0;'
mysql -u root -p$MASTER_PASSWORD -e 'FLUSH PRIVILEGES;'
Point your DNS to the DR environment.