MySQL主從資料庫同步延遲問題解決

相信大家對于這些好處已經非常了解了，在項目的部署中也采用這種方案。但是mysql的主從同步一直有從庫延遲的問題，那麼為什麼會有這種問題。這種問題如何解決呢？

2. mysql資料庫主從同步延遲是怎麼産生的。

3. mysql資料庫主從同步延遲解決方案。

1. mysql資料庫主從同步延遲原理。

答：談到mysql資料庫主從同步延遲原理，得從mysql的資料庫主從複制原理說起，mysql的主從複制都是單線程的操作，主庫對所有ddl和 dml産生binlog，binlog是順序寫，是以效率很高，slave的slave_io_running線程到主庫取日志，效率很比較高，下一步， 問題來了，slave的slave_sql_running線程将主庫的ddl和dml操作在slave實施。dml和ddl的io操作是随即的，不是順 序的，成本高很多，還可能可slave上的其他查詢産生lock争用，由于slave_sql_running也是單線程的，是以一個ddl卡主了，需要 執行10分鐘，那麼所有之後的ddl會等待這個ddl執行完才會繼續執行，這就導緻了延時。有朋友會問：“主庫上那個相同的ddl也需要執行10分，為什 麼slave會延時？”，答案是master可以并發，slave_sql_running線程卻不可以。

答：當主庫的tps并發較高時，産生的ddl數量超過slave一個sql線程所能承受的範圍，那麼延時就産生了，當然還有就是可能與slave的大型query語句産生了鎖等待。

3. mysql資料庫主從同步延遲解決方案

答：最簡單的減少slave同步延時的方案就是在架構上做優化，盡量讓主庫的ddl快速執行。還有就是主庫是寫，對資料安全性較高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設定，而slave則不需要這麼高的資料安全，完全可以講sync_binlog設定為0或者關閉binlog，innodb_flushlog也 可以設定為0來提高sql的執行效率。另外就是使用比主庫更好的硬體裝置作為slave。

mysql-5.6.3已經支援了多線程的主從複制。原理和丁奇的類似，丁奇的是以表做多線程，oracle使用的是以資料庫(schema)為機關做多線程，不同的庫可以使用不同的複制線程。

基于區域網路的master/slave機制在通常情況下已經可以滿足'實時'備份的要求了。如果延遲比較大，就先确認以下幾個因素： 

1. 網絡延遲

2. master負載

3. slave負載

一般的做法是，使用多台slave來分攤讀請求，再從這些slave中取一台專用的伺服器，隻作為備份用，不進行其他任何操作，就能相對最大限度地達到'實時'的要求了

slave_net_timeout機關為秒 預設設定為 3600秒

參數含義：當slave從主資料庫讀取log資料失敗後，等待多久重建立立連接配接并擷取資料

master-connect-retry機關為秒 預設設定為 60秒

參數含義：當重建立立主從連接配接時，如果連接配接建立失敗，間隔多久後重試。

通常配置以上2個參數可以減少網絡問題導緻的主從資料同步延遲

判斷主從延時，通常有兩個方法：

1. seconds_behind_master  vs  2. mk-heartbeat，下面具體說下兩者在實作功能的差别。

可以通過監控show slave status\g指令輸出的seconds_behind_master參數的值來判斷，是否有發生主從延時。

其值有這麼幾種：

null - 表示io_thread或是sql_thread有任何一個發生故障，也就是該線程的running狀态是no,而非yes.

0 - 該值為零，是我們極為渴望看到的情況，表示主從複制良好，可以認為lag不存在。

正值 - 表示主從已經出現延時，數字越大表示從庫落後主庫越多。

負值 - 幾乎很少見，隻是聽一些資深的dba說見過，其實，這是一個bug值，該參數是不支援負值的，也就是不應該出現。

seconds_behind_master是通過比較sql_thread執行的event的timestamp和io_thread複制好的 event的timestamp(簡寫為ts)進行比較，而得到的這麼一個內插補點。我們都知道的relay-log和主庫的bin-log裡面的内容完全一 樣，在記錄sql語句的同時會被記錄上當時的ts，是以比較參考的值來自于binlog，其實主從沒有必要與ntp進行同步，也就是說無需保證主從時鐘的 一緻。你也會發現，其實比較真正是發生在io_thread與sql_thread之間，而io_thread才真正與主庫有關聯，于是，問題就出來了， 當主庫i/o負載很大或是網絡阻塞，io_thread不能及時複制binlog（沒有中斷，也在複制），而sql_thread一直都能跟上 io_thread的腳本，這時seconds_behind_master的值是0，也就是我們認為的無延時，但是，實際上不是，你懂得。這也就是為什 麼大家要批判用這個參數來監控資料庫是否發生延時不準的原因，但是這個值并不是總是不準，如果當io_thread與master網絡很好的情況下，那麼 該值也是很有價值的。（就好比：媽–兒子–媳婦的關系，媽與兒子親人，媳婦和兒子也親人，不見得媳婦與媽就很親。開個玩笑:-）之前，提到 seconds_behind_master這個參數會有負值出現，我們已經知道該值是io_thread的最近跟新的ts與sql_thread執行到 的ts內插補點，前者始終是大于後者的，唯一的肯能就是某個event的ts發生了錯誤，比之前的小了，那麼當這種情況發生時，負值出現就成為可能。

方法2. mk-heartbeat，maatkit萬能工具包中的一個工具，被認為可以準确判斷複制延時的方法。

mk-heartbeat的實作也是借助timestmp的比較實作的，它首先需要保證主從伺服器必須要保持一緻，通過與相同的一個ntp server同步時鐘。它需要在主庫上建立一個heartbeat的表，裡面至少有id與ts兩個字段，id為server_id，ts就是目前的時間戳 now()，該結構也會被複制到從庫上，表建好以後，會在主庫上以背景程序的模式去執行一行更新操作的指令，定期去向表中的插入資料，這個周期預設為1 秒，同時從庫也會在背景執行一個監控指令，與主庫保持一緻的周期去比較，複制過來記錄的ts值與主庫上的同一條ts值，內插補點為0表示無延時，內插補點越大表示 延時的秒數越多。我們都知道複制是異步的ts不肯完全一緻，是以該工具允許半秒的差距，在這之内的差異都可忽略認為無延時。這個工具就是通過實打實的複 制，巧妙的借用timestamp來檢查延時，贊一個！