Most of the replicated MySQL configurations are - one master and several replicated slaves, for the sake of simplicity I'm not going to get into the discussion on shards etc... And in most cases the struggle is to keep the replication up to date. But there are scenarios when delaying the replicated slave is actually beneficial.
Delaying MySQL replicated slave is a very valuable "undo" options for the databases. For example if someone accidentally drops one of the MySQL databases or tables those databases and tables can be easily recovered from the delayed MySQL slave because the drop statement did not get the chance to propagate to the delayed replicated slave. It is also a very valuable security option in case someone hacked into the master MySQL database and dropped the database objects.
Having a MySQL replicated configuration and not using the delayed slave is simply hard to justify.
So how do we go about delaying the replicated slave ?
There are two options:
1. MySQL 5.6 (that is one from Oracle TM) that is of you are using MySQL 5.6
MySQL release 5.6 from Oracle TM actually has a new command that will force the slave to delay:
CHANGE MASTER TO MASTER_DELAY =
The "N" stands for the number of seconds.
2. Percona (TM) toolkit
Percona (TM) developed a tool kit that can be downloaded from Percona site http://www.percona.com/software/downloads/. There are several tools in the download package but we will focus on pt-slave-delay
pt-slave delay is essentially a command line tool and its also very simple to use:
pt-slave-delay --delay=120m, u=root, p=yourrootpassword, h=hostaname
Simple as that, there are a lot of options to the command but all you need if running on the same host as the database all you need is the root userid and password.
Once you issue the command you'll see the following output:
013-03-28T14:36:53 slave stopped at master position mysql-bin.002116/61079053
2013-03-28T14:37:53 slave stopped at master position mysql-bin.002116/70028546
2013-03-28T14:38:53 slave stopped at master position mysql-bin.002116/78913700
2013-03-28T14:39:53 slave stopped at master position mysql-bin.002116/88378080
2013-03-28T14:40:53 slave stopped at master position mysql-bin.002116/101285536
2013-03-28T14:41:53 slave stopped at master position mysql-bin.002116/110519229
What pt-slave-delay actually doing is periodically stopping the slave using "slave stop" command and then starting it again using "slave start" command.
Simple as that.
I usually run pt-slave-delay in the background screen session so I can monitor the progress. Very simple and elegant.
Now there is no reason for you not to delay. Also pt-slave delay works fine with most of MySQL distributions and MariaDB.