MYSQL 5.6 Master-Master replication setup

This Blog is in continuation of replication setup which we have done in previous blog Master-Slave replication. In previous blog we have configured master-slave replication from NODE1 to NODE2 and in this blog we are going to configure master-slave replication from NODE2 to NODE1, so that each node will work as Master as well as Slave.

Again, this tutorial will use the following Nodes:

NODE1(192.168.56.110) : Master Database(In this case it is Slave)
NODE2(192.168.56.121) : Slave Database (In this case it is Master)

1: Configure the Instance Database (NODE 2)

Edit your configuration file “/etc/my.cnf” to add/edit following parameter on your slave server (NODE 2) to Enable Binary log:

log-bin=/var/lib/mysql_logs/bin-log/Node2-bin-log

Restart mysql server “/etc/init.d/mysql restart” 

Create user for replication and give it necessary grants 

Now login to mysql and goto database and apply read lock on all tables. So that data would remain consistent with binlog position in backup. Take the backup > note down the mater binary location> unlock tables.

Information Gathered (this position will be required while configuring slave) 

Binary log file : Node2-bin-log.000003

Position           : 120

Transfer the backup to master machine(NODE1)

2: Configure the Instance Database (NODE 1)

Edit your configuration file “/etc/my.cnf” to add/edit following parameter on your master server (NODE 1) to Enable Relay log:

relay-log=/var/log/mysql/relay-bin

Restart mysql server “/etc/init.d/mysql restart”

Import the database that you have exported from slave database(NODE 2) 

Connect you MYSQL prompt again and run below command .It has information which we captured while setting up master previously.

CHANGE MASTER TO MASTER_HOST='192.168.56.121',MASTER_USER='repl', MASTER_PASSWORD='google@123', MASTER_LOG_FILE='Node2-bin-log.000003', MASTER_LOG_POS= 120;

Now, Node2 is configured as Master and Node 1 as Slave. In previous blog you have already configured Node1 as master and Node2 as slave.

Hence your master-master replication has been setup completely.Transaction will in sync on both the instances.Thanks!!