Databases, Systems & Networks

MySQL master master replication, also known as “mysql chained replication”, “multi master replication or “mysql daisy chaining replication” is an extension of mysql replication allowing the creation of multiple master servers that can then be masters of multiple slaves. In this post, I demonstrate how to setup mysql master master replication.

In a multi master mysql configuration, bar the first master server, each additional master acts as both a master and slave. Therefore, it is possible to create new masters from each additional slave added.

Take a look at this diagram which helps to illustrate one possible configuration.

In this tutorial we’ll cover a basic example of three servers; two masters and one slave but first let’s understand why we might want to set up chained replication with mysql.

This tutorial assumes that it is a new configuration where everything is being set up in advance of the application hitting the database servers.

Use cases for MySQL Master Master Replication

• Migrations
• Improving replication performance

Using MySQL Master Master Replication to Facilitate Database Migrations

I was recently involved in a large database migration project. We had to move several hundred gigabytes of data onto new production servers. The databases resided on two sets of database servers. All of them were to be consolidated onto one set of database servers. Each migration was to happen on different days. After each migration, the application would be brought back online. Application downtime needed to be as minimal as possible.

Common approaches to backup and restore databases involve using mysqldump or Percona Xtrabackup to name two. Backing up and restoring large databases takes time, especially with mysqldump so I decided to get the databases syncing between the current and new production environments in each case.

Improving replication performance with Multi Master MySQL Replication

One of the reasons why MySQL replication is employed by system, application and data architects is to scale databases horizontally. Having an additional slave or slaves helps to scale read operations.

As each slave connects, it creates additional load on the master. Each slave must receive a full copy of the binary log. In environments where there are many slaves, this can increase the network load on the master where it starts to become a bottleneck. In addition, the master may also be serving requests as part of the solution as well as processing the writes.

Adding an additional master as a slave helps to take the load of the primary master. This model can be scaled so that multiple secondary masters can be created, all as slaves to the primary master depending on requirements.

So let’s look at how to set up a basic mysql master, master, slave configuration

The changes should be applied to the my.cnf (my.ini in Windows) and for them to be persisted, the mysql service should be restarted.

Overview:

Master Server (primary)

• Enable the binary log
• Create user to allow secondary master server to connect

Master Server (secondary)

• Enable the binary log
• Enable the relay log
• Enable log slave updates
• Create user to allow slave to connect
• Point at primary master log position

Slave Server (slave to secondary master server)

• Enable the relay log
• Point at secondary master log position

Detailed setup

1/ Configuration changes

The following screenshots show the changes I have made to the my.cnf on my test virtual machines.

Master Server config (primary)

Master Server config (secondary)

Slave Server config (slave to secondary master server)

So as with standard replication, the binary log and relay logs are enabled using the log-bin and relay-log settings. The additional setting used in this configuration is the log-slave-updates. This tells the secondary master to log the changes from the primary master to its own binary log which will allow those changes to be applied to the slave.

2/ Create user on the primary master to allow the slaves to connect.

3/ Find the master binary log file and current position and apply them to the secondary master using CHANGE MASTER TO

On the primary master

*************************** 1. row ***************************
File: mysql-bin.000001
Position: 897
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:

On the secondary master execute this changing to your host, user, log file etc accordingly:

To finish, execute this on the secondary master:

Check the status of replication on the secondary master using:

Check that it’s working ok, look for these lines in the output as guides that it is working ok:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error:
Seconds_Behind_Master: 0

4/ Repeat steps 2 and 3 between the secondary master and the slave

Perform the same steps changing user, host, log file and log positions accordingly

5/ Test

Apply an update to the primary master and check that it replicates across to the secondary master and the slave, for example create a new database.