MySQL 中Redo與Binlog順序一致性問題 【轉】

標籤：增加   pap   else   www.   box   復原事務   測試   服務   分組   

首先，我們知道在MySQL中，二進位日誌是server層的，主要用來做主從複製和即時間點復原時使用的。而交易記錄（redo log）是InnoDB儲存引擎層的，用來保證事務安全的。現在我們來討論一下MySQL主從複製過程中的一些細節問題，有關於主從複製可以看具體的章節。

在瞭解了以上基礎的內容後，我們可以帶著以下的幾個問題去學習複製到底是怎樣工作的。

• 為什麼MySQL有binlog，還有redo log？
• 事務是如何提交的？事務提交先寫binlog還是redo log？如何保證這兩部分的日誌做到順序一致性？
• 為了保障主從複製安全，故障恢複是如何做的？
• 為什麼需要保證二進位日誌的寫入順序和InnoDB層事務提交順序一致性呢？

為什麼MySQL有binlog，還有redo log?

這個是因為MySQL體繫結構的原因，MySQL是多儲存引擎的，不管使用那種儲存引擎，都會有binlog，而不一定有redo log，簡單的說，binlog是MySQL Server層的，redo log是InnoDB層的。

事務是如何提交的？事務提交先寫binlog還是redo log？如何保證這兩部分的日誌做到順序一致性？

MySQL Binary log在MySQL 5.1版本後推出主要用於主備複製的搭建，我們回顧下MySQL在開啟/關閉Binary Log功能時是如何工作的。

MySQL沒有開啟Binary log的情況下？

首先看一下什麼是CrashSafe？CrashSafe指MySQL伺服器宕機重啟後，能夠保證：

– 所有已經提交的事務的資料仍然存在。

– 所有沒有提交的事務的資料自動復原。

Innodb通過Redo Log和Undo Log可以保證以上兩點。為了保證嚴格的CrashSafe，必須要在每個事務提交的時候，將Redo Log寫入硬體儲存。這樣做會犧牲一些效能，但是可靠性最好。為了平衡兩者，InnoDB提供了一個innodb_flush_log_at_trx_commit系統變數，使用者可以根據應用的需求自行調整。

innodb_flush_log_at_trx_commit = 0|1|2

0 – 每N秒將Redo Log Buffer的記錄寫入Redo Log檔案，並且將檔案刷入硬體儲存1次。N由innodb_flush_log_at_timeout控制。

1 – 每個事務提交時，將記錄從Redo Log Buffer寫入Redo Log檔案，並且將檔案刷入硬體儲存。

2 – 每個事務提交時，僅將記錄從Redo Log Buffer寫入Redo Log檔案。Redo Log何時刷入硬體儲存由作業系統和innodb_flush_log_at_timeout決定。這個選項可以保證在MySQL宕機，而作業系統正常工作時，資料的完整性。

通過redo日誌將所有已經在儲存引擎內部提交的事務應用redo log恢複，所有已經prepare但是沒有commit的transactions將會應用undo log做rollback。然後用戶端串連時就能看到已經提交的資料存在資料庫內，未提交被復原地資料需要重新執行。

MySQL開啟Binary log的情況下？

MySQL為了保證master和slave的資料一致性，就必須保證binlog和InnoDB redo日誌的一致性（因為備庫通過二進位日誌重放主庫提交的事務，而主庫binlog寫入在commit之前，如果寫完binlog主庫crash，再次啟動時會復原事務。但此時從庫已經執行，則會造成主備資料不一致）。所以在開啟Binlog後，如何保證binlog和InnoDB redo日誌的一致性呢？為此，MySQL引入二階段提交（two phase commit or 2pc），MySQL內部會自動將普通事務當做一個XA事務（內部分布式事物）來處理：

– 自動為每個事務分配一個唯一的ID（XID）。

– COMMIT會被自動的分成Prepare和Commit兩個階段。

– Binlog會被當做事務協調者(Transaction Coordinator)，Binlog Event會被當做協調者日誌。

想瞭解2PC，可以參考文檔：https://en.wikipedia.org/wiki/Two-phase_commit_protocol

Binlog在2PC中充當了事務的協調者（Transaction Coordinator）。由Binlog來通知InnoDB引擎來執行prepare，commit或者rollback的步驟。事務提交的整個過程如下：

以上的圖片中可以看到，事務的提交主要分為兩個主要步驟：

1. 準備階段（Storage Engine（InnoDB） Transaction Prepare Phase）

此時SQL已經成功執行，並產生xid資訊及redo和undo的記憶體日誌。然後調用prepare方法完成第一階段，papare方法實際上什麼也沒做，將事務狀態設為TRX_PREPARED，並將redo log刷磁碟。

2. 提交階段(Storage Engine（InnoDB）Commit Phase)

2.1 記錄協調者日誌，即Binlog日誌。

如果事務涉及的所有儲存引擎的prepare都執行成功，則調用TC_LOG_BINLOG::log_xid方法將SQL語句寫到binlog（write()將binary log記憶體日誌資料寫入檔案系統快取，fsync()將binary log檔案系統快取日誌資料永久寫入磁碟）。此時，事務已經鐵定要提交了。否則，調用ha_rollback_trans方法復原事務，而SQL語句實際上也不會寫到binlog。

2.2 告訴引擎做commit。

最後，調用引擎的commit完成事務的提交。會清除undo資訊，刷redo日誌，將事務設為TRX_NOT_STARTED狀態。

PS：記錄Binlog是在InnoDB引擎Prepare（即Redo Log寫入磁碟）之後，這點至關重要。

由上面的二階段提交流程可以看出，一旦步驟2中的操作完成，就確保了事務的提交，即使在執行步驟3時資料庫發送了宕機。此外需要注意的是，每個步驟都需要進行一次fsync操作才能保證上下兩層資料的一致性。步驟2的fsync參數由sync_binlog=1控制，步驟3的fsync由參數innodb_flush_log_at_trx_commit=1控制，俗稱“雙1”，是保證CrashSafe的根本。

參數說明如下：

innodb_flush_log_at_trx_commit（redo）

• 0： log buffer每秒一次地寫入log file中，且進行flush操作。InnoDB日誌重新整理頻率由控制 innodb_flush_log_at_timeout，它允許你將日誌重新整理頻率設定為N秒（其中N是1 … 2700，預設值為1）。
• 1：每次事務提交時都會把log buffer的資料寫入log file，並進行flush操作。
• 2：每次事務提交時MySQL都會把log buffer的資料寫入log file，不進行flush操作。

sync_binlog （binlog）

• 0：重新整理binlog_cache中的資訊到磁碟由os決定。
• N：每N次事務提交重新整理binlog_cache中的資訊到磁碟。

事務的兩階段交易認可協議保證了無論在任何情況下，事務要麼同時存在於儲存引擎和binlog中，要麼兩個裡面都不存在，這就保證了主庫與從庫之間資料的一致性。如果資料庫系統發生崩潰，當資料庫系統重新啟動時會進行崩潰恢複操作，儲存引擎中處於prepare狀態的事務會去查詢該事務是否也同時存在於binlog中，如果存在就在儲存引擎內部提交該事務（因為此時從庫可能已經擷取了對應的binlog內容），如果binlog中沒有該事務，就復原該事務。例如：當崩潰發生在第一步和第二步之間時，明顯處於prepare狀態的事務還沒來得及寫入到binlog中，所以該事務會在儲存引擎內部進行復原，這樣該事務在儲存引擎和binlog中都不會存在；當崩潰發生在第二步和第三步之間時，處於prepare狀態的事務存在於binlog中，那麼該事務會在儲存引擎內部進行提交，這樣該事務就同時存在於儲存引擎和binlog中。

為了保證資料的安全性，以上列出的3個步驟都需要調用fsync將資料持久化到磁碟。由於在引擎內部prepare好的事務可以通過binlog恢複，所以通常情況下第三個fsync是可以省略的。

另外，MySQL內部兩階段交易認可需要開啟innodb_support_xa=true，預設開啟。這個參數就是支援分散式交易兩段式事務提交。redo和binlog資料一致性就是靠這個兩段式提交來完成的，如果關閉會造成交易資料的丟失。

為了保障主從複製安全，故障恢複是如何做的？

開啟Binary log的MySQL在crash recovery時：MySQL在prepare階段會產生xid，然後會在commit階段寫入到binlog中。在進行恢複時事務要提交還是復原，是由Binlog來決定的。

– 事務的Xid_log_event存在，就要提交。

– 事務的Xid_log_event不存在，就要復原。

恢複的過程非常簡單：

– 從Binlog中讀出所有的Xid_log_event

– 告訴InnoDB提交這些XID的事務

– InnoDB復原其它的事務

總結一下，基本頂多會出現下面是幾種情況：

• 當事務在prepare階段crash，資料庫recovery的時候該事務未寫入Binary log並且儲存引擎未提交，將該事務rollback。
• 當事務在binlog階段crash，此時日誌還沒有成功寫入到磁碟中，啟動時會rollback此事務。
• 當事務在binlog日誌已經fsync()到磁碟後crash，但是InnoDB沒有來得及commit，此時MySQL資料庫recovery的時候將會讀出二進位日誌的Xid_log_event，然後告訴InnoDB提交這些XID的事務，InnoDB提交完這些事務後會復原其它的事務，使儲存引擎和二進位日誌始終保持一致。

總結起來說就是如果一個事務在prepare階段中落盤成功，並在MySQL Server層中的binlog也寫入成功，那這個事務必定commit成功。

為什麼需要保證二進位日誌的寫入順序和InnoDB層事務提交順序一致性呢？

上面提到單個事務的二階段提交過程，能夠保證儲存引擎和binary log日誌保持一致，但是在並發的情況下怎麼保證InnoDB層交易記錄和MySQL資料庫二進位日誌的提交的順序一致？當多個事務並發提交的情況，如果Binary Log和儲存引擎順序不一致會造成什麼影響？

這是因為備份及恢複需要，例如通過xtrabackup或ibbackup這種物理備份工具進行備份時，並使用備份來建立複製，如：

如，事務按照T1、T2、T3順序開始執行，將二進位日誌（按照T1、T2、T3順序）寫入記錄檔系統緩衝，調用fsync()進行一次group commit將記錄檔永久寫入磁碟，但是儲存引擎提交的順序為T2、T3、T1。當T2、T3提交事務之後，若通過線上物理備份進行資料庫恢複來建立複製時，因為在InnoDB儲存引擎層會檢測事務T3在上下兩層都完成了事務提交，不需要在進行恢複了，此時主備資料不一致（搭建Slave時，change master to的日誌位移量記錄T3在事務位置之後）。

為瞭解決以上問題，在早期的MySQL 5.6版本之前，通過prepare_commit_mutex鎖以串列的方式來保證MySQL資料庫上層二進位日誌和Innodb儲存引擎層的事務提交順序一致，然後會導致組提交（group commit）特性無法生效。為了滿足資料的持久化需求，一個完整事務的提交最多會導致3次fsync操作。為了提高MySQL在開啟binlog的情況下單位時間內的事務提交數，就必須減少每個事務提交過程中導致的fsync的調用次數。所以，MySQL從5.6版本開始加入了binlog group commit技術（MariaDB 5.3版本開始引入）。

MySQL資料庫內部在prepare redo階段擷取prepare_commit_mutex鎖，一次只能有一個事務可擷取該mutex。通過這個臭名昭著prepare_commit_mutex鎖，將redo log和binlog刷盤序列化，序列化的目的也僅僅是為了保證redo log和Binlog一致，繼而無法實現group commit，犧牲了效能。整個過程如：

可以看出在prepare_commit_mutex，只有當上一個事務commit後釋放鎖，下一個事務才可以進行prepare操作，並且在每個事務過程中Binary log沒有fsync()的調用。由於記憶體資料寫入磁碟的開銷很大，如果頻繁fsync()把日誌資料永久寫入磁碟資料庫的效能將會急劇下降。此時MySQL資料庫提供sync_binlog參數來設定多少個binlog日誌產生的時候調用一次fsync()把二進位日誌刷入磁碟來提高整體效能。

所示MySQL開啟Binary log時使用prepare_commit_mutex和sync_log保證二進位日誌和儲存引擎順序保持一致，prepare_commit_mutex的鎖機製造成高並發提交事務的時候效能非常差而且二進位日誌也無法group commit。

這個問題早在2010年的MySQL資料庫大會中提出，Facebook MySQL技術組，Percona公司都提出過解決方案，最後由MariaDB資料庫的開發人員Kristian Nielsen完成了最終的”完美”解決方案。在這種情況下，不但MySQL資料庫上層二進位日誌寫入是group commit的，InnoDB儲存引擎層也是group commit的。此外還移除了原先的鎖prepare_commit_mutex，從而大大提高了資料庫的整體性。MySQL 5.6採用了類似的實現方式，並將其稱為BLGC（Binary Log Group Commit），並把事務提交過程分成三個階段，Flush stage、Sync stage、Commit stage。

 

BLGC（Binary Log Group Commit）組提交後如何保證事務記錄到binlog的順序和 事務提交到儲存引擎的一致性呢？ 

MySQL 5.6 BLGC技術出現後，在這種情況下，不但MySQL資料庫上層二進位日誌寫入是group commit的，InnoDB儲存引擎層也是group commit的。此外還移除了原先的鎖prepare_commit_mutex，從而大大提高了資料庫的整體性。其事務的提交（commit）過程分成三個階段，Flush stage、Sync stage、Commit stage。如：

Binlog組提交的基本思想是，引入隊列機制保證Innodb commit順序與binlog落盤順序一致，並將事務分組，組內的binlog刷盤動作交給一個事務進行，實現組提交目的。在MySQL資料庫上層進行提交時首先按順序將其放入一個隊列中，隊列中的第一個事務稱為leader，其他事務稱為follow，leader控制著follow的行為。

從可以看出，每個階段都有一個隊列，每個隊列有一個mutex保護，約定進入隊列第一個線程為leader，其他線程為follower，所有事情交由leader去做，leader做完所有動作後，通知follower刷盤結束。BLGC就是將事務提交分為了3個階段，FLUSH階段，SYNC階段和COMMIT階段。

• Flush Stage

將每個事務的二進位日誌寫入記憶體中。

1) 持有Lock_log mutex [leader持有，follower等待]。

2) 擷取隊列中的一組binlog(隊列中的所有事務)。

3) 將binlog buffer到I/O cache。

4) 通知dump線程dump binlog。

• Sync Stage

將記憶體中的二進位日誌重新整理到磁碟，若隊列中有多個事務，那麼僅一次fsync操作就完成了二進位日誌的寫入，這就是BLGC。

1) 釋放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]。

2) 將一組binlog 落盤(sync動作，最耗時，假設sync_binlog為1)。

• Commit Stage

leader根據順序調用儲存引擎層事務的提交，Innodb本身就支援group commit，因此修複了原先由於鎖prepare_commit_mutex導致group commit失效的問題。

1) 釋放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]。

2) 遍曆隊列中的事務，逐一進行innodb commit。

3) 釋放Lock_commit mutex。

4) 喚醒隊列中等待的線程。

說明：由於有多個隊列，每個隊列各自有mutex保護，隊列之間是順序的，約定進入隊列的一個線程為leader，因此FLUSH階段的leader可能是SYNC階段的follower，但是follower永遠是follower。

當有一組事務在進行commit階段時，其他新事物可以進行Flush階段，從而使group commit不斷生效。當然group commit的效果由隊列中事務的數量決定，若每次隊列中僅有一個事務，那麼可能效果和之前差不多，甚至會更差。但當提交的事務越多時，group commit的效果越明顯，資料庫效能的提升也就越大。

MySQL提供了一個參數binlog_max_flush_queue_time（MySQL 5.7.9版本失效），預設值為0，用來控制MySQL 5.6新增的BLGC（binary log group commit），就是二進位日誌組提交中Flush階段中等待的時間，即使之前的一組事務完成提交，當前一組的事務也不馬上進入Sync階段，而是至少需要等待一段時間，這樣做的好處是group commit的事務數量更多，然而這也可能會導致事務的回應時間變慢。該參數預設為0表示不等待，且推薦設定依然為0。除非使用者的MySQL資料庫系統中有大量的串連（如100個串連），並且不斷地在進行事務的寫入或更新操作。

MySQL 5.7 Parallel replication實現主備多線程複製基於主庫BLGC（Binary Log Group Commit）機制，並在Binary log日誌中標識同一組事務的last_commited=N和該組事務內所有的事務提交順序。為了增加一組事務內的事務數量提高備庫組提交時的並發量引入了binlog_group_commit_sync_delay=N和binlog_group_commit_sync_no_delay_count=N

註：binlog_max_flush_queue_time在MySQL的5.7.9及之後版本不再生效）參數，MySQL等待binlog_group_commit_sync_delay毫秒直到達到binlog_group_commit_sync_no_delay_count事務個數時，將進行一次組提交。

下面是提供測試組提交的一張圖，可以看到組提交的TPS高不少。

 

 

MySQL 中Redo與Binlog順序一致性問題 【轉】