Multi-master + slaves replication in MySQL I've been playing around with MySQL and tried to create replication topology according to the following picture. Slaves are used as read servers and Masters are used as both read and write servers.

 

The problem is that it does not work. If there is a write made on one of the masters, the other master does not redistribute the statements/rows to its slave.

What's the problem ? I'll be grateful for any suggestions :)
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


Oct 09, 2013 : Multi-master and multi slave
Jan 31, 2013 : Master to Slave to Slave Configuration in MySQL
Feb 06, 2012 : Converting 3 Master + 1 Slave to 4 Master - Any advice?
Dec 17, 2011 : Mysql Master-Master Replication Topologies on &gt;2 machines


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 &amp; 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


Jul 06, 2012 :  Screwed up replication by sharing server ids
Dec 27, 2011 : Mysql thinks Master &amp; Slave have the same server-id
Dec 07, 2011 : Master and Slave having same id&#39;s


As long as M1, M2, S1, S2 all have unique server-ids, you should have no issues.