深入講解MySQL中的重要日志 binlog 的寫入機制以及影響IO性能的關(guān)鍵配置，并且介紹了如何利用binlog去恢復(fù)數(shù)據(jù)，保證MySQL的可靠性。

Q：binlog寫入時機

binlog 的寫入邏輯并不復(fù)雜：事務(wù)執(zhí)行過程中，先把日志寫到 binlog cache，事務(wù)提交的時候，再把 binlog cache 寫到 binlog 文件中。

什么是binlog cache？

binlog cache 在事務(wù)的運行期間暫存著binlog的數(shù)據(jù)，binlog cache 是在每個線程內(nèi)空間獨立的。如果啟用了bin log日志，MySQL 會為每個客戶端分配一個二進制日志緩存。如果您經(jīng)常使用大型事務(wù)，則可以增加此緩存大小以獲得更好的性能，可通過 binlog_cache_size 配置其大小，默認 32768 bytes。

如果binlog cache空間足夠，在事務(wù)提交的時候，cache中的內(nèi)容會被清空，同時這些數(shù)據(jù)會被寫入到 binlog files 中；因為 bin log內(nèi)容無論多大在事務(wù)提交時都需要一次性寫入，所以當 bin log cache放不下的時候，就需要暫存到磁盤，然后提交被寫入到 binlog files。

參數(shù) binlog_cache_size:控制單個線程內(nèi) binlog cache 所占內(nèi)存的大小

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

• write首先會寫入 page cache 中的 binlog files 中，page cache 就是一塊內(nèi)存。（不占用磁盤IOPS）
• fsync然后操作系統(tǒng)執(zhí)行 fsync 時 bin log 才會從 page cache 中真正持久化到磁盤。（占用磁盤IOPS）

write 和 fsync寫入時機

關(guān)于write和fsync 的時機，是通過參數(shù) sync_binlog 控制：

• sync_binlog=0：表示每次提交事務(wù)只是 write，不執(zhí)行 fsync，也就是binlog不做持久化。（不建議）
• sync_binlog=1：表示每次提交事務(wù)都要發(fā)生 fsync。
• sync_binlog=N：表示每次事務(wù)都會write，但是N次事務(wù)提交會執(zhí)行fsync進行持久化。

通常來說，為了提高IOPS，會將這個參數(shù)設(shè)為100-1000。缺點是如果還沒有執(zhí)行fsync就宕機，最多會丟失最近N個事務(wù)的binlog日志。如果為了保證數(shù)據(jù)安全，就設(shè)為1。

如果binlog內(nèi)容缺失怎么辦？

對于通過 redo log 可以恢復(fù)數(shù)據(jù)，我們都已經(jīng)知道了，但是如果 binlog 內(nèi)容缺失呢？

根據(jù) sync_binlog 參數(shù)描述，如果設(shè)置為大于一，就代表N次事務(wù)才會將log持久化到磁盤中。而與此同時，redo log 的 innodb_flush_log_at_trx_commit 參數(shù)設(shè)置為1，表示每次事務(wù)提交都會執(zhí)行 fsync。

這就產(chǎn)生了一種可能，如果發(fā)生mysql服務(wù)器了crash，此時redolog已經(jīng)做持久化，但是binlog還有事務(wù)仍保存在page cache中，沒有來得及執(zhí)行fsync。宕機后重啟服務(wù)，依賴redolog恢復(fù)數(shù)據(jù)，就會出現(xiàn)binlog 長度比真實數(shù)據(jù)所應(yīng)該需要的長度短的情況。

面對數(shù)據(jù)完整但是binlog缺失，這種情況下會出現(xiàn)什么異樣呢？

如果 MySQL 服務(wù)器在崩潰恢復(fù)時發(fā)現(xiàn)二進制日志比應(yīng)有的短，則它至少缺少一個成功提交的 InnoDB 事務(wù)。如果 sync_binlog=1 并且磁盤/文件系統(tǒng)在請求時進行實際同步（有些則沒有），則不會發(fā)生這種情況，所以服務(wù)器打印一條錯誤消息日志。在這種情況下，此二進制日志不正確，應(yīng)從源數(shù)據(jù)的新快照重新啟動復(fù)制。

按照官方描述，如果將 sync_binlog設(shè)為1不會出現(xiàn)這種情況，如果出現(xiàn)了binlog比預(yù)期少，則會在服務(wù)器打印一條binlog日志的異常log：The binary log xxx is shorter than its expected size.

所以這種情況需要去人為干預(yù)處理，從最新的快照數(shù)據(jù)源重新復(fù)制。

如何使用binlog恢復(fù)歷史數(shù)據(jù)？

binlog 會記錄所有的邏輯操作，并且是采用“追加寫”的形式，不會像redolog一樣去覆蓋日志文件。binlog 里面的數(shù)據(jù)是可靠的，一定是事務(wù)提交后的數(shù)據(jù)，這就全靠 redolog 來保證。

眾所周知，我們可以通過 binlog 恢復(fù)任意時間點的數(shù)據(jù)，如何做到呢？

其實這是有前提的，我們必須要有定期備份全量數(shù)據(jù)的機制，比如半個月、每周、或者每天。

假定場景：比如今天中午12點有一次誤刪表數(shù)據(jù)，需要找回數(shù)據(jù)，如何做？

• 首先，找到最近的一次全量備份，比如昨天凌晨1點，那就從這個備份恢復(fù)到臨時庫；
• 然后，從備份的時間昨天凌晨1點開始，將備份的 binlog 依次取出來重放，直到今天中午12點的數(shù)據(jù)。

這樣臨時庫就到達了誤刪數(shù)據(jù)之前的狀態(tài)，然后可以把表數(shù)據(jù)從臨時庫取出來，按需求恢復(fù)到線上正式數(shù)據(jù)庫。