Forcing a Mysql slave to delay seconds or minutes behind a master can be useful for many reasons:
- test your application to see how it reacts in case of a lagged replication
- release financial data after a period of validation
- build some sort of wayback machine
- protect against oops
etc.
Such a feature was suggested by Jeremy Zawodny in 2001 and asked for some years (see http://bugs.mysql.com/bug.php?id=28760) and was available as a patch for Mysql 5.
Thanks to Kay Röpke it is going to be available in Mysql 6 with something like this:
CHANGE MASTER TO MASTER_DELAY = [TIME];
where [TIME] is of datatype TIME.
http://forge.mysql.com/wiki/ReplicationFeatures/DelayedReplication#TIME-...
However you might not be ready to switch to Mysql6 very soon, so here you can find a tool to simulate the same behaviour:
http://www.maatkit.org/doc/mk-slave-delay.html
mk-slave-delay makes a MySQL slave server lag behind its master, ie. to hold slavehost one minute behind its master for ten minutes:
mk-slave-delay --delay 1m --interval 15s --run-time 10m slavehost
It bases the delay on binlog positions in the slave's relay logs by default, so there is no need to connect to the master. This works well if the IO thread doesn't lag the master much, which is typical in most replication setups; the IO thread lag is usually milliseconds on a fast network.
As the author says mk-slave-delay is generally low-risk. It simply starts and stops the replication SQL thread. Note: if you have some monitor in place like Nagios, this might cause such systems to think the slave is having trouble.