Initialize MySQL Master-Slave replication with a script

Mysql Master-SlaveMySQL 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. 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. 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à !

11 Comments

  1. Nicolas said:

    Hi Thomas, I’m glad I could help. Thanks for sharing your upgrade. Cheers !

    October 19, 2016
    Reply
  2. giuseppe said:

    instead of mysqldump you can use rsync

    February 21, 2017
    Reply
  3. vagner said:

    congrats !

    May 30, 2017
    Reply
  4. Erkan said:

    thanks a lot!

    March 9, 2018
    Reply
  5. Oak Hall said:

    Would this work for multiple databases? Just rerun for each database? Thanks! Oak

    September 8, 2018
    Reply
  6. Oak Hall said:

    FYI LINE 83 NEEDS TO BE $PASS

    below does all databases. Thanks so much for making the script!!

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

    USER=XXXX
    PASS=XXXXX

    MASTER_HOST=192.168.x.xxx
    SLAVE_HOSTS=(192.168.x.xxx)

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

    echo “MASTER: $MASTER_HOST”

    mysql -h $MASTER_HOST “-u$USER” “-p$PASS” < ${DBLISTFILE}

    DBLIST=””
    for DB in `cat ${DBLISTFILE}` ; do DBLIST=”${DBLIST} ${DB}” ; done

    MYSQLDUMP_OPTIONS=”–routines –triggers –single-transaction”
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} –databases ${DBLIST} > $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” /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" <<-EOSQL &
    STOP SLAVE;
    CHANGE MASTER TO MASTER_HOST='$MASTER_HOST',
    MASTER_USER='$USER',
    MASTER_PASSWORD='$PASS',
    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

    September 10, 2018
    Reply
  7. Hila said:

    Hi,
    First of all, thanks for sharing.
    Second, You wrote that the script does all the steps you mentioned, but it doesn’t. it doesn’t set server_id for master and slave, nor sets the log-bin..
    If you could edit it so it’ll really reflect all steps, it’ll be great 🙂

    Thanks,
    Hila

    March 2, 2019
    Reply
    • Nicolas said:

      Hi Hila, you are right, the script does not perform step 1, it considers than MySQL has been already installed and configured with your server-id and binary log preferences. I modified the post to mention than step 1 is not performed by the script. If you want to automate Step 1 please go ahead and share it with us 🙂

      March 3, 2019
      Reply
  8. Arvind said:

    Thanks for sharing this valuable script, i would like appreciate efforts you applied for it.

    August 7, 2019
    Reply
  9. Sunil said:

    One of very useful works!! Thank you for sharing and helping others.

    Love your work and intention, care !!

    September 11, 2022
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *