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 :)
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 my.cnf
[mysqld]
log-slave-updates
and restart mysqld.
I have discussed log-slave-updates in this situation before
Oct 09, 2013 : Multi-master and multi slaveJan 31, 2013 : Master to Slave to Slave Configuration in MySQLFeb 06, 2012 : Converting 3 Master + 1 Slave to 4 Master - Any advice?Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machinesYou need to adjust the diagram for another reason. The blue arrows need to be reversed. Why ?
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:
I have discussed server-id issues like this before
Jul 06, 2012 : Screwed up replication by sharing server idsDec 27, 2011 : Mysql thinks Master & Slave have the same server-idDec 07, 2011 : Master and Slave having same id'sAs long as M1, M2, S1, S2 all have unique server-ids, you should have no issues.
IO_thread will discard the event and not write it to M1's relay log, thus the loop is broken when the event arrives back at the originating server. See sql/rpl_slave.cc in the source files if you are curious as to the implementation details.
Commented
Mar 7, 2014 at 20:12
I think first you have to setup master-master replication and later add slaves to respective master server (tree topology)
Please read different types of replication topologies mentioned here:
http://dev.mysql.com/doc/refman/5.1/ja/replication-topology.html
Also follow the steps mentioned here to setup master-master replication
https://www.digitalocean.com/community/articles/how-to-set-up-mysql-master-master-replication
