MySQL Replication Tutorial For Disaster Recovery

Facebook
Twitter
LinkedIn

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.

You might also like:   Understanding Rack Apps and Middleware

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.

You might also like:   The Ruby Unbundled Series: Why You Should Check Out Hotwire Now

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.

Want more posts like this?

What you should do now:

Facebook
Twitter
LinkedIn

Easy Application Deployment to AWS

Focus on development, not on managing infrastructure

Deploying, running and managing your Ruby on Rails app is taking away precious resources? Engine Yard takes the operational overhead out of the equation, so you can keep innovating.

  • Fully-managed Ruby DevOps
  • Easy to use, Git Push deployment
  • Auto scaling, boost performance
  • Private, fully-configured Kubernetes cluster
  • Linear pricing that scales, no surprises
  • Decades of Ruby and AWS experience

14 day trial. No credit card required.

Sign Up for Engine Yard

14 day trial. No credit card required.

Book a Demo