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分鐘吃透
PS.
寶妹兒已将Bin log 系列，整理到2023版《MySQL 大廠高頻面試題大全》PDF了，友善系統學習、面試通關。
《MySQL 大廠高頻面試題大全》PDF，已收錄100+道真題，一共78頁，近30000字，需要的小夥伴自取。
吃透它，足以應付MySQL面試。
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）查詢資料
......
檢視詳解篇：
2.2 主從複制
Bin log 是實作主從複制的關鍵，主資料庫将修改操作記錄到 Bin log 中，從資料庫通過解析 Bin log 實作資料的同步。
MySQL 主從複制中的主要線程
- master（Bin log dump thread）
- slave（I/O thread 、SQL thread）
- Master 的一條線程
- Slave 中的兩條線程
檢視詳解篇：
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，及其特點、優缺點、應用場景、配置、切換等。
具體檢視詳解篇：
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 操作
......
詳解篇：
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。
詳解篇：
10
總結
到這裡，寶妹兒的 Bin log 系列創作暫時就告一段落了，未來還會繼續補充完善，更多體系化的内容分享持續送達。
最後，謝謝“記**記”、“E***T”、....等小夥伴們的支援，向寶妹兒提供了很多非常棒的建議.....
謝謝您的關注、點贊、建議，這是鼓勵寶妹兒堅持前行的重要力量。
PS.
Bin log 系列已收錄于寶妹兒精編的 2023版《MySQL 大廠高頻面試題大全》PDF。
《MySQL 大廠高頻面試題大全》一共78頁，34000字，圖文并茂，長期持續更新。
吃透它，足以應對 MySQL 面試，如圖自取所需。