Replicate from master-master setup I have MySQL Master-Master replication on 2 servers. 

I want to replicate to another slave server. 

When I configure the slave to replicate from master1, only data added on master1 is replicated.   When I added a record on master2, the record is replicated to master1 but not replicated to the slave.

Any idea how to solve this?


m1

server_id = 1
log_slave_updates = OFF

m2

server_id = 23
log_slave_updates = OFF

s3

server_id = 21
log_slave_updates = OFF
The Problem

The server option log_slave_updates needs to be enabled on both m1 and m2

The Solution

Please add the following in /etc/my.cnf on both m1 and m2

[mysqld]
log_slave_updates


then run this on both m1 and m2

service mysql restart


The Reason

Let's use your example above


m1 is Master of m2
m1 is Master of s3
m2 is Master of m1


Here is what happens when you run INSERT INTO mydb.mytable ... on m1


m2 executes INSERT
m2 records INSERT into the current binary log along with server_id=23
m1 retrieves INSERT from m2 via I/O Thread
m1 records INSERT via I/O Thread into lastest relay log
m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
m1 executes INSERT


OK, all well and good. But, why doesn't s3 get to receive anything? Because log_slave_updates is not enabled, m1 does not save the INSERT and the original server_id posting the INSERT into its own binary logs.

If log_slave_updates was enabled on m1, now follow along your example and let's see what should happen:


m2 executes INSERT
m2 records INSERT into the current binary log along with server_id=23
m1 retrieves INSERT from m2 via I/O Thread
m1 records INSERT via I/O Thread into lastest relay log
m1 retrieves INSERT from relay log via SQL Thread and examines its server_id
m1 sees that the server_id of the INSERT is 23 which is not the same as m1's server_id (1)
m1 executes INSERT
m1 records INSERT into the current binary log along with server_id=23
s3 retrieves INSERT from m1 via I/O Thread
s3 records INSERT via I/O Thread into lastest relay log
s3 retrieves INSERT from relay log via SQL Thread and examines its server_id
s3 sees that the server_id of the INSERT is 23 which is not the same as s3's server_id (21)
s3 executes INSERT


This fully explains why s3 never sees anything from m2