mysql 5.6 新特性 主从推延
日期：2014-05-16 浏览次数：20966 次
前面一篇文章《mysql replication 主从间如何延迟 --- 某大型互联网面试题》已经写到如何延迟主从配置，在5.6里已经实现了这个功能，那么我们看一下官方文档介绍吧 ：
http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html
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 theMASTER_DELAY option forCHANGE
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.
mysql 5.6提供了slave服务器指定落后于master服务器一段时间这样的功能.默认没有延迟,通过指定master_delay=n选项来设置延迟n秒：
change master to master_delay=n;
从master接受的event事件等到过了n秒后才在slave上面执行。不会产生异常，仅仅影响sql线程的内部状态。
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.
延迟复制用于下面的一些场景：
1 保护master上的用户错误。dba可以通过延迟回滚到灾难发生前。
2 当有延迟的时候，测试系统活动状况。如以下例子：在slave上面因为一个大量加载可能导致延迟，那么我们就可以人为模拟这个延迟，虽然大量加载不一定导致延迟。
3 检查数据库之前的一些状况。
START SLAVE andSTOP
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_StateisWaiting 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 theStatevalue of the SQL thread as displayed bySHOW PROCESSLIST
- 通过 show slave status 查看到三个方面的信息关于延迟
- sql_delay :非负整数表明延迟与master
- sql_remaining_delay:
当发生等待时，这个地方会有非负整数值。平时，这个地方是null。 - slave_sql_running_state:字符串指定sql线程状态（类似于 slave_io_s
