mysql 5.6 新特性 主从延迟
前面一篇文章《mysql replication 主从间如何延迟 --- 某大型互联网面试题》已经写到如何延迟主从配置，在5.6里已经实现了这个功能，那么我们看一下官方文档介绍吧 ：
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
CHANGE MASTER TO MASTER_DELAY =
欢迎大家阅读《mysql 5.6 新特性 主从推延》,跪求各位点评，by 搞代码
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.
change master to master_delay=n;
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.
Waiting 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 is
Slave_SQL_Running_State: A string indicating the state of the SQL thread (analogous to
Slave_IO_State). The value is identical to the
Statevalue of the SQL thread as displayed by
- 通过 show slave status 查看到三个方面的信息关于延迟
- sql_delay :非负整数表明延迟与master
- slave_sql_running_state:字符串指定sql线程状态（类似于 slave_io_state）。这个状态与通过show 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.
relay-log.info file now contains the delay value, so the file format has changed. SeeSection 126.96.36.199, “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.
当slave sql thread 线程正在延迟等待时，show processlist 显示的它的状态值是等待。the relay-log.info 文件也包含延迟值，因此这个文件格式已经改变。具体看官方文档。