MySQL master/slave chain

Have you ever needed to create a MySQL databases replication chain like A->B->C where B is slave of A and master of C? Me neither, until yesterday.

Since it took us about an afternoon to make it works (along with our DBAs, so we're not alone ;)) let's share some knowledge.

A very brief recap of how MySQL replication works:

  1. slave I/O thread connects to the master, gets the new information from the binlog files and stores them in the relay log;
  2. slave SQL thread reads the relay log and applies the changes to the slave database, without changing the slave binlog files.
That said, B replicates correctly from A, but C is unable to replicate from B because B doesn't change its binlog files with updates coming from A, because there's no changes done directly on B.

In order to make the chain works, you need to add the parameter log-slave-updates on B configuration: that will reply the changes from relay log to binlog, and so C will see the changes it needs to correctly replicate.

PS: mysqldump --master-data (executed on the slave server against the master) would help you set up the correct information for replication.

No comments: