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.

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

  • 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 – James Anderson Dec 30 '14 at 16:18
  • Thanks, I have marked as answer even though I am waiting for the restart to know for sure. – James Anderson Dec 31 '14 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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