MySQL replication allows you to set in quite easy way “infinite” chain of replication. Which can be very useful in some cases. Especially if you need to make bigger changes in architecture without downtime.

We used this A->B->C replication chain for the first time when we needed to move from one cloud provider to another – we used:

  • (A) old master on old cloud ->
    • (B) new intended master on new cloud ->
      • (C) replicas on new cloud for apps

And currently we set it for new app development with new version of MySQL:

  • (A) old 5.5 Master ->
    • (B) 5.7 replica/master ->
      • (C) 5.7 replicas for apps

How to do it:

  1. set your A database as master
  2. create manually B replica from A
  3. on B replica set:
    • log_slave_updates=ON
    • set “expire_logs_days” to some reasonable number of days – depends mainly on disk space
    • allow bin_logs
    • set other useful settings used normally on master db as described in (1)
  4. create manually C replica from B as described in (2) – meaning – you have to make dump on B for C with replication stopped and tables locked against changes on B and note master status of B and use it to set master for C