MySQL slave delay how to

The concept of delayed replicated slave is nothing new - Oracle (TM) for example had it for years with the Data Guard. So what is it in the delaying the replicated database slave, how to use it and how to implement it for MySQL - will discuss those topics in the posting.

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 = N;

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.