1

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.

Replication

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 :)

3

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.

  • Sounds good. I am gonna try it as soon as I get my db topology up again. 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 ? – no6 Mar 7 '14 at 16:32
  • @no6 if M1 sends an update to M2, M2 technically will send the update back to M1, because it has to -- binlogs are continuous streams of data, and the same stream is sent to all slaves of a given master -- but when M1 realizes that it was the originator of the event (as it can see from the server_id value of the binlog event) M1's slave 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. – Michael - sqlbot Mar 7 '14 at 20:12
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.