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
SHOW VARIABLES LIKE 'server_id';
for all three servers and post them in your question.SHOW VARIABLES LIKE 'log_slave_updates';
and post that in the question as well.