How to Setup MySQL Master-Master Replication (Circular Replication)

Master-Master Replication (Circular Replication) also known as ring topology in MySQL, this setup requires two or more MySQL servers which act as a master. All masters receive writes and generate binlogs.

Below points should be considered while doing the master-master setup.

You need to set an auto-increment offset on each server to avoid primary key collisions.
There is no conflict resolution.
MySQL Replication currently does not support the atomicity of updates across two different servers.

We have already set up 2 mysql instances in test-machine01 server running on port 3306 (Server_ID : 1) & 3307 (Server_ID : 3), We have also changed the mysql prompt to Master-1, Master-2, and Replica for easy understanding.

Hostname	IP	Role	Server_ID	Port	Installation Link
test-machine01	192.168.114.177	Master-1	1	3306	Click Here
test-machine01	192.168.114.177	Master-2	3	3307	Click Here

Master-1: Below are the Master-1 configuration parameters

mysql> prompt mysql Master-1>
PROMPT set to 'mysql Master-1>'
mysql Master-1>

mysql Master-1>select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 13ecba9c-444a-11eb-a397-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql Master-1>
mysql Master-1>
mysql Master-1>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql Master-1>select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql Master-1>


[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 1
socket=/var/lib/mysql/mysql.sock
pid_file=/var/run/mysqld/mysqld.pid

# Data Storage
datadir=/var/lib/mysql
innodb_directories="/u01/mysql/"

#Logging
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log
expire_logs_days = 7
relay_log = /var/lib/mysql/relay_bin01
relay_log_index = /var/lib/mysql/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1
auto_increment_increment=2
auto_increment_offset=1

Master-1: Below are the Master-2 configuration parameters

mysql> prompt mysql Master-2>
PROMPT set to 'mysql Master-2>'
mysql Master-2>

mysql Master-2>select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 98411332-6aad-11eb-809a-000c29f9d9e6 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql Master-2>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql Master-2>select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

mysql Master-2>


[mysqld@inst2]
#General
user = mysql
port=3307
server_id = 3
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid

# Data Storage
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
innodb_directories="/u01/mysql/"
plugin_dir=/usr/lib64/mysql/plugin

#Logging
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-2/data/relay_bin01
relay_log_index = /u01/mysql-2/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1
auto_increment_increment=2
auto_increment_offset=2

Step 1. Configuration Setup: Setup above parameters in masters configuration file (my.cnf) and restart both mysql instance.

[root@test-machine01 ~]# systemctl restart mysqld@inst1
[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl restart mysqld@inst2
[root@test-machine01 ~]#

Step 2.: Create Replication User: Create replication user in both master mysql instances. This user will be used by replica to connect to the master instance.

mysql Master-1>CREATE USER IF NOT EXISTS 'repluser'@'%' IDENTIFIED BY 'Password123#@!';
Query OK, 0 rows affected (0.06 sec)
 
mysql Master-1>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql Master-1>ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';
Query OK, 0 rows affected (0.00 sec)
 
mysql Master-1>flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql Master-1>
 
mysql Master-2>CREATE USER IF NOT EXISTS 'repluser'@'%' IDENTIFIED BY 'Password123#@!';
Query OK, 0 rows affected (0.06 sec)
 
mysql Master-2>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql Master-2>ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';
Query OK, 0 rows affected (0.00 sec)
 
mysql Master-2>flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql master2 >

Step 3. Reset Master: Execute reset master command to reset Executed_Gtid_Set variable in both masters mysql instances.

Master-1

mysql Master-1>show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000004 |      196 |              |                  | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql Master-1>
mysql Master-1>reset master;
Query OK, 0 rows affected (0.07 sec)

mysql Master-1>show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql Master-1>

Master-2

mysql Master-2>show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |      196 |              |                  | 98411332-6aad-11eb-809a-000c29f9d9e6:1-9 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql Master-2>
mysql Master-2>
mysql Master-2>reset master;
Query OK, 0 rows affected (0.07 sec)

mysql Master-2>show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql Master-2>
mysql Master-2>

Step 4. Dummy Transaction: Execute dummy transaction in both masters mysql instances to increment Executed_Gtid_Set variable to 1 and note down Executed_Gtid_Set value.

Master-1

mysql Master-1>use repl_test;
Database changed
mysql Master-1>create table dummy ( id int);
Query OK, 0 rows affected (0.06 sec)

mysql Master-1>show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000001 |      363 |              |                  | 13ecba9c-444a-11eb-a397-000c29f9d9e6:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql Master-1>

Master-2

mysql Master-2>use repl_test;
Database changed

mysql Master-2>create dummy test ( id int);
Query OK, 0 rows affected (0.07 sec)

mysql Master-2>show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000001 |      363 |              |                  | 98411332-6aad-11eb-809a-000c29f9d9e6:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql Master-

Step 5: Update Executed_Gtid_Set: Update variable gtid_purged with Executed_Gtid_Set value from the above output and update master information and then start replica.

Master-1

mysql Master-1>
mysql Master-1>SET @@GLOBAL.gtid_purged = "98411332-6aad-11eb-809a-000c29f9d9e6:1";
Query OK, 0 rows affected (0.00 sec)

mysql Master-1>

mysql Master-1>CHANGE MASTER TO MASTER_HOST='192.168.114.177',MASTER_USER='repluser',MASTER_PASSWORD='Password123#@!',MASTER_PORT=3307,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql Master-1>start replica ;
Query OK, 0 rows affected (0.00 sec)

mysql Master-1>

Master-2

mysql Master-2>SET @@GLOBAL.gtid_purged = "13ecba9c-444a-11eb-a397-000c29f9d9e6:1";
Query OK, 0 rows affected (0.07 sec)

mysql Master-2>
mysql Master-2>CHANGE MASTER TO MASTER_HOST='192.168.114.177',MASTER_USER='repluser',MASTER_PASSWORD='Password123#@!',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql Master-2>start replica ;
Query OK, 0 rows affected (0.01 sec)

mysql Master-2>

Step 6: Check Status: Execute command show replica status to validate the status.

Master-1

mysql Master-1>show replica status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.114.177
                  Master_User: repluser
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 363
               Relay_Log_File: relay_bin01.000002
                Relay_Log_Pos: 409
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 363
              Relay_Log_Space: 614
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3
                  Master_UUID: 98411332-6aad-11eb-809a-000c29f9d9e6
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1,
98411332-6aad-11eb-809a-000c29f9d9e6:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

mysql Master-1>

Master-2

mysql Master-2>show replica status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.114.177
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 363
               Relay_Log_File: relay_bin01.000002
                Relay_Log_Pos: 409
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 363
              Relay_Log_Space: 614
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1,
98411332-6aad-11eb-809a-000c29f9d9e6:1
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

mysql Master-2>

Step 7: Test replication configuration: Execute transaction in both master mysql instances and validate if the same transaction replicated in other master instances.

Master-1

mysql Master-1>use repl_test;
Database changed

mysql Master-1>create table repl_test8 as select *from repl_test7;
Query OK, 28 rows affected (0.07 sec)
Records: 28  Duplicates: 0  Warnings: 0

Master-2

mysql Master-2>show tables ;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| repl_test           |
| repl_test1          |
| repl_test2          |
| repl_test3          |
| repl_test4          |
| repl_test5          |
| repl_test6          |
| repl_test8          |
| test                |
+---------------------+
9 rows in set (0.00 sec)

Master-2

mysql Master-2>create table repl_test9 as select *from repl_test8;
Query OK, 28 rows affected (0.01 sec)
Records: 28  Duplicates: 0  Warnings: 0

mysql Master-2>

Master-1

mysql Master-1>show tables ;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| repl_test1          |
| repl_test2          |
| repl_test3          |
| repl_test4          |
| repl_test5          |
| repl_test6          |
| repl_test7          |
| repl_test8          |
| repl_test9          |
| test                |
+---------------------+
10 rows in set (0.01 sec)

mysql Master-1>

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.