Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Please run SHOW VARIABLES LIKE 'server_id'; for all three servers and post them in your question. –  RolandoMySQLDBA Sep 26 '12 at 16:57
    
Please run SHOW VARIABLES LIKE 'log_slave_updates'; and post that in the question as well. –  RolandoMySQLDBA Sep 26 '12 at 16:59
    
please find info above –  James Douglas Sep 26 '12 at 17:40
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
thank you very much for your explanation –  James Douglas Sep 28 '12 at 15:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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