Mysql的binlog原理

什麼是二進位制日誌(binlog)

binlog是記錄所有資料庫表結構變更（例如CREATE、ALTER TABLE…）以及表資料修改（INSERT、UPDATE、DELETE…）的二進位制日誌。

binlog不會記錄SELECT和SHOW這類操作，因為這類操作對資料本身並沒有修改，但你可以通過查詢通用日誌來檢視MySQL執行過的所有語句。

二進位制日誌包括兩類檔案：二進位制日誌索引檔案（檔名字尾為.index）用於記錄所有的二進位制檔案，二進位制日誌檔案（檔名字尾為.00000*）記錄資料庫所有的DDL和DML(除了資料查詢語句)語句事件。

 

事務日誌及與二進位制日誌的區別

Mysql預設情況下會有兩個檔案：ib_logfile0和ib_logfile1，這兩個檔案就是重做日誌檔案，或者事務日誌。

事務日誌的目的：萬一例項或者介質失敗，事務日誌檔案就能派上用場。

每個InnoDB儲存引擎至少有一個事務日誌檔案組，每個檔案組下至少有2個事務日誌檔案，如預設的ib_logfile0、ib_logfile1。InnoDB儲存引擎先寫事務日誌檔案1，當達到檔案的最後時，會切換至事務日誌檔案2，當事務日誌檔案2也被寫滿時，會再被切換到重事務日誌檔案1中

 

二進位制日誌處理事務和非事務性語句的區別

在事務性語句（update）執行過程中，伺服器將會進行額外的處理，在伺服器執行時多個事務是並行執行的，為了把他們的記錄在一起，需要引入事務日誌的概念。在事務完成被提交的時候一同重新整理到二進位制日誌。對於非事務性語句（insert，delete）的處理。遵循以下3條規則：

1）如果非事務性語句被標記為事務性，那麼將被寫入重做日誌。

2）如果沒有標記為事務性語句，而且重做日誌中沒有，那麼直接寫入二進位制日誌。

3）如果沒有標記為事務性的，但是重做日誌中有，那麼寫入重做日誌。

注意如果在一個事務中有非事務性語句，那麼將會利用規則2，優先將該影響非事務表語句直接寫入二進位制日誌。
 

 

XA的概念

XA(分散式事務)規範主要定義了(全域性)事務管理器(TM: Transaction Manager)和(區域性)資源管理器(RM: Resource Manager)之間的介面。XA為了實現分散式事務，將事務的提交分成了兩個階段：也就是2PC (tow phase commit)，XA協議就是通過將事務的提交分為兩個階段來實現分散式事務。

兩階段

1）prepare 階段

事務管理器向所有涉及到的資料庫伺服器發出prepare"準備提交"請求，資料庫收到請求後執行資料修改和日誌記錄等處理，處理完成後只是把事務的狀態改成"可以提交",然後把結果返回給事務管理器。即：為prepare階段，TM向RM發出prepare指令，RM進行操作，然後返回成功與否的資訊給TM。

2）commit 階段

事務管理器收到迴應後進入第二階段，如果在第一階段內有任何一個資料庫的操作發生了錯誤，或者事務管理器收不到某個資料庫的迴應，則認為事務失敗，回撤所有資料庫的事務。資料庫伺服器收不到第二階段的確認提交請求，也會把"可以提交"的事務回撤。如果第一階段中所有資料庫都提交成功，那麼事務管理器向資料庫伺服器發出"確認提交"請求，資料庫伺服器把事務的"可以提交"狀態改為"提交完成"狀態，然後返回應答。即：為事務提交或者回滾階段，如果TM收到所有RM的成功訊息，則TM向RM發出提交指令；不然則發出回滾指令。

• 外部與內部XA

MySQL中的XA實現分為：外部XA和內部XA。前者是指我們通常意義上的分散式事務實現；後者是指單臺MySQL伺服器中，Server層作為TM(事務協調者，通常由binlog模組擔當)，而伺服器中的多個資料庫例項作為RM，而進行的一種分散式事務，也就是MySQL跨庫事務；也就是一個事務涉及到同一個MySQL伺服器中的兩個innodb資料庫(目前似乎只有innodb支援XA)。內部XA也可以用來保證redo和binlog的一致性問題。

 

事務日誌與二進位制日誌的一致性問題

我們MySQL為了相容其它非事務引擎的複製，在server層面引入了 binlog, 它可以記錄所有引擎中的修改操作，因而可以對所有的引擎使用複製功能； 然而這種情況會導致redo log與binlog的一致性問題；MySQL通過內部XA機制解決這種一致性的問題。

第一階段：InnoDB prepare， write/sync redo log；binlog不作任何操作；

第二階段：包含兩步，1> write/sync Binlog； 2> InnoDB commit (commit in memory)；

當然在5.6之後引入了組提交的概念，可以在IO效能上進行一些提升，但總體的執行順序不會改變。

當第二階段的第1步執行完成之後，binlog已經寫入，MySQL會認為事務已經提交併持久化了(在這一步binlog就已經ready並且可以傳送給訂閱者了)。在這個時刻，就算資料庫發生了崩潰，那麼重啟MySQL之後依然能正確恢復該事務。在這一步之前包含這一步任何操作的失敗都會引起事務的rollback。

 

第二階段的第2步大部分都是記憶體操作，比如釋放鎖，釋放mvcc相關的read view等等。MySQL認為這一步不會發生任何錯誤，一旦發生了錯誤那就是資料庫的崩潰，MySQL自身無法處理。這個階段沒有任何導致事務rollback的邏輯。在程式執行層面，只有這一步完成之後，事務導致變更才能通過API或者客戶端查詢體現出來。

 

下面的一張圖，說明了MySQL在何時會將binlog傳送給訂閱者。

 

MySQL會在binlog落盤之後會立即將新增的binlog傳送給訂閱者以儘可能的降低主從延遲

 

如何開啟mysql的binlog

log-bin=mysql-bin #新增這一行就ok

binlog-format=row #選擇row模式

server_id=1 #配置mysql replaction需要定義，不能和canal的slaveId重複

在mysql的my.ini配置檔案中加上如上配置後重啟mysql

 

 

binlog常見格式

 

業內目前推薦使用的是row模式，準確性高，雖然說檔案大，但是現在有SSD和萬兆光纖網路，這些磁碟IO和網路IO都是可以接受的。

 

在innodb裡其實又可以分為兩部分，一部分在快取中，一部分在磁碟上。這裡業內有一個詞叫做刷盤，就是指將快取中的日誌刷到磁碟上。跟刷盤有關的引數有兩個個:sync_binlog和binlog_cache_size。這兩個引數作用如下

 

binlog_cache_size: 二進位制日誌快取部分的大小，預設值32k

sync_binlog=[N]: 表示每多少個事務寫入緩衝，刷一次盤,預設值為0

注意兩點:

(1)binlog_cache_size設過大，會造成記憶體浪費。binlog_cache_size設定過小，會頻繁將緩衝日誌寫入臨時檔案。

(2)sync_binlog=0:表示重新整理binlog時間點由作業系統自身來決定，作業系統自身會每隔一段時間就會重新整理快取資料到磁碟，這個效能最好。sync_binlog=1，代表每次事務提交時就會重新整理binlog到磁碟，對效能有一定的影響。sync_binlog=N,代表每N個事務提交會進行一次binlog重新整理。

另外，這裡存在一個一致性問題，sync_binlog=N，資料庫在作業系統當機的時候，可能資料並沒有同步到磁碟，於是再次重啟資料庫，會帶來資料丟失問題。