• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How to Setup MySQL Master Master Replication

April 4, 2018 by Andy Hayes 1 Comment

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.

mysql master master replication

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)

mysql master master replication primary master config

Master Server config (secondary)

mysql master master replication secondary master config

Slave Server config (slave to secondary master server)

mysql master master replication slave config

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.

GRANT REPLICATION SLAVE ON *.* TO 'user'@'host' IDENTIFIED BY 'SecurePassword';

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

SHOW MASTER STATUS\G;
*************************** 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:

CHANGE MASTER TO
MASTER_HOST='primarymaster.home',
MASTER_USER='replication',
MASTER_PASSWORD='SecurePassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=897;

To finish, execute this on the secondary master:

START SLAVE;

Check the status of replication on the secondary master using:

SHOW SLAVE STATUS\G;

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.

Summary

Master master replication in MySQL is a great way to improve replication performance or to facilitate synchronisation of database servers as part of database migration activities.

For more information on this topic, you can view this link

If you want more information on how to set up master/slave replication you can view my post here

I hope you found this post useful 🙂

Filed Under: MySQL Administration Tagged With: mysql, replication

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Jesus Ontiveros says

    October 29, 2020 at 7:40 pm

    Hello, we have 2 DB’s, one on each site, DB A and DB B, is there a way to backup a Master-Master (Active-Active ) Replication for DB A and DB B? Do I have to add another (VM -DB) as Slave to each DB A and DB B?

    any feedback is greatly appreciated.

    Your response is greatly appreciated.

    Thank you

    Reply

Leave a Reply Cancel reply

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

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©