PROBLEM
Looking at the diagram and seeing the red line, I immediately thought of one thing that's missing.
The root of the problem is that both masters require log-slave-updates in my.cnf. Why ?
When a master receives a binlog event from its relay logs, it cannot replicate to any other Slave if it cannot record a binlog event into its local binary logs. For a MySQL Instance to be both Master and Slave, it has to be allowed to record Slave-based binlog events into its Binary Logs so other Slaves can read the same event.
SOLUTION
All you need to do is add this to each Master's my.cnf
[mysqld]
log-slave-updates
and restart mysqld.
Give it a Try !!!
CAVEAT #1
I have discussed log-slave-updates in this situation before
CAVEAT #2
You need to adjust the diagram for another reason. The blue arrows need to be reversed. Why ?
- In a diagram, any DB Server can have multiple arrows pointing out, but can only have one arrow coming in. This is due to a Slave only having one Master.
- MySQL Replication can only run CHANGE MASTER TO from one Master.
UPDATE 2014-03-07 12:12 EST
In your last comment, you said
What about updates between masters M1 & M2. M1 and M2 are both slaves to each other. If M1 sends update to M2. Will M2 send the same update back to M1? Will it go on forever ?
Yes, M2 will send the same update back to M1.
However, the update will not get executed again on M1. Why ?
Each binlog event and each relay log event includes the server-id of the event. This allows MySQL Replication to work as follows for a Master that is also a Slave:
- Server sees relay log event
- Server examines server-id of relay log event
- If server-id of relay log event is the same as my server-id, do not execute
- If server-id of relay log event is not the same as my server-id
- Execute relay log event
- If log-slave-updates is enabled, record relay log event in local binlogs
I have discussed server-id issues like this before
As long as M1, M2, S1, S2 all have unique server-ids, you should have no issues.
You are almost there. I guess you have missed the auto_increment_increment & auto_increment_offset variables.
Set auto increment increment to the total number of servers.
Set auto_increment_offset to a number between 1 and auto increment increment for each server.
example:
Server 1: auto increment increment = 2, auto_increment_offset = 1
Server 2: auto increment increment = 2, auto_increment_offset = 2
Best Answer
The idea of semi-synchronous is to wait for acknowledgement from at least one slave and since you would be using master-master so you might have to install both plugins i.e. for master & slave on both nodes. This is because when one master would write the other would behave like it's slave and same goes for the other case. Hope it helps