Bin log 保證了數據的可靠性和一致性，MySQL 數據庫的數據備份、主備、主主、主從等的實現，都依賴於 Bin log。

毫不誇張地說，Bin log 是 MySQL 極爲重要的內核之一，同時，Bin log 也是 MySQL 的高頻面試題，大廠校招、社招面試 90% 會問到。

大家好，我是愛分享的程序員寶妹兒，分享即學習。

本篇是 Bin log 的終結篇，包含了 Bin log 系列的全部內容，想要了解 Bin log 的成體系知識，看這一篇就足夠了，寶子們，看了記得關注 + 收藏哦。

本文內容包括：

• Bin log 基礎概述：概念、優缺點、使用場景、配置、參數等

• 基於 Bin log 實現 MySQL 主從複製，5 個必看關鍵步驟

• Bin log 恢復 MySQL 數據，7 步快速搞定

• Bin log 三大模式：Statement、Row、Mixed

• Bin log 增加過快，影響 MySQL 性能怎麼辦

• Bin log、redo log 和 Undo log 的區別，2 分鐘喫透

01

 Bin log 的概念

在 MySQL 中，Bin log 是一種日誌文件，以二進制形式記錄事務的開始、提交和回滾，以及數據的增、刪、改操作。

通過 Bin log，我們可以追蹤和還原數據庫中的操作歷史。

02

Bin log 的作用

Bin log 的兩個主要作用：

2.1 數據恢復

Bin log 記錄了每個事務的詳細操作，包括數據的修改和事務的狀態變更。

使用 Bin log 恢復 MySQL 數據，簡單地說，就是讓 MySQL 將保存在 Bin log 日誌中指定段落區間的 sql 語句，逐個重新執行一次而已。

Bin log 恢復數據主要有如下 7 個步驟：

1）查看當前 Bin log 的位置

2）tb_person 表中插入兩條記錄

3）記錄當前 Bin log 位置

4）查詢數據

......

查看詳解篇：Bin log 數據恢復，7 個步驟搞定，再不怕 MySQL 宕機了

2.2 主從複製

Bin log 是實現主從複製的關鍵，主數據庫將修改操作記錄到 Bin log 中，從數據庫通過解析 Bin log 實現數據的同步。

MySQL 主從複製中的主要線程

• master（Bin log dump thread）

• slave（I/O thread 、SQL thread）

• Master 的一條線程

• Slave 中的兩條線程

查看詳解篇：基於 Bin log 實現 MySQL 複製，5 個關鍵步驟務必掌握！

03

 Bin log 日誌結構

Bin log 日誌文件包含了索引文件和具體日誌文件。

• 索引文件：用於跟蹤日誌文件，每行一個日誌文件。默認情況下，索引文件名爲 {Host 名}-bin.index。

• 日誌文件：是由一系列事件 (Binary Log Events) 組成。默認情況下，文件名爲{Host 名}-bin.NNNNNN。後綴六個數字，是編號，用於區分不同的日誌文件。

每個 Bin log 事件由四個部分組成：

• 通用 Header：存放事件的基本信息：事件類型和事件數據大小。

• Post Header：存放特定事件類型的相關信息

• 事件實體：存儲事件的數據，如執行過的語句和變更的實際數據

• Checksum：MySQL5.6 新特性，用於檢查數據是否損壞。

04

Bin log 的相關參數

• log_bin ：啓用 Bin log 功能，並指定路徑名稱

• log_bin_index ：指定二進制索引文件的路徑與名稱

• Bin log_do_db ：只記錄指定數據庫的二進制日誌

• Bin log_ignore_db ：不記錄指定的數據庫的二進制日誌

• max_Bin log_cache_size ：Bin log 使用的內存最大的尺寸

• Bin log_cache_size ：Bin log 使用的內存大小

• Bin log_cache_use ：使用二進制日誌緩存的事務數量

• Bin log_cache_disk_use : 使用二進制日誌緩存

• max_Bin log_size ：Bin log 最大值，最大和默認值是 1GB

• sync_Bin log ：這個參數直接影響 MySQL 的性能和完整性

• sync_Bin log=0 ：當事務提交後，MySQL 僅僅是將 Bin log_cache 中的數據寫入 Bin log 文件，但不執行 fsync 之類的磁盤 同步指令通知文件系統將緩存刷新到磁盤，而讓 Filesystem 自行決定什麼時候來做同步，這個是性能最好的。

• sync_Bin log=n ：在進行 n 次事務提交以後，MySQL 將執行一次 fsync 之類的磁盤同步指令，同志文件系統將 Bin log 文件緩存刷新到磁盤。

在 MySQL 中，默認設置是 sync_Bin log=0 ，即不作任何強制性的磁盤刷新指令。

sync_Bin log=0 的性能最好、但風險也最大，一旦系統繃 Crash ，在文件系統緩存中的所有 Bin log 信息都會丟失。

05

Bin log 的三種模式

針對不同的應用場景，Bin log 推出了三種模式 Statement、Row、Mixed，以滿足對數據庫的需求。

Bin log 模式的優缺點：

• statement： 基於 SQL 語句的模式，某些語句和函數如 UUID, LOAD DATA INFILE 等在複製過程可能導致數據不一致甚至出錯。

• row： 基於行的模式，記錄的是行的變化，很安全。但是 Bin log 會比其他兩種模式大很多，在一些大表中清除大量數據時在 Bin log 中會生成很多條語句，可能導致從庫延遲變大。

• mixed： 混合模式，根據語句來選用是 statement 還是 row 模式。

Bin log 模式選型思路：

• 使用 MySQL 特殊功能較少，例如存儲過程、觸發器、函數等，用 Statement 模式。

• 使用 MySQL 特殊功能較多，用 Mixed 模式。

• 使用 MySQL 特殊功能較多，同時希望數據最大化一致，用 Row 模式。

關於 Bin log 三種模式 Statement、Row、Mixed，及其特點、優缺點、應用場景、配置、切換等。

具體查看詳解篇：喫透 3 大 Bin log 模式，MySQL 數據零丟失

06

Bin log 的開啓、關閉、刪除

Bin log 的開啓

在 MySQL 配置文件 my.cnf 中增加 log-bin 參數，即可開啓 Bin log。

[MySQLd]
log-bin=MySQL-bin

通過命令行的方式開啓 Bin log：

SET SQL_LOG_BIN=1

Bin log 的關閉

通過命令行的方式關閉 Bin log：

SET SQL_LOG_BIN=0

Bin log 的刪除

過期刪除

MySQL> show variables like 'expire_log_days';
MySQL> set global expire_log_days=7;

reset master 會刪除所有 Bin log，reset slave 會刪除所有 relay log。

07

  Bin log 空間查詢

在數據庫管理中，定期查詢和監控 Bin log 的空間佔用情況，評估和監控系統的運行狀態，預防磁盤空間不足和性能問題的發生，確保數據庫的正常運行。

此時，我們可以通過 MySQL 的內置命令和查詢，來評估和監控 Bin log 的大小和增長趨勢。

一些查詢 Bin log 空間的常用方法和命令：

• SHOW VARIABLES LIKE 'max_Bin log_size'; ：用於查詢 Bin log 的最大大小限制，這對於評估是否需要調整 Bin log 的大小很有用。

• SHOW BINARY LOGS; ：用於查詢當前已生成的 Bin log 文件列表，瞭解已使用的 Bin log 空間，以及每個 Bin log 文件的大小和生成時間等信息。

可以將這些命令嵌入到腳本或管理工具中，實現自動化的 Bin log 空間查詢和監控。

08

 Bin log 增長過快怎麼辦？

當 Bin log 增長過快時，磁盤空間佔用過多，就會導致磁盤空間不足或性能下降等問題，影響數據庫性能和穩定性。

Bin log 增長過快的主要原因：

• 大事務

• 頻繁的 DDL 操作

• 長時間的讀事務

• 錯誤的配置參數

Bin log（歸檔日誌）增長過快的解決方案：

• 拆分大事務

• 優化 DDL 操作

......

詳解篇：Bin log 增長過快怎麼辦？5 個方法完美解決！

09

 Bin log、redolog 和 undolog 的區別

Bin log、Redo log 和 Undo log 的區別，是 MySQL 的高頻面試題，大廠校招面試 90% 會問。

在 MySQL 數據庫中，Bin log、Redo log 和 Undo log 都是極爲重要的日誌文件。

MySQL InnoDB 引擎:

使用 redo log （重做日誌）保證事務的持久性。

使用 Undo log （回滾日誌）來保證事務的原子性。

使用 Bin log（歸檔日誌）同步數據，保證數據一致性。

MySQL 實現事務、崩潰恢復、集羣的主從複製等，底層都離不開日誌，可以說日誌是 MySQL 的內核所在。

只有瞭解 MySQL 日誌，纔算是徹底搞懂 MySQL。

詳解篇：MySQL 的 Bin log、redo log 和 Undo log 的區別，2 分鐘徹底搞懂！

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/VXyMS4UHKOHbW2qfffIoQA