MySQL – Replication Slave of Slave Not Receiving Updates

MySQLmysql-5.6replication

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.

Best Answer

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.

Related Question