2

I have 4 servers: A, B, C and D.

A and B are in a master-master setup.

C is a slave to B and D is a slave to C.

Changes on A or B get to all nodes other than D. I need them to get to D

Changes on C get to D but obviously they don't go to A or B which is fine.

After some reading I found that I needed the log-slave-updates option set on C, this way C would log the changes replicated to it and then D could read those and make the same updates.

I have set the option to on and D is waiting for it's master to send events but changes from A or B are still not getting to D.

6
  • Please run SHOW GLOBAL VARIABLES LIKE 'server_id'; on all servers and tells us what server's server_id is. Dec 30, 2014 at 14:38
  • Servers A, B, C and D have server-ids of 1, 2, 3 and 4 Dec 30, 2014 at 14:45
  • Is log-slave-updates configured on A, B and C ? Dec 30, 2014 at 14:51
  • No it's only on C Dec 30, 2014 at 15:12
  • Do I need it on A and B? If so does that require a restart before it will work? Dec 30, 2014 at 15:32

1 Answer 1

3

With log-slave-updates on server C, only updates written directly on B will go to server D.

Server A and Server B require log-slave-updates, otherwise, none of their updates will reach C. In general, any Master that is also a Slave must have log-slave-updates in order to relay binlog events from distant servers.

I have discussed this before

Restarting mysqld is required on servers A and B for the setting to take effect

Let me explain further

Your Setup

  • A is Slave of B (log-slave-updates off)
  • B is Slave of A (log-slave-updates off)
  • C is Slave of B (log-slave-updates on)
  • D is Slave of C
  • A, B, and C are Masters

You run INSERT INTO tblname on Server A what happens ?

Since B is Slave of A

  • B's IO Thread reads A's binary logs
  • B's IO Thread gets INSERT INTO tblname
  • B's IO Thread records it in its relay logs
  • B's SQL thread reads relay logs
  • B's SQL thread gets INSERT INTO tblname
  • B's SQL thread executes INSERT INTO tblnameit
  • Since B does not have log-slave-updates defined, B does not record INSERT INTO tblname into its binary logs. C's IO Thread never sees INSERT INTO tblname coming from server A.

You run INSERT INTO tblname on Server B what happens ?

Since A and C Slaves of B and D is a Slave of C

  • A's IO Thread reads B's binary logs
  • A's IO Thread gets INSERT INTO tblname
  • A's IO Thread records it in its relay logs
  • A's SQL thread reads relay logs
  • A's SQL thread gets INSERT INTO tblname
  • A's SQL thread executes INSERT INTO tblnameit
  • C's IO Thread reads B's binary logs
  • C's IO Thread gets INSERT INTO tblname
  • C's IO Thread records it in its relay logs
  • C's SQL thread reads relay logs
  • C's SQL thread gets INSERT INTO tblname
  • C's SQL thread executes INSERT INTO tblnameit
  • Since C has log-slave-updates enabled, C records INSERT INTO tblname in its binary logs
  • D's IO Thread reads C's binary logs
  • D's IO Thread gets INSERT INTO tblname
  • D's IO Thread records it in its relay logs
  • D's SQL thread reads relay logs
  • D's SQL thread gets INSERT INTO tblname
  • D's SQL thread executes INSERT INTO tblnameit

Based on this description, if you had any slaves coming from A, those slaves would never see INSERT INTO tblname coming from B.

Therefore, you need log-slave-updates on A and B as well as C.

2
  • The strange thing is they do reach C. I am testing by creating a new DB on A. The new DB appears on B and C but not D Dec 30, 2014 at 16:18
  • Thanks, I have marked as answer even though I am waiting for the restart to know for sure. Dec 31, 2014 at 8:53

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.