Flite Culture

Replacing Pt-slave-delay With MASTER_DELAY in MySQL 5.6

In the past I have used pt-slave-delay when I want to maintain an intentionally delayed replica. Now that I have upgraded to MySQL 5.6 I am switching over to use MASTER_DELAY, which is a built-in feature that does the same thing.

For example I can replace this:

1
pt-slave-delay --delay 7d --interval 1m --daemonize

With this:

1
2
3
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 604800;
START SLAVE;

The implementation is similar: the IO thread copies the events to the relay log as fast as normal, but the SQL thread only executes events older than the defined lag. The process to fast-forward the replica should be similar as well.

So far I see a couple of advantages of using MASTER_DELAY:

  • Running stop slave and start slave manually can’t cause the replica to catch up beyond the defined lag, which could happen with pt-slave-delay
  • No need to monitor the daemon (I used monit for this when running pt-slave-delay)

Since MASTER_DELAY is part of CHANGE MASTER it is persisted with the other replication configuration data, thus it doesn’t need to (and can’t) be defined in my.cnf, and it survives a reboot.


Comments