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

Mysql主從基本原理，主要形式以及主從同步延遲原理(讀寫分離)導致主庫從庫資料不一致問題的及解決方案

 

一、主從資料庫的差異

從資料庫(Slave)是主資料庫的備份，當主資料庫(Master)變更時從資料庫要更新，這些資料庫軟體可以設計更新周期。這是提高資訊安全的手段。主從資料庫伺服器不在一個地理​​位置上，當發生意外時資料庫可以保存。

(1) 主從分工

其中Master負責寫入操作的負載，也就是說一切寫的操作都在Master上進行，而讀的操作則分攤到Slave上進行。這樣一來的可以大大提高讀取的效率。在一般的網路應用中，經過一些數據調查得出結論，讀/寫的比例大概在10：1左右，也就是說大量的數據操作是集中在讀的操作，這也就是為什麼我們會有多個Slave的原因。但是為什麼要分離讀寫呢？熟悉DB的研發人員都知道，寫入作業牽涉到鎖的問題，不管是行鎖還是表鎖或區塊鎖，都是比較降低系統執行效率的事情。我們這樣的分離是把寫入操作集中在一個節點上，而讀取操作其其他的N個節點上進行，從另一個方面有效的提高了讀取的效率，保證了系統的高可用性。

(2) 基本流程
1)、Mysql的主從同步就是當master（主函式庫）發生資料變化的時候，會即時同步到slave（從函式庫） 。
2)、主從複製可以水平擴展資料庫的負載能力，容錯，高可用，資料備份。

3)、不管是delete、update、insert，或是建立函數、儲存過程，都是在master上，當master有操作的時候，slave會快速的接受到這些操作，從而做同步。

(3) 用途與條件
1)、mysql主從複製用途
  ●即時災備，用於故障切換
●讀寫分離，提供查詢服務
  ●備份，避免影響業務
2)、主從部署必要條件：
  ●主庫開啟binlog日誌（設定log-bin參數）
  ●主從server-id不同
  ●從庫伺服器能連通主庫

 

二、主從同步的粒度、原理和形式：

(1)、 三種主要實作粒度
詳細的主從同步主要有三種形式：statement、row、mixed
　1)、statement: 會將對資料庫操作的sql語句寫道binlog中
　2)、row: 會將每一個資料的變更寫道binlog中。
   3)、mixed: statement與row的混合。 Mysql決定何時寫statement格式的binlog, 何時寫row格式的binlog。

(2)、主要的實作原理、具體操作、示意圖

1)、在master機器上的操作：
當master上的資料變更時，該事件變更會依照順序寫入bin-log。當slave連結到master的時候，master機器會為slave開啟binlog dump執行緒。當master的binlog改變的時候，bin-log dump執行緒會通知slave，並將對應的binlog內容傳送給slave。
2)、在slave機器上操作：

　  當主從同步開啟的時候，slave上會建立兩個執行緒：I\O執行緒。該執行緒連接到master機器，master機器上的binlog dump 執行緒會將binlog的內容傳送給該I\O執行緒。此I/O線程接收到binlog內容後，再將內容寫入到本地的relay log；sql線程。該線程讀取到I/O線程寫入的ralay log。並且根據relay log。並且根據relay log 的內容對slave資料庫做對應的操作。

3)、MySQL主從複製原理圖如下:

 

從函式庫產生兩個線程，一個I/O線程，一個SQL線程；
i/o線程去請求主庫的binlog，並將得到的binlog日誌寫到relay log（中繼日誌） 文件中；
主庫會產生一個log dump 線程，用來給從函式庫i/o線程傳binlog；
SQL 線程，會讀取relay log檔案中的日誌，並解析成具體操作，來實現主從的操作一致，而最終資料一致；

(2)、主從形式

mysql主從複製靈活
  ● 一主一從
  ● 主複製
  ● 一主多從---擴展系統讀取的效能，因為讀取是從庫讀取的；
  ● 多主一從---5.7開始支援

  ● 聯級複製---

## 

三、主從同步的延遲等問題、原因及解決方案：

(1)、mysql資料庫從函式庫同步的延遲問題

　 1）相關參數：

先在伺服器上執行show slave satus;可以看到很多同步的參數：　

Master_Log_File：                      SLAVE中的I/O執行緒目前正在讀取的主伺服器二進位日誌檔案的名稱
Read_Master_Log_Pos: 10    
Relay_Log_File：                        SQL執行緒目前讀取且執行中的中繼日誌檔案的名稱
Relay_Log_Pos:      ##Relay_Master_Log_File：      由SQL線程執行的包含大多數近期事件的主伺服器二進位日誌檔案的名稱
Slave_IO_Running：                 I/O執行緒是否啟動並成功地連接到主伺服器上
Slave_SQL_Running：  從屬伺服器SQL執行緒和從屬伺服器I/O執行緒之間的時間差距，單位以秒計。
從函式庫同步延遲狀況出現的● show slave status顯示參數Seconds_Behind_Master不為0，這個數值可能會很大
● show slave status顯示參數Relay_Master_Log_File和Master_Log_File顯示bin-log的編號相差很大，說明bin-log從庫上沒有及時同步，所以近期執行的bin-log和目前IO線程所讀的bin-log相差很大
● mysql的從庫資料目錄下存在大量mysql-relay-log日誌，該日誌同步完成之後就會被系統自動刪除，存在大量日誌，說明主從同步延遲很厲害

(2)、MySql資料庫從庫同步的延遲問題

 

1)、MySQL資料庫主從同步延遲原理mysql主從同步原理：主庫針對寫入操作，順序寫binlog，從庫單線程去主庫順序讀取」寫操作的binlog”，從庫取到binlog在本地原樣執行（隨機寫），來確保主從資料邏輯上一致。 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語句產生了鎖等待。首要原因：資料庫在業務上讀寫壓力太大，CPU運算負荷大，網路卡負荷大，硬碟隨機IO太高次要原因：讀寫binlog帶來的效能影響，網路傳輸延遲。

 

(3)、MySql資料庫從庫同步的延遲解決方案

1)、架構面

1.業務的持久化層的實作採用分庫架構，mysql服務可平行擴展，分散壓力。

2.單一庫讀寫分離，一主多從，主寫從讀，分散壓力。這樣從庫壓力比主庫高，保護主庫。

3.服務的基礎架構在業務和mysql之間加入memcache或redis的cache層。降低mysql的讀壓力。

4.不同業務的mysql物理上放在不同機器，分散壓力。

5.使用比主庫更好的硬體設備作為slave總結，mysql壓力小，延遲自然會變小。

2)、硬體方面

1.採用好伺服器，例如4u比2u效能明顯好，2u比1u效能明顯好。

2.儲存用ssd或是盤陣或san，提升隨機寫的效能。

3.主從間保證處在同一個交換器下面，並且是萬兆環境。

總結，硬體強勁，延遲自然會變小。一句話，縮小延遲的解決方案就是花錢和花時間。

3)、mysql主從同步加速

1、sync_binlog在slave端設定為0

2、–logs-slave-updates 從伺服器從主伺服器接收的更新不記入它的二進位日誌。

3、直接停用slave端的binlog

4、slave端，如果使用的儲存引擎是innodb，innodb_flush_log_at_trx_commit =2

4)、從檔案系統本身屬性角度優化 

master端修改linux、Unix檔案系統中檔案的etime屬性， 由於每當讀取檔案時OS都會將讀取作業發生的時間回寫到磁碟上，對於讀取操作頻繁的資料庫檔案來說這是沒必要的，只會增加磁碟系統的負擔影響I/O效能。可以透過設定檔案系統的mount屬性，組織作業系統寫atime訊息，在linux上的操作為：開啟/etc/fstab，加上noatime參數/dev/sdb1 /data reiserfs noatime 1 2然後重新mount檔案系統#mount -oremount /data

5)、同步參數調整主庫是寫，對資料安全性較高，例如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設定是需要的而slave則不需要這麼高的資料安全，完全可以講sync_binlog設定為0或關閉binlog，innodb_flushlog也可以設定為0來提高sql的執行效率

1、sync_binlog=1 oMySQL提供一個sync_binlog參數來控制資料庫的binlog刷到磁碟上去。默認，sync_binlog=0，表示MySQL不控制binlog的刷新，由檔案系統自己控制它的快取的刷新。這時候的性能是最好的，但是風險也是最大的。一旦系統Crash，在binlog_cache中的所有binlog資訊都會被遺失。

如果sync_binlog>0，表示每sync_binlog次交易提交，MySQL呼叫檔案系統的刷新作業將會快取刷下去。最安全的就是sync_binlog=1了，表示每次交易提交，MySQL都會把binlog刷下去，是最安全但是效能損耗最大的設定。這樣的話，在資料庫所在的主機作業系統損壞或突然掉電的情況下，系統才有可能遺失1個交易的資料。但是binlog雖然是順序IO，但是設定sync_binlog=1，多個事務同時提交，同樣很大的影響MySQL和IO效能。雖然可以透過group commit的補丁來緩解，但刷新的頻率過高對IO的影響也非常大。

對於高並發事務的系統來說，「sync_binlog」設定為0和設定為1的系統寫入效能差距可能高達5倍甚至更多。所以很多MySQL DBA設定的sync_binlog不是最安全的1，而是2或是0。這樣犧牲一定的一致性，可以獲得更高的同時和性能。預設情況下，並不是每次寫入時都會將binlog與硬碟同步。因此如果作業系統或機器(不只是MySQL伺服器)崩潰，有可能binlog中最後的語句遺失了。要防止這種情況，你可以使用sync_binlog全域變數(1是最安全的值，但也是最慢的)，讓binlog在每N次binlog寫入後與硬碟同步。即使sync_binlog設定為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。

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

3、ls(1) 指令可用於列出檔案的 atime、ctime 和 mtime。

atime 文件的access time 在讀取文件或執行文件時更改的ctime 文件的create time 在寫入文件，更改所有者，權限或鏈接設置時隨inode的內容更改而更改mtime 文件的modified time 在寫入檔案時隨檔案內容的變更而變更ls -lc filename 列出檔案的ctimels -lu filename 列出檔案的atimels -l filename 列出檔案的mtimestat filename 列出atime，mtime，ctimeatime不一定在存取文件之後被修改因為：使用ext3檔案系統的時候，如果在mount的時候使用了noatime參數那麼就不會更新atime資訊。這三個time stamp都放在inode 中.如果mtime,atime 修改,inode 就一定會改, 既然inode 改了,那ctime也就跟著改了.之所以在mount option 中使用noatime, 就是不想file system 做太多的修改, 而改善讀取效能

 
 

#(4)、MySql資料庫同步其他問題及解決方案

#1)、mysql主從複製存在的問題：  ● 主庫宕機後，資料可能遺失  ● 從函式庫只有一個sql Thread，主函式庫寫壓力大，複製很可能延遲2)、解決方法：  ● 半同步複製---解決資料遺失的問題  ● 平行複製----解決從庫複製延遲的問題

3)、半同步複製mysql semi-sync（半同步複製）半同步複製：  ● 5.5整合到mysql，以插件的形式存在，需要單獨安裝  ● 確保事務提交後binlog至少傳輸到一個從庫  ● 不保證從庫應用完這個事務的binlog  ● 性能有一定的降低，響應時間會更長  ● 網路異常或從庫宕機，卡主主庫，直到超時或從庫恢復4) 、主從複製--非同步複製原理、半同步複製與平行複製原理比較

a、非同步複製原理：

b、半同步複製原理：

事務在主庫寫完binlog後需要從庫返回一個已接受，才放回給客戶端；5.5集成到mysql，以插件的形式存在，需要單獨安裝確保事務提交後binlog至少傳輸到一個從庫不保證從庫應用完成這個事務的binlog性能有一定的降低網路異常或從庫宕機，卡主庫，直到超時或從庫恢復

c、並行複製mysql並行複製  ● 社群版5.6新增  ● 並行是指從庫多執行緒apply binlog  ● 庫層級並行應用binlog，同一個庫資料變更或序列的(5.7版並行複製基於事務組)設定set global slave_parallel_workers=10;設定sql執行緒數為10

原理：從庫多執行緒apply binlog在社群5.6新增庫層級並行應用binlog，同一個函式庫資料變更還是串列的5.7版本並行複製基於事務群組

更多MySQL相關技術文章，請造訪MySQL教學欄位進行學習！

以上是MySQL主從同步延遲的原因及解決辦法的詳細內容。更多資訊請關注PHP中文網其他相關文章！