2

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
3
  • Please run SHOW VARIABLES LIKE 'server_id'; for all three servers and post them in your question. Sep 26, 2012 at 16:57
  • Please run SHOW VARIABLES LIKE 'log_slave_updates'; and post that in the question as well. Sep 26, 2012 at 16:59
  • please find info above Sep 26, 2012 at 17:40

1 Answer 1

0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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