快速帶你讀懂MySQL的binlog寫入機制

深入講解MySQL中的重要日志 binlog 的寫入機制以及影響IO性能的關鍵配置，并且介紹了如何利用binlog去恢複資料，保證MySQL的可靠性。

Q：binlog寫入時機

binlog 的寫入邏輯并不複雜：事務執行過程中，先把日志寫到 binlog cache，事務送出的時候，再把 binlog cache 寫到 binlog 檔案中。

什麼是binlog cache？

binlog cache 在事務的運作期間暫存着binlog的資料，binlog cache 是在每個線程内空間獨立的。如果啟用了bin log日志，MySQL 會為每個用戶端配置設定一個二進制日志緩存。如果您經常使用大型事務，則可以增加此緩存大小以獲得更好的性能，可通過 binlog_cache_size 配置其大小，預設 32768 bytes。

如果binlog cache空間足夠，在事務送出的時候，cache中的内容會被清空，同時這些資料會被寫入到 binlog files 中；因為 bin log内容無論多大在事務送出時都需要一次性寫入，是以當 bin log cache放不下的時候，就需要暫存到磁盤，然後送出被寫入到 binlog files。

參數 binlog_cache_size:控制單個線程内 binlog cache 所占記憶體的大小

上面說的 寫入到 bin log 中其實又拆為兩部分：

• write首先會寫入 page cache 中的 binlog files 中，page cache 就是一塊記憶體。（不占用磁盤IOPS）
• fsync然後作業系統執行 fsync 時 bin log 才會從 page cache 中真正持久化到磁盤。（占用磁盤IOPS）

write 和 fsync寫入時機

關于write和fsync 的時機，是通過參數 sync_binlog 控制：

• sync_binlog=0：表示每次送出事務隻是 write，不執行 fsync，也就是binlog不做持久化。（不建議）
• sync_binlog=1：表示每次送出事務都要發生 fsync。
• sync_binlog=N：表示每次事務都會write，但是N次事務送出會執行fsync進行持久化。

通常來說，為了提高IOPS，會将這個參數設為100-1000。缺點是如果還沒有執行fsync就當機，最多會丢失最近N個事務的binlog日志。如果為了保證資料安全，就設為1。

如果binlog内容缺失怎麼辦？

對于通過 redo log 可以恢複資料，我們都已經知道了，但是如果 binlog 内容缺失呢？

根據 sync_binlog 參數描述，如果設定為大于一，就代表N次事務才會将log持久化到磁盤中。而與此同時，redo log 的 innodb_flush_log_at_trx_commit 參數設定為1，表示每次事務送出都會執行 fsync。

這就産生了一種可能，如果發生mysql伺服器了crash，此時redolog已經做持久化，但是binlog還有事務仍儲存在page cache中，沒有來得及執行fsync。當機後重新開機服務，依賴redolog恢複資料，就會出現binlog 長度比真實資料所應該需要的長度短的情況。

面對資料完整但是binlog缺失，這種情況下會出現什麼異樣呢？

如果 MySQL 伺服器在崩潰恢複時發現二進制日志比應有的短，則它至少缺少一個成功送出的 InnoDB 事務。如果 sync_binlog=1 并且磁盤/檔案系統在請求時進行實際同步（有些則沒有），則不會發生這種情況，是以伺服器列印一條錯誤消息日志。在這種情況下，此二進制日志不正确，應從源資料的新快照重新啟動複制。

按照官方描述，如果将 sync_binlog設為1不會出現這種情況，如果出現了binlog比預期少，則會在伺服器列印一條binlog日志的異常log：The binary log xxx is shorter than its expected size.

是以這種情況需要去人為幹預處理，從最新的快照資料源重新複制。

如何使用binlog恢複曆史資料？

binlog 會記錄所有的邏輯操作，并且是采用“追加寫”的形式，不會像redolog一樣去覆寫日志檔案。binlog 裡面的資料是可靠的，一定是事務送出後的資料，這就全靠 redolog 來保證。

衆所周知，我們可以通過 binlog 恢複任意時間點的資料，如何做到呢？

其實這是有前提的，我們必須要有定期備份全量資料的機制，比如半個月、每周、或者每天。

假定場景：比如今天中午12點有一次誤删表資料，需要找回資料，如何做？

• 首先，找到最近的一次全量備份，比如昨天淩晨1點，那就從這個備份恢複到臨時庫；
• 然後，從備份的時間昨天淩晨1點開始，将備份的 binlog 依次取出來重放，直到今天中午12點的資料。

這樣臨時庫就到達了誤删資料之前的狀态，然後可以把表資料從臨時庫取出來，按需求恢複到線上正式資料庫。