0

MySQL Master-Master Replication

Introduction

Mysql replication is the process of mirroring the database changes in another instance, which will help to keep a copy of data in another location/or instance in case of the primary DB server failure. The replicated copy is also can act as a read-only replica, or another read-write instance. In this article, we will discuss how we can set up a master-master replication. which means, both DB instance can be writable and readable by the application and the changes will be replicated with each other.

Prerequisites

  • 2 Ubuntu 18.04 LTS with SSH access and sudo permission
  • Mysql server and client installed (please follow this link if you don’t know how to install MySQL in Ubuntu)
  • Static IP configured.

Step 1 – Common on Both servers

By default, the MySQL process will only accept connections on localhost (127.0.0.1). edit /etc/mysql/my.cnf on both server and comment out # bind-address = 127.0.0.1 and restart MySQL server by running sudo service mysql restart

Step 2

On Server 1

Update the replication configuration in /etc/mysql/my.cnf as below

server_id           = 1
log_bin             = /varlib/mysql/mysql-bin.log
log_bin_index       = /varlib/mysql/mysql-bin.log.index
relay_log           = /varlib/mysql/mysql-relay-bin
relay_log_index     = /varlib/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1

Restart Mysql server by running sudo service mysql restart

login to MySQL server and create a replication user as below mysql -u root -p

CREATE USER 'replicator'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<server 2 ip address>';

Once you have created the MySQL replication user, follow the same procedure in server 2

On Server 2

Update the replication configuration in /etc/mysql/my.cnf as below

server_id           = 2
log_bin             = /varlib/mysql/mysql-bin.log
log_bin_index       = /varlib/mysql/mysql-bin.log.index
relay_log           = /varlib/mysql/mysql-relay-bin
relay_log_index     = /varlib/mysql/mysql-relay-bin.index
expire_logs_days    = 60
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2

Restart Mysql server by running sudo service mysql restart

login to MySQL server and create a replication user as below mysql -u root -p

CREATE USER 'replicator'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<server 1 IP address>';

Now we have completed the configuration in both servers, now we need to tell the Mysql to connect to master. For that first get the master details from both the servers by SHOW MASTER STATUS; command

The output from server 1
mysql> show master status;
+----------------------+----------+------------------------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB                 | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+------------------------------+------------------+-------------------+
| mysql-bin.00001 | 23 |  |                  |                   |
+----------------------+----------+------------------------------+------------------+-------------------+
1 row in set (0.00 sec)

Note down the file name and position from both servers.

In our case, mysql-bin.00001 is the file and 23 is the position in server 1

The output from server 2
mysql> show master status;
+----------------------+----------+------------------------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB                 | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+------------------------------+------------------+-------------------+
| mysql-bin.00001 | 10 |  |                  |                   |
+----------------------+----------+------------------------------+------------------+-------------------+
1 row in set (0.00 sec)

In our case, mysql-bin.00001 is the file and 10 is the position in server 2

Step 3

Promote both servers to master. SSH to server 1 and login to MySQL server mysql-u root -p

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = '<server 2 IP>',MASTER_USER = 'replicator', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = '<server 2 bin log name>', MASTER_LOG_POS = <server 2 log pos>;
mysql> START SLAVE;

SSH to server 2 and login to MySQL server mysql-u root -p

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST = '<server 1 IP>',MASTER_USER = 'replicator', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = '<server 1 bin log name>', MASTER_LOG_POS = <server 1 log pos>;
mysql> START SLAVE;

Not we have migrated both servers as master, Check the replication status

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <remote server IP>
                  Master_User: mainuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 59033533
               Relay_Log_File: mysql-relay-bin.000285
                Relay_Log_Pos: 5577990
        Relay_Master_Log_File: mysql-bin.000026
             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: 1146
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 22109049
              Relay_Log_Space: 47502883
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 51fe90c4-7b55-76e4-92c2-2c44fd7a4fed
             Master_Info_File: /var/lib/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 200123 22:50:11
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

Both Slave_IO_Running: Yes and Slave_SQL_Running: Yes

should be Yes

Conclusion

We have successfully configured MySQL master-master replication. please test the replication by creating a DB on any of the servers. It should be available in another too

Leave a Reply