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

標籤：

最近在做MySQL主從資料庫同步測試，發現了一些問題，其中主從同步延遲問題是其中之一，下面內容是從網上找到的一些講解，記錄下來以便自己學習；

MySQL的主從同步是一個很成熟的架構，優點為：①在從伺服器可以執行查詢工作(即我們常說的讀功能)，降低主伺服器壓力;②在從主伺服器進行備份，避免備份期間影響主伺服器服務;③當主伺服器出現問題時，可以切換到從伺服器。

MySQL主從同步故障－Slave_SQL_Running: No http://www.linuxidc.com/Linux/2014-02/96945.htm

MySQL主從同步搭建 http://www.linuxidc.com/Linux/2013-12/93934.htm

MySQL主從複製配置詳述 http://www.linuxidc.com/Linux/2014-02/97136.htm

MySQL Replication(主從伺服器)配置執行個體 http://www.linuxidc.com/Linux/2013-12/94485.htm

在Linux系統中做MySQL資料庫主從伺服器 http://www.linuxidc.com/Linux/2013-12/93986.htm

MySQL 安裝與主從配置 http://www.linuxidc.com/Linux/2013-12/93378.htm

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

1. 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線程卻不可以。

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

答：當主庫的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)為單位做多線程，不同的庫可以使用不同的複製線程。

sync_binlog=1

This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction

預設情況下，並不是每次寫入時都將binlog與硬碟同步。因此如果作業系統或機器(不僅僅是MySQL伺服器)崩潰，有可能binlog中最後的語句丟 失了。要想防止這種情況，你可以使用sync_binlog全域變數(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫入後與硬碟 同步。即使sync_binlog設定為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，MySQL伺服器 處理COMMIT語句，它將整個事務寫入binlog並將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB復原，但仍 然存在binlog中。可以用--innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要--innodb-safe-binlog；由於引入了XA事務支援，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的 binlog(sync_binlog =1)和(預設情況為真)InnoDB日誌與硬碟同步，該選項的效果是崩潰後重啟時，在滾回事務後，MySQL伺服器從binlog剪下復原的 InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切資料等，並使從伺服器保持與主伺服器保持同步(不接收 復原的語句)。

innodb_flush_log_at_trx_commit （這個很管用）

抱怨Innodb比MyISAM慢 100倍？那麼你大概是忘了調整這個值。預設值1的意思是每一次事務提交或事務外的指令都需要把日誌寫入（flush）硬碟，這是很費時的。特別是使用電 池供電緩衝（Battery backed up cache）時。設成2對於很多運用，特別是從MyISAM錶轉過來的是可以的，它的意思是不寫入硬碟而是寫入系統緩衝。日誌仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的資料。而值2隻會在整個作業系統 掛了時才可能丟資料。

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