MySQL Replication Tutorial For Disaster Recovery



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.

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.


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$(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


sudo apt-get install mysql-server


sudo apt-get install qpress

MySQL Config

The config on the Production db instance. Put the following on /etc/mysql/conf.d/logbin.cnf.

server-id                       = SERVER_ID

The config on the DR db instance. Put the following on /etc/mysql/conf.d/replication.cnf.

server-id                       = SERVER_ID
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.


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

# MySQL datadir. /var/lib/mysql is used on Ubuntu 16.04 LTS

# Hostname of your Production db instance

# Hostname of your DR db instance

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
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

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: 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 --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}'`

Start replication.

mysql -u root -p$MASTER_PASSWORD -e "START SLAVE"

To verify if replication has caught up,


You should see

                  Master_User: replication
                  Master_Port: 13306
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


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/
sudo rm $DATADIR/

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;'

Point your DNS to the DR environment.

Free Ebook: PaaS Is Dead

Platform as a Service (PaaS) is experiencing a digital transformation, and despite what some may argue, it’s far from dead. Learn why PaaS continues to prove it has a promising future for DevOps.

PaaS Is Dead

Related posts

Application Load Balancer

December 11, 2017

Engine Yard announces support for Application Load Balancing. This new feature gives you the

Read More

Disaster Recovery Plan for Rails Apps

October 24, 2017

This is part of a series on preparing your Rails application for Black Friday. This disaster

Read More

Dependency Management Guidelines for Rails Teams

October 5, 2017

I recently encountered a discussion in a developer chatroom about how to find good ruby gems to

Read More

Christopher Rigor

Christopher Rigor is a Senior Technical Evangelist at Engine Yard. He’s a long time Rails user, system administrator, and recently became a contributor of RailsInstaller. Previously, he was the DevOps Support Manager for Asia-Pacific at Engine Yard.
Find me on:


Subscribe Here!