Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Master-Master Replication setup
Replication makes data highly available for applications and websites, master-master replication is an active-active cluster kind of solution which can be useful to load balance reads and writes. If we have only two nodes, then master-master replication is a good option.
Consider these two servers for reference
1 2 3 | Master1/Slave2 - A.A.A.A(IP address) Master2/Slave1 - B.B.B.B(IP address) |
MySQL should be installed on both servers to setup master-master replication.
Step 1: Add these options in my.cnf of master1/slave2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | server-id=104 log-bin=mysql-bin relay-log=relay-bin sync_binlog = 1 relay-log-info-repository = TABLE relay_log_purge = 1 log-slave-updates auto_increment_increment = 2 auto_increment_offset = 1 |
Restart mysql service in master1
1 | service mysql restart or /etc/init.d/mysql restart |
server_id should be an unique number in the replication setup, auto_increment is used to avoid conflicts in data inserts, log-bin to enable binlog , relay-log to enable relay binlog, sync_binlog to avoid data loss or maintain consistency, relay-log-info-repository to store binlog file position details in a table, relay_log_purge to purge relay-binlog after applying sql in slave.
Step 2: Add these options in my.cnf of Master2/Slave1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | server-id=105 log-bin=mysql-bin relay-log=relay-bin sync_binlog = 1 relay-log-info-repository = TABLE relay_log_purge = 1 log-slave-updates auto_increment_increment = 2 auto_increment_offset = 2 |
Restart the mysql server in master2
1 | service mysql restart or /etc/init.d/mysql restart |
Step3: Create user for slave2 in Master1 DB
1 2 3 4 5 | create user 'user_name1'@'B.B.B.B' identified by 'your_password1'; grant replication slave on *.* to 'user_name1'@'B.B.B.B' identified by 'your_password1'; ; flush privileges; |
Step 4: Create user for slave1 in Master2 DB
1 2 3 4 5 | create user 'user_name2'@'A.A.A.A' identified by 'your_password2'; grant replication slave on *.* to 'user_name2'@'A.A.A.A' identified by 'your_password2'; flush privileges; |
Step 5: Make note of binlog file and position in Master1
1 | mysql> show master status; |
use this noted file and position in next step.
Step6: Update replication user, master info and binlog position in Slave1 (B.B.B.B)
1 2 3 | stop slave; CHANGE MASTER TO MASTER_HOST='A.A.A.A', MASTER_USER='user_name1', MASTER_PASSWORD='your_password1', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=702; |
Step7: Start slave and check the replication in slave1 server
1 2 | start slave; show slave status\G |
Both I/O and SQL thread should be running.
check replication by doing table update in master1
If replication is working fine from Master1-Slave1, then go to next step
Step8: Make note of binlog file and position in Master2
1 | mysql> show master status; |
use this noted file and position in next step.
Step9: Update replication user, master info and binlog position in Slave2 (A.A.A.A)
1 2 3 | stop slave; CHANGE MASTER TO MASTER_HOST='B.B.B.B', MASTER_USER='user_name2', MASTER_PASSWORD='your_password2', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=702; |
Step10: Start slave and check the replication in slave2 server
1 2 3 | start slave; show slave status\G |
Both I/O and SQL thread should be running.
check replication by doing table update in master2
If replication is working fine from Master2-Slave2, then master-master replication setup is complete.
Hi Jagannath,
I think, log_slave_updates parameter is missing from configuration.
Please check