Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Sunday, 21 January 2018

Logical Architecture of Master-Master Replication

How Does Native MySQL Master-to-Master Replication Work

Master-Master replication is working is two mode . 
1: Active-Active Mode 
2: Active-Passive Mode

In both scenario the architecture/Configuration is same but in case of active-active mode we need to more attention on monitoring .

As I already shown the flow diagram of Master -Master replication in Fig1.

Fig1:Architecture of Master-Master Replication
There are two important threads working in replication.

1: Slave_IO Thread 
2: Slave_SQL Thread

Slave_IO thread reads the binlog file written by other Master and write the same in it's Relay log and then Slave_SQL thread read it's own relay log file and execute the transactions .

Active-Passive Mode:-

In this mode one master is Live and another one is standby only . Transactions are performed only on one master.
eg. We are considering Master1 is active(Live) and Master2 is Passive(Standby - not active).

At a time only master1 writing a binlog (MySql-binlog 1) on master1 accordingly this binlog is read by Slave_IO thread of Master2 and written in Relay log(RelayLog 2) of master2 . After that Slave_SQL thread of master2 read the Relay Log2 and execute the transaction on master2.

And so on the data of Master1 has been replicated on Master2.

Active-Active Mode:- 

In this mode both master server are live, it means simultaneous transaction can perform in both master.

In this mode two important parameter is come in knowledge.
auto_increment_increment= 2
auto_increment_offset  = 1 
[ For more details about these parameters work and how it configure, please Click Here ]

This parameter maintains the consistency of primary Key Value and simultaneous transaction must be replicated with same transaction id.

At a time master1 writing a binlog (MySql-binlog 1) on master1 and  master2 writing a binlog (MySql-binlog 2) on master2. 
Slave_IO thread of master2 read "Mysql-binlog 1" and write a RelayLog2 on master2 .After that  Slave_SQL thread of master2 read the Relay Log2 and execute the transaction on master2. 
As same as  Slave_IO thread of master1 read "Mysql-binlog 2" and write a RelayLog1 on master1. After that  Slave_SQL thread of master1 read the Relay Log1 and execute the transaction on master1. 

No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...