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.
All you need to do is add this to each Master's
and restart mysqld.
Give it a Try !!!
I have discussed log-slave-updates in this situation before
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.