MySQL中關於主從資料庫同步延遲的問題解決

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

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

相信大家對於這些好處已經非常了解了，在專案的部署中也採用這種方案。但是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中關於主從資料庫同步延遲的問題解決的詳細內容。更多資訊請關注PHP中文網其他相關文章！