MySQL主從同步延遲原因及解決辦法

MySQL主從延遲原因以及解決方案：談到MySQL資料庫主從同步延遲原理，得從mysql的資料庫主從複制原理說起，mysql的主從複制都是單線程的操作(mysql5.6版本之前)，主庫對所有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線程卻不可以。

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

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

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

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

（2）另外就是使用比主庫更好的硬體裝置作為slave。

就是把，一台從伺服器當度作為備份使用，而不提供查詢，那邊他的負載下來了，執行relay log裡面的SQL效率自然就高了。

（3）增加從伺服器喽，這個目的還是分散讀的壓力，進而降低伺服器負載。

4.MySQL資料庫主從同步延遲産生的因素。

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

另外，再介紹2個可以減少延遲的參數 –slave-net-timeout=seconds 參數含義：當slave從主資料庫讀取log資料失敗後，等待多久重建立立連接配接并擷取資料 slave_net_timeout機關為秒 預設設定為 3600秒 slave_net_timeout 3600 –master-connect-retry=seconds 參數含義：當重建立立主從連接配接時，如果連接配接建立失敗，間隔多久後重試。 master-connect-retry機關為秒 預設設定為 60秒 通常配置以上2個參數可以減少網絡問題導緻的主從資料同步延遲。

---------------------------------------------------------------------自己總結———————————————————————

主從同步延遲的解決辦法：

(1)參數：我們知道因為主伺服器要負責更新操作， 他對安全性的要求比從伺服器高，所有有些設定可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設定，而slave則不需要這麼高的資料安全，完全可以講sync_binlog設定為0或者關閉binlog，innodb_flushlog， innodb_flush_log_at_trx_commit 也 可以設定為0來提高sql的執行效率 這個能很大程度上提高效率。

從庫優化Mysql參數。比如增大innodb_buffer_pool_size，讓更多操作在Mysql記憶體中完成，減少磁盤操作。

(2)版本：主從同步以後， 主伺服器會把更新語句寫入binlog,   從伺服器的IO 線程(這裡要注意， 5.6.3 之前的IO線程僅有一個，5.6.3之後的有多線程去讀了，速度自然也就加快了)回去讀取主伺服器的binlog 并且寫到從伺服器的Relay log 裡面，然後從伺服器的 的SQL thread 會一個一個執行 relay log 裡面的sql ， 進行資料恢複。

(2)硬體：使用比主庫更好的硬體裝置作為slave。

從庫使用高性能主機。包括cpu強悍、記憶體加大。避免使用虛拟雲主機，使用實體主機，這樣提升了i/o方面性。