Site icon Database Tutorials

MySQL master-master replication

 

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

 

MySQL should be installed on both servers to setup master-master replication.

Step 1: Add these options in my.cnf of master1/slave2

 

Restart mysql service in master1

 

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

 

Restart the mysql server in master2

 

Step3:  Create user for slave2 in Master1 DB

 

Step 4:  Create user for slave1 in Master2 DB

 

Step 5: Make note of binlog file and position in Master1

use this noted file and position in next step.

 

Step6: Update replication user, master info and binlog position in Slave1 (B.B.B.B)

 

Step7: Start slave and check the replication in slave1 server

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

use this noted file and position in next step.

 

Step9: Update replication user, master info and binlog position in Slave2 (A.A.A.A)

 

Step10: Start slave and check the replication in slave2 server

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.

Exit mobile version