Setting up delayed replication
Sometimes, you need a delayed slave for disaster recovery purpose. Suppose a disastrous statement (such as a DROP DATABASE command) was executed on the master. You have to use point-in-time recovery from backups to restore the database. It will lead to a huge downtime depending on the size of the database. To avoid that situation, you can use a delayed slave, which will be always delayed from the master by a configured amount of time. If a disaster occurs and that statement is not applied by the delayed slave, you can stop the slave and start until the disastrous statement, so that the disastrous statement won't be executed. Then promote it to master.
The procedure is exactly the same as setting up normal replication, except that you specify MASTER_DELAY in the CHANGE MASTER TO command.
How is the delay measured?
In versions earlier than MySQL 8.0, the delay is measured based on the Seconds_Behind_Master value. In MySQL 8.0, it is measured based on original_commit_timestamp...
