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

3 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

Leave a Reply

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

four + 1 =