Configure BiDirectional MySQL Replication (Master-Master)

- February 15, 2022

Now a days HA(High Availability) and DR(Disaster Recovery) Solutions are mandatory and required for mission critical databases. In MySQL world , DR solution are provided using the Replication (Master-Master).

In this blog, We will discuss about setting up new Master-Master setup.

1) Preparing Master Server 1(mysqlmaster01) :

1.a) Make Changes in my.cnf file.

cp -pr /etc/my.cnf /etc/my.cnf_<ddmmyyyy>

Add below lined to my.cnf
vi /etc/my.cnf

server-id=1
log-bin
gtid_mode=ON
enforce-gtid-consistency
auto-increment-increment = 2
auto-increment-offset = 1
expire_logs_days = 10
relay-log = /var/lib/mysql/relaylog/mysql-relay
relay-log-recovery
binlog_format=mixed
replicate-do-db=mysqldb

1.b) Restart MYSQL server to make changed effective.

service mysqld restart

1.c) Verify binary logs:

SHOW MASTER LOGS;
Show master Status;
ls -lrt /var/log/*bin*

1.d) Create Replication user in Master Server 1:

mysql> CREATE USER 'repl'@'198.168.0.2' IDENTIFIED BY 'P@ssw0rd-LAMS';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'198.168.0.2';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;

2) Preparing New MySQL DB as Master Server 2(mysqlmaster02):

2.a) Make Changes in my.cnf file.

cp -pr /etc/my.cnf /etc/my.cnf_<ddmmyyyy>

Add below lined to my.cnf
vi /etc/my.cnf

server-id=2
log-bin
gtid_mode=ON
enforce-gtid-consistency
auto-increment-increment = 2
auto-increment-offset = 2
expire_logs_days = 10
relay-log = /var/lib/mysql/relaylog/mysql-relay
relay-log-recovery
binlog_format=mixed
replicate-do-db=mysqldb

2.b) Restart MYSQL server to make chaged effective.

service mysqld restart

2.c) Create replication user in Master server 2:

mysql> CREATE USER 'repl'@'198.168.0.1' IDENTIFIED BY 'P@ssw0rd-LAMS';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'198.168.0.1';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;

3) Backup Database and ensure master DB with readonly mode

3.a) Start a session on the master(mysqlmaster02) by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

mysql> FLUSH TABLES WITH READ LOCK;
mysql>set @@global.read_only=true;

NOTE:Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

3.b) In a different session on the master(mysqlmaster02), use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

mysql > SHOW MASTER STATUS;

NOTE: Information from above command will be used during the replication setup on the slave server.

3.c) Backup Master server (mysqlmaster02)

From seperate putty session :

mysqldump -uroot --all-databases --set-gtid-purged=auto --triggers --routines --events --singletransaction > /root/mydump.sql

3.d) Release locking session
connect to sesssion in step 3.a .

mysql> set @@global.read_only=false;
mysql> unlock tables;
mysql> exit

4) Restoring Database to Slave DB(mysqlmaster01)

mysql -u root -p < /root/mydump.sql

5) Configure Slave on Master server 2:

mysql> CHANGE MASTER TO
MASTER_HOST='198.168.0.1',
MASTER_USER='repl',
MASTER_PASSWORD='P@ssw0rd-LAMS',
MASTER_LOG_FILE='recorded_log_file_name', ==> Use information from step 3.b
MASTER_LOG_POS=recorded_log_position; ==> Use information from step 3.b

mysql> show warnings;
mysql> SHOW SLAVE STATUS\G

tail -100f /var/log/mysql.log ==> Keep checking log on Slave Server (mysqlmaster01).
tail -100f /var/log/mysql.log ==> Keep checking log on master Server (mysqlmaster02).

6)Configure Slave on Master server 1:

mysql > SHOW MASTER STATUS; ==> Run on server mysqlmaster01

mysql> CHANGE MASTER TO
MASTER_HOST='198.168.0.2',
MASTER_USER='repl',
MASTER_PASSWORD='P@ssw0rd-LAMS',
MASTER_LOG_FILE='recorded_log_file_name', ==> Use information from above step.
MASTER_LOG_POS=recorded_log_position; ==> Use information from above step.

7) Start Slave process on both the master server :

mysql> start slave;
mysql> SHOW SLAVE STATUS\G ==> Check for Slave_IO_Running,Slave_SQL_Running,Last_errno,Last_error

you can learn more about MySql and InnoDB from below books:

MySQL Replication Simplified: Easy step-by-step examples to establish, troubleshoot and monitor replication

Introducing InnoDB Cluster: Learning the MySQL High Availability Stack

MySQL Cookbook: Solutions for Database Developers and Administrators

Effective MySQL Backup and Recovery

=================================================================

You can also , Visit our Blogs for related topic on MySQL :
1)MySQL InnoDB Cluster node Addition Issue: - Cluster.addInstance: : MySQL server has gone away (RuntimeError)
2)Install & Configure MySQL Router - MySQL InnoDB Cluster

3)Restoring a Failed member in InnoDB Cluster