Mysql: Bin log原理以及三種格式

一、什么是 Binlog？

二、Binlog 的應用場景與案例

1. 數據恢復 (Point-in-Time Recovery)?

2. 主從復制 (Master-Slave Replication)

3. 數據審計

?三、Binlog 的三種格式

1. STATEMENT 模式 (Statement-Based Logging - SBL)

2. ROW 模式 (Row-Based Logging - RBL)

3. MIXED 模式 (Mixed-Based Logging - MBL)

一、什么是 Binlog？

????????Binlog（Binary Log）是 MySQL 數據庫中一種二進制日志文件，它記錄了所有對數據庫的更改操作，包括數據的插入、更新、刪除，以及表結構（DDL）的修改等。Binlog 以事件（event）的形式記錄這些操作，并且是邏輯性的，也就是說它記錄的是SQL語句的邏輯，而不是數據頁的物理變化。

????????它是 MySQL 數據庫服務層提供的，與具體的存儲引擎無關。無論你用的是 InnoDB、MyISAM 還是其他存儲引擎，只要是對數據庫有更改的操作，Server 層都會將它記錄到 Binlog 中。

????????Binlog 的主要目的是為了記錄數據庫發生改變的事件，并且它是一個追加寫入（append-only）的文件，一旦寫入就不會被修改。

二、Binlog 的應用場景與案例

1. 數據恢復 (Point-in-Time Recovery)?

場景： 假設你的電商網站數據庫在今天下午 2 點，因為運維人員誤執行了一條：

DELETE FROM products;?

導致商品表所有數據丟失。你昨天晚上 12 點對數據庫做了全量備份。

解決方案：

恢復全量備份： 首先，將昨天晚上 12 點的全量備份恢復到數據庫中，這樣數據就恢復到了昨天晚上 12 點的狀態。
應用 Binlog： 然后，你需要找到從昨天晚上 12 點備份開始到今天下午 2 點之間（誤刪除之前）的所有 Binlog 文件。使用 mysqlbinlog 工具解析這些 Binlog，并將其中的 SQL 語句應用到恢復后的數據庫上。這樣，數據庫就會從昨天晚上 12 點逐步恢復到今天下午 2 點誤刪除之前的狀態。

案例分析：

????????通過這種方式，即使發生了災難性的數據丟失，只要有定期備份和完整的 Binlog，就可以將數據庫恢復到任意一個精確的時間點，最大限度地減少數據損失。這是 Binlog 在數據安全中最核心的價值之一。

2. 主從復制 (Master-Slave Replication)

場景： 為了提高數據庫的讀取性能和可用性，你希望搭建一個 MySQL 主從復制集群，主庫負責寫入操作，從庫負責讀取操作。這也是Binlog的核心功能，我們來重點講解一下：

什么是 MySQL 主從復制？

????????MySQL 主從復制是一種將數據從一個 MySQL 數據庫服務器（主庫 Master）自動同步到一個或多個其他 MySQL 數據庫服務器（從庫 Slave）的過程。主庫處理所有的寫入操作，而從庫則接收并應用主庫的更新。

這個過程就像是：

主庫負責記錄所有最新的變化（比如賬本上的每一筆交易）。

從庫則實時地復制這份賬本，保證自己的數據和主庫保持一致。

Binlog 在 MySQL 主從復制中的實現與底層原理：

????????MySQL 的主從復制是一個異步（或半同步）的數據同步過程，其核心就是通過 Binlog 來實現的。整個過程可以概括為三個主要步驟：寫入 Binlog、傳輸 Binlog 和 重放 Binlog。

主從復制實現原理圖：

? ? ? ? ? ? ? ?

1. 主庫：寫入 Binlog (Binary Log)

原理： 當主庫執行任何數據修改操作（INSERT、UPDATE、DELETE、DDL 等）時，在事務提交之前，這些操作會被記錄到 Binlog 中。
實現細節：
- 事件記錄： MySQL 服務器會將這些操作封裝成 Binlog 事件。每個事件都包含了操作的類型、作用的數據庫和表、以及具體的數據變更內容（取決于 Binlog 格式，這個我們之后會提到：SQL 語句或行數據）。
- 寫入文件： 這些事件會按照順序寫入到當前活躍的 Binlog 文件中。Binlog 文件是追加寫入的，當一個文件達到最大大小時，MySQL 會自動創建一個新的 Binlog 文件。
- 索引文件： MySQL 還會維護一個 Binlog 索引文件（通常是 .index 后綴），記錄了所有 Binlog 文件的名稱，以便快速查找。
- 刷盤機制： 為了保證數據安全，Binlog 事件通常會定期或在每次事務提交時被刷寫（fsync）到磁盤上，這由 sync_binlog 參數控制。設置為 1 表示每次事務提交都刷盤，是最安全的，但性能開銷最大。

案例：

你連接到主庫，執行：

CREATE DATABASE my_shop;
USE my_shop;
CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100),price DECIMAL(10, 2)
);
INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
UPDATE products SET price = 1250.00 WHERE id = 1;

這些操作會依次被記錄到主庫的 Binlog 文件中（例如 mysql-bin.000001）。如果 Binlog 格式是 ROW，那么 INSERT 會記錄插入的行數據，UPDATE 會記錄更新前后的行數據。

2. 從庫：I/O 線程 (IO Thread) - 傳輸 Binlog

原理： 從庫上有一個專門的 I/O 線程，它負責連接到主庫，并請求主庫發送 Binlog 事件流。
實現細節：
- 連接主庫： 從庫的 I/O 線程使用在從庫配置中設置的主庫 IP 地址、端口、復制用戶和密碼來連接主庫。
- 發送請求： 從庫會告訴主庫它已經接收并處理到的 Binlog 文件名和位置（這是通過 CHANGE MASTER TO 命令設定的）。
- 主庫 Dump 線程： 主庫會為此從庫啟動一個Dump 線程 (Binlog Dump Thread)。這個 Dump 線程會讀取主庫的 Binlog 文件，并將其發送給從庫的 I/O 線程。
- 寫入 Relay Log： 從庫的 I/O 線程接收到 Binlog 事件后，會將這些事件順序寫入到自己的中繼日志 (Relay Log) 中。中繼日志也是一系列文件，與 Binlog 結構類似。同時，I/O 線程會更新 master.info 文件（或 mysql.slave_master_info 表），記錄當前已接收到的 Binlog 文件名和位置，以便下次斷開重連時可以從上次中斷的地方繼續。

? ? ? ? ? ? ? ? ? ? ? ??

3. 從庫：SQL 線程 (SQL Thread) - 重放 Binlog

原理： 從庫的 SQL 線程負責讀取中繼日志中的事件，并在從庫上逐個執行這些事件，從而使從庫的數據與主庫保持同步。
實現細節：
- 讀取 Relay Log： SQL 線程會從 Relay Log 中讀取事件。
- 解析并執行： 對于每個事件，SQL 線程會解析其內容，并將其作為 SQL 語句（或行數據變更）在從庫上執行。
- 事務處理： 如果主庫的 Binlog 中是一個事務，那么從庫的 SQL 線程也會將這個事務作為一個整體進行提交，確保事務的原子性。
- 進度記錄： SQL 線程會實時更新 relay-log.info 文件（或 mysql.slave_relay_log_info 表），記錄當前已經執行到的 Relay Log 文件名和位置，以及對應的主庫 Binlog 文件名和位置，這被稱為復制位點。

3. 數據審計

場景：公司的安全規定要求記錄所有對敏感數據的修改歷史，以便在出現問題時進行追溯。

解決方案：

啟用 Binlog： 確保數據庫啟用了 Binlog。
解析 Binlog： 定期或實時地使用 mysqlbinlog 工具解析 Binlog 文件。mysqlbinlog 可以將二進制格式的 Binlog 轉換為可讀的 SQL 語句。
審計分析： 將解析出來的 SQL 語句導入到日志分析系統或專門的審計工具中，進行進一步的分析、過濾和存儲。這樣就可以清晰地看到誰在什么時候對哪個表做了什么操作。

案例分析：

通過解析 Binlog，你可以發現：

非法操作： 哪些用戶執行了非授權的數據修改。
數據變更溯源： 某個字段的值是在何時被誰修改的，修改前后的值是什么。
性能分析： 某些高頻操作對數據庫的性能影響。

?三、Binlog 的三種格式

MySQL 的 Binlog 記錄格式由 binlog_format 參數控制，主要有三種：STATEMENT、ROW 和 MIXED。

1. STATEMENT 模式 (Statement-Based Logging - SBL)

記錄內容： 這種模式記錄的是導致數據改變的 SQL 語句本身。例如，如果你執行 UPDATE users SET age = age + 1 WHERE id = 1;，Binlog 中記錄的就是這條 SQL 語句。
優點：
- 日志文件小： 只需要記錄執行的 SQL 語句，而不是每條語句影響到的每一行數據的變化，因此 Binlog 文件通常比較小。這在網絡傳輸和存儲方面有優勢。
- 易于閱讀： 對于人來說，直接查看 SQL 語句更容易理解數據庫在特定時間點做了什么操作。
缺點：
- 可能導致主從數據不一致： 這是 STATEMENT 模式最大的問題。某些不確定性函數（如 UUID()、NOW()、RAND()）、存儲過程、觸發器等，在主庫和從庫上執行時可能產生不同的結果，從而導致主從數據不一致。
- 對復雜操作支持不好： 對于像 LOAD DATA INFILE 或包含子查詢的復雜 UPDATE 語句，有時難以準確地在從庫上重放。

案例分析：

假設主庫和從庫的當前時間不同（在復制中這很常見）。

你執行以下 SQL 語句：

INSERT INTO orders (order_time) VALUES (NOW());

主庫： 在主庫上執行時，NOW() 函數會獲取主庫當前時間，比如 2025-07-09 14:30:00。Binlog 記錄的也是 INSERT INTO orders (order_time) VALUES (NOW()); 這條語句。
從庫： 從庫收到這條 Binlog 后，會重放這條 SQL 語句。如果從庫的時間比主庫快或慢，或者因為網絡延遲等原因導致從庫執行時間點和主庫不同，那么從庫上的 NOW() 函數獲取的時間可能與主庫不同（例如 2025-07-09 14:30:05），從而導致主從 order_time 字段的值不一致。

再比如：

DELETE FROM products LIMIT 1;

這條語句沒有 ORDER BY 子句，刪除哪一行是不確定的。
主庫： 可能刪除了 id = 10 的產品。
從庫： 從庫收到語句后重放，可能刪除了 id = 20 的產品，導致主從數據不一致。?

2. ROW 模式 (Row-Based Logging - RBL)

記錄內容： 這種模式記錄的是 行級別的數據變更，包括哪些行被修改了，以及修改前和修改后的行數據。它不記錄具體的 SQL 語句。
優點：
- 主從數據一致性高： 這是最主要的優點。因為它記錄的是最終的數據變更結果，所以無論 SQL 語句有多復雜或是否包含不確定性函數，從庫都能準確地復制主庫的變更，保證數據一致性。
- 更安全： 避免了 STATEMENT 模式可能導致的主從不一致問題，尤其適用于復雜的數據庫操作。
- 可以進行精確恢復： 在數據恢復時，可以精確到行的變更。
缺點：
- 日志文件可能非常大： 特別是對于 UPDATE 或 DELETE 大量行的操作。例如，一個 UPDATE 語句更新了 100 萬行數據，Binlog 中就會記錄 100 萬行數據的變更，導致 Binlog 文件急劇膨脹，占用大量磁盤空間，并增加網絡傳輸負擔。
- 不易于閱讀： Binlog 中記錄的是二進制的行數據變化，不直觀，無法直接像 SQL 語句那樣閱讀和理解。

案例分析：

依然是剛才的 INSERT INTO orders (order_time) VALUES (NOW()); 語句。

主庫： 執行 INSERT 后，主庫會將新插入行的所有列的值（包括 order_time 的具體值，例如 2025-07-09 14:30:00）記錄到 Binlog 中。
從庫： 從庫收到 Binlog 后，直接按照 Binlog 中記錄的數據值插入一行，而不會重新計算 NOW()。因此，主從數據庫中的 order_time 字段會完全一致。

再比如：

UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

主庫： 如果有 1000 行 category = 'electronics' 的產品，Binlog 會記錄這 1000 行的 每一行 的變更（舊值和新值）。
從庫： 從庫會根據 Binlog 中記錄的 1000 行的變更信息，直接更新相應的行，而不是再次執行 UPDATE 語句。這樣保證了主從數據的一致性。

3. MIXED 模式 (Mixed-Based Logging - MBL)

記錄內容： MIXED 模式是 STATEMENT 和 ROW 模式的混合體。MySQL 會根據執行的 SQL 語句，自動判斷 應該使用 STATEMENT 模式還是 ROW 模式來記錄 Binlog。
- 對于那些不會導致主從不一致的 SQL 語句（例如簡單的 SELECT、INSERT INTO ... VALUES、DELETE FROM ... WHERE id = X），MySQL 會使用 STATEMENT 模式，以保持 Binlog 文件較小。
- 對于那些可能導致主從不一致的語句（例如包含 NOW()、RAND() 的語句，或者沒有 ORDER BY 的 LIMIT 語句等），MySQL 會自動切換到 ROW 模式來記錄。
優點：
- 兼顧一致性和性能： 在保證數據一致性的前提下，盡量減小 Binlog 文件的大小。這是目前推薦的 Binlog 格式，平衡了兩種模式的優缺點。
- 智能切換： 由 MySQL 自動判斷，無需 DBA 手動干預。
缺點：
- 理解復雜性： 對于分析 Binlog 的人來說，Binlog 中可能同時包含 STATEMENT 和 ROW 事件，需要更復雜的解析工具來處理。

案例分析：

執行 INSERT INTO users (name) VALUES ('Alice');：這是一條簡單且確定性的 SQL 語句，MySQL 會以 STATEMENT 模式記錄。
執行 INSERT INTO orders (order_time) VALUES (NOW());：由于 NOW() 函數的不確定性，MySQL 會自動切換到 ROW 模式記錄，確保 order_time 在主從之間完全一致。
執行 DELETE FROM temp_logs LIMIT 100;：如果沒有 ORDER BY 子句，刪除哪些行是不確定的，MySQL 會以 ROW 模式記錄。如果加上 ORDER BY 子句（例如 DELETE FROM temp_logs ORDER BY log_id LIMIT 100;），則可以以 STATEMENT 模式記錄，因為刪除的順序是確定的。

總結：

STATEMENT： 最古老的模式，Binlog 文件小，但存在主從不一致的風險。不推薦在新環境中使用。

ROW： 最安全、最可靠的模式，確保主從數據一致，但 Binlog 文件可能很大。在對數據一致性要求極高，且不介意 Binlog 文件大小的場景下使用。

MIXED： 目前最推薦的模式。它智能地平衡了安全性和效率，既保證了數據一致性，又在可能的情況下減小了 Binlog 文件。

在實際生產環境中，MIXED 模式通常是最佳選擇，因為它在保證數據一致性的同時，能夠有效地管理 Binlog 文件的大小。