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:

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


  1. Nicolas said:

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

    October 19, 2016
  2. giuseppe said:

    instead of mysqldump you can use rsync

    February 21, 2017
  3. vagner said:

    congrats !

    May 30, 2017
  4. Erkan said:

    thanks a lot!

    March 9, 2018
  5. Oak Hall said:

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

    September 8, 2018
  6. Oak Hall said:


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

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


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


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

    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[@]}”
    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 &
    # 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."
    echo " – Slave IO state OK"

    September 10, 2018
  7. Hila said:

    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 🙂


    March 2, 2019
    • 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
  8. Arvind said:

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

    August 7, 2019

Leave a Reply

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

six + 8 =