MySQL 5.6 supports delayed replication such that a
slave server deliberately lags behind the master by at least a
specified amount of time. The default delay is 0 seconds. Use the
MASTER_DELAY
option for
CHANGE MASTER TO
to set the delay
to N
seconds:
CHANGE MASTER TO MASTER_DELAY = N;
An event received from the master is not executed until at least
N
seconds later than its execution on
the master. The exceptions are that there is no delay for format
description events or log file rotation events, which affect only
the internal state of the SQL thread.
Delayed replication can be used for several purposes:
To protect against user mistakes on the master. A DBA can roll back a delayed slave to the time just before the disaster.
To test how the system behaves when there is a lag. For example, in an application, a lag might be caused by a heavy load on the slave. However, it can be difficult to generate this load level. Delayed replication can simulate the lag without having to simulate the load. It can also be used to debug conditions related to a lagging slave.
To inspect what the database looked like long ago, without having to reload a backup. For example, if the delay is one week and the DBA needs to see what the database looked like before the last few days' worth of development, the delayed slave can be inspected.
START SLAVE
and
STOP SLAVE
take effect immediately
and ignore any delay. RESET SLAVE
resets the delay to 0.
SHOW SLAVE STATUS
has three fields
that provide information about the delay:
SQL_Delay
: A nonnegative integer indicating the number of seconds that the slave must lag the master.SQL_Remaining_Delay
: WhenSlave_SQL_Running_State
isWaiting until MASTER_DELAY seconds after master executed event
, this field contains an integer indicating the number of seconds left of the delay. At other times, this field isNULL
.Slave_SQL_Running_State
: A string indicating the state of the SQL thread (analogous toSlave_IO_State
). The value is identical to theState
value of the SQL thread as displayed bySHOW PROCESSLIST
.
When the slave SQL thread is waiting for the delay to elapse
before executing an event, SHOW
PROCESSLIST
displays its State
value
as Waiting until MASTER_DELAY seconds after master
executed event
.
The relay-log.info
file now contains the
delay value, so the file format has changed. See
Section 17.2.2.2, “Slave Status Logs”. In particular, the first line
of the file now indicates how many lines are in the file. If you
downgrade a slave server to a version older than MySQL 5.6, the
older server will not read the file correctly. To address this,
modify the file in a text editor to delete the initial line
containing the number of lines.