Databases, Systems & Networks

Source: Nico’s Blog

MySQL Master-Slave replication is natively supported by MySQL. However its configuration is not so simple. For each slave added as a replica, a few configuration steps must be done on both the master and itself.

So if you want to install a master instance on a machine or VM, and then install 5 other instances as slaves on other hosts, you will be doing quite a lot of back-and-forth configuration. I couldn’t find any way of configuring the replication automatically on the web, so I decided to create my own bash script to do it.

MySQL Master-Slave Replication

What is it and why use it ?

This form of replication is pretty simple: only a single instance, known as the master, creates or updates data. Each additional instance, called slave, simply keeps a copy of the master’s data, by replicating it each time the master has new data.

Using this form of replication serves 2 purposes:

1. 1. Have a backup at all times.
      • • It is better in performance and risk than performing a full database dump each hour/day/month.
      • If the master dies, a slave can be turned into the new master.

1. Improve performance.
   • • If you have 1 master + 3 slaves and have only a few write operations, you can spread read operations on all 4 instances.
   • If you have lots of write operations, you could read from the 3 slaves only. This way your master could dedicate itself to writing only.

The process of turning a slave into a master is not automatic and would require manual intervention. At least reading from the database would still work, only writes would fail. To do things properly you would also have to reconfigure all other slaves to be slaves of the new master… Maybe a sophisticated script could do this, but right now I’m only focusing on initializing the replication, as I don’t have a real need for production use, so this is a fight for another day

How does it work ?

When the master receives a query which modifies data, it translates the changes made to the database as events, and writes them in a binary log. Then events can be statement-based, row-based, or a mix of both. I wouldn’t worry too much about that… the default sounds good :p It’s row-based since MySQL 5.7.7 by the way, and it was statement-based before.

When a slave starts, it starts 2 threads:

1. 1. The IO thread: connects to the master to get the latest binary log data and copy it to a local relay log.

1. The SQL thread: reads events from the relay log, and applies them to the database.

What is the standard procedure to set up replication ?

Many tutorials exist to set up a master and a slave. For example DigitalOcean and RackSpace have nice tutorials.

To sum up the important points of what they do:

1. Set in the master’s configuration file:
   • server-id for a unique instance identifier
   • log-bin to specify where to store the binary log
   • binlog_do_db to specify which database(s) must be updated in the binary log
2. Grant rights for slave replication to a user
3. Lock the master database
4. Using the SHOW MASTER STATUS command, save the binary log filename and position during the time of the dump.
5. Perform a dump of the database.
6. Unlock the master database.
7. Set in the slave’s configuration file:
   • server-id for a unique instance identifier
   • relay-log to specify where to store the relay log
8. Import the dump from step 5 into the slave database.
9. Declare a master with the CHANGE MASTER TO command, with the following values:
   • The master hostname or IP.
   • The credentials for the user from step 2.
   • The binary log filename and position saved in step 4.

Locking the database in step 3 is necessary to guarantee that the dump represents the state of the database at the saved binary log position.

My script

The script I wrote does all of the above steps (except step 1 which you can do during or after installing your MySQL instances) for a given database and a configurable list of slaves:

#!/bin/bash
#title : replication-start.sh
#description : This script automates the process of starting a Mysql Replication on 1 master node and N slave nodes.
#author : Nicolas Di Tullio
#date : 20160706
#version : 0.2 
#usage : bash mysql_replication_autostart.sh
#bash_version : 4.3.11(1)-release
#=============================================================================

#
# Requirements for this script to work:
# * The Mysql user defined by the $USER variable must:
# - Have the same password $PASS on all mysql instances
# - Be able to grant replication privileges
# - All hosts must be able to receive mysql commands remotely from the node executing this script
#

DB=djangodb
DUMP_FILE="/tmp/$DB-export-$(date +"%Y%m%d%H%M%S").sql"

USER=root
PASS=root

MASTER_HOST=192.168.0.201
SLAVE_HOSTS=(192.168.0.202 192.168.0.203)

##
# MASTER
# ------
# Export database and read log position from master, while locked
##

echo "MASTER: $MASTER_HOST"

mysql -h $MASTER_HOST "-u$USER" "-p$PASS" $DB <<-EOSQL &
GRANT REPLICATION SLAVE ON *.* TO '$USER'@'%' IDENTIFIED BY '$PASS';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
DO SLEEP(3600);
EOSQL

echo " - Waiting for database to be locked"
sleep 3

# Dump the database (to the client executing this script) while it is locked
echo " - Dumping database to $DUMP_FILE"
mysqldump -h $MASTER_HOST "-u$USER" "-p$PASS" --opt $DB > $DUMP_FILE
echo " - Dump complete."

# Take note of the master log position at the time of dump
MASTER_STATUS=$(mysql -h $MASTER_HOST "-u$USER" "-p$PASS" -ANe "SHOW MASTER STATUS;" | awk '{print $1 " " $2}')
LOG_FILE=$(echo $MASTER_STATUS | cut -f1 -d ' ')
LOG_POS=$(echo $MASTER_STATUS | cut -f2 -d ' ')
echo " - Current log file is $LOG_FILE and log position is $LOG_POS"

# When finished, kill the background locking command to unlock
kill $! 2>/dev/null
wait $! 2>/dev/null

echo " - Master database unlocked"

##
# SLAVES
# ------
# Import the dump into slaves and activate replication with
# binary log file and log position obtained from master.
##

for SLAVE_HOST in "${SLAVE_HOSTS[@]}"
do
echo "SLAVE: $SLAVE_HOST"
echo " - Creating database copy"
mysql -h $SLAVE_HOST "-u$USER" "-p$PASS" -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB;"
scp $DUMP_FILE $SLAVE_HOST:$DUMP_FILE >/dev/null
mysql -h $SLAVE_HOST "-u$USER" "-p$PASS" $DB < $DUMP_FILE

echo " - Setting up slave replication"
mysql -h $SLAVE_HOST "-u$USER" "-p$PASS" $DB <<-EOSQL &
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='$MASTER_HOST',
MASTER_USER='$USER',
MASTER_PASSWORD='$USER',
MASTER_LOG_FILE='$LOG_FILE',
MASTER_LOG_POS=$LOG_POS;
START SLAVE;
EOSQL
# Wait for slave to get started and have the correct status
sleep 2
# Check if replication status is OK
SLAVE_OK=$(mysql -h $SLAVE_HOST "-u$USER" "-p$PASS" -e "SHOW SLAVE STATUS\G;" | grep 'Waiting for master')
if [ -z "$SLAVE_OK" ]; then
echo " - Error ! Wrong slave IO state."
else
echo " - Slave IO state OK"
fi
done

Double-click on the code to get the raw code with correct indentations. The tab indentations are important, especially for the multi-line HereDoc instructions.

Basically, this script automates all the steps detailed in the previous section (after step 1). It only performs a single dump of the master database, saves it on the client which is running the script, before redistributing it to all slaves.

The only tricky part to understand is the master database locking. When locking a database, if you exit the session, it will be automatically unlocked. But what we need to do here is lock the database in a mysql client session, and perform the dump in an ssh session while it is locked. That is why the DigitalOcean tutorial suggests opening a new ssh window to do the dump while the mysql client session is still open in the first window.

To simulate this in bash, I lock the database and performed a long SLEEP in the mysql session as a background task. Then I perform the mysqldump, and finally kill the background mysql session to release the lock. Voilà !