MySQL Bidirectional database replication between two master (master-master-replication)

This demo is a variation of https://github.com/eMahtab/mysql-master-slave-replication , where we had setup Master Slave replication from a single mysql master to a single mysql slave/replica.

In this demo we setup two MySQL instances (e.g. mysql-master-1 and mysql-master-2), both working as master. mysql-master-1 replicates its data to mysql-master-2 and mysql-master-2 replicates its data to mysql-master-1.

!!! Prerequisite : You would need Docker installed on your system to follow along this demo.

We would run both mysql-master-1 and mysql-master-2 as docker containers, using docker compose.

Step 1 : Create the Docker compose file and execute docker compose up

Below docker-compose-master-master.yml declares two services, named as mysql_master_1 and mysql_master_2 (in docker compose file actual containers are named as mysql-master-1 and mysql-master-2). We are using mysql:8.0 as the docker image, and declare root user password as toor and create a test database.

Make sure docker engine is running on your host machine before running the docker compose -f docker-compose-master-master.yml up command.

---
version: "2"
services:
  mysql_master_1:
    image: mysql:8.0
    container_name: mysql-master-1
    volumes:
      - mysql-master-1-volume:/tmp
    command:
      [
        "mysqld",
        "--datadir=/tmp/master1/data",
        "--log-bin=bin.log",
        "--server-id=1"
      ]
    environment:
      &mysql-default-environment
      MYSQL_ROOT_PASSWORD: toor
      MYSQL_DATABASE: test
      MYSQL_USER: test_user
      MYSQL_PASSWORD: insecure
    ports:
      - "3308:3306"

  mysql_master_2:
    image: mysql:8.0
    container_name: mysql-master-2
    volumes:
      - mysql-master-2-volume:/tmp
    command:
      [
        "mysqld",
        "--datadir=/tmp/master2/data",
        "--log-bin=bin.log",
        "--server-id=2"
      ]
    environment: *mysql-default-environment
    ports:
      - "3309:3306"

volumes:
  mysql-master-1-volume:
  mysql-master-2-volume:

Step 2 : Create Replication user on both the MySQL instances with REPLICATION SLAVE privilege

Next we need to create a replication user on both the MySQL instances (mysql-master-1 and mysql-master-2) and grant that user REPLICATION SLAVE privilege. To do this, we first open the bash shell on each MySQL instance docker exec -it mysql-master-1 bash and connect to mysql mysql -uroot -ptoor running on the instance, then execute below mysql commands.

CREATE USER 'replicator'@'%' IDENTIFIED BY 'rotacilper';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

Here we create a replication user called replicator with password rotacilper and grant this user REPLICATION SLAVE privilege, and finally flush privileges.

Step 3 : Execute SHOW MASTER STATUS on both the MySQL instances

Get the master status, execute the command SHOW MASTER STATUS; on both the MySQL instances to find the Binlog file and position.

Step 4 : Execute CHANGE MASTER TO and START SLAVE; command on both the MySQL instances

Next we need to execute CHANGE MASTER TO and START SLAVE; command on both the MySQL instances. Connect to each MySQL instance and execute below command, don't forget to update MASTER_LOG_FILE and MASTER_LOG_POS values which you got from executing SHOW MASTER STATUS command.

On mysql-master-1

Update MASTER_LOG_FILE and MASTER_LOG_POS and then execute this on MySQL instance running on mysql-master-1 docker container

CHANGE MASTER TO
  MASTER_HOST='mysql_master_2',
  MASTER_PORT=3306,
  MASTER_USER='replicator',
  MASTER_PASSWORD='rotacilper',
  MASTER_LOG_FILE='[log_file_name_from_mysql-master-2]',
  MASTER_LOG_POS=[log_position_from_mysql-master-2],
  GET_MASTER_PUBLIC_KEY=1;

and then

START SLAVE;

On mysql-master-2

Update MASTER_LOG_FILE and MASTER_LOG_POS and then execute this on MySQL instance running on mysql-master-2 docker container

CHANGE MASTER TO
  MASTER_HOST='mysql_master_1',
  MASTER_PORT=3306,
  MASTER_USER='replicator',
  MASTER_PASSWORD='rotacilper',
  MASTER_LOG_FILE='[log_file_name_from_mysql-master-1]',
  MASTER_LOG_POS=[log_position_from_mysql-master-1],
  GET_MASTER_PUBLIC_KEY=1;

and then

START SLAVE;

Step 5 : Insert Records on mysql-master-1 and see Replication in action

By performing the steps 1 to 4, we have set the replication from mysql-master-1 to mysql-master-2 and vice versa, now whatever changes are done on any one of the MySQL instance will be replicated to other MySQL instance automatically.

We create a users table under test database on mysql-master-1 and insert 20 records in users table

USE test
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL);
INSERT INTO users (name)
VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie'),
    ('David'),
    ('Eve'),
    ('Frank'),
    ('Grace'),
    ('Hannah'),
    ('Ivy'),
    ('Jack'),
    ('Karen'),
    ('Liam'),
    ('Mia'),
    ('Noah'),
    ('Olivia'),
    ('Paul'),
    ('Quincy'),
    ('Rachel'),
    ('Sophia'),
    ('Thomas');

Records are replicated on mysql-master-2

Step 5a : Create and execute a MySQL procedure on mysql-master-1 and see Replication in action

DELIMITER $$

Next we create a procedure insert_users which inserts 10,000 records in users table

CREATE PROCEDURE insert_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10000 DO
        INSERT INTO users (name) VALUES (CONCAT('User_', i));
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

And then execute the procedure

CALL insert_users();

Below screenshot shows replication in progress and after some time all 10,000 records are replicated on mysql-master-2

Step 6 : Testing the replication in opposite direction, from mysql-master-2 to mysql-master-1 (Bidirectional data replication)

We insert 2 records in users table on mysql-master-2 , and those 2 records are replicated to mysql-master-1, as shown in screenshot below.

Step 6a : Testing the replication in opposite direction, from mysql-master-2 to mysql-master-1 (Bidirectional data replication)

Next we create a table da_events on mysql-master-2 and insert a single record in the table.

CREATE TABLE da_events (id INT AUTO_INCREMENT PRIMARY KEY, event_type VARCHAR(200));

INSERT INTO da_events VALUES (1, 'FETCH_CATEGORY');

As we can see from screenshot the data is replicated on mysql-master-1

References :

1. https://github.com/eMahtab/mysql-master-slave-replication