redis主從模式，mysql 主從特性_mysql 5.6 新特性 主從推延

mysql 5.6 新特性 主從延遲

前面一篇文章《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;

歡迎大家閱讀《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.

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: When Slave_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 by SHOW PROCESSLIST

通過 show slave status 查看到三個方面的信息關于延遲

sql_delay :非負整數表明延遲與master

sql_remaining_delay: 當發生等待時，這個地方會有非負整數值。平時，這個地方是null。

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.

The relay-log.info file now contains the delay value, so the file format has changed. SeeSection?16.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.

當slave sql thread? 線程正在延遲等待時，show? processlist 顯示的它的狀態值是等待。the relay-log.info 文件也包含延遲值，因此這個文件格式已經改變。具體看官方文檔。

第一次翻譯，有些可能不太通順，歡迎指正探討。

原創文章，轉載請注明： 轉載自搞代碼

微信 賞一包辣條吧~

支付寶 賞一聽可樂吧~