MySQL 二進制日志深度解析：mysqlbinlog 與 SHOW BINLOG EVENTS 實戰對照

MySQL 二進制日志深度解析：mysqlbinlog 與 SHOW BINLOG EVENTS 實戰對照

Binlog 是 MySQL 複製、增量恢復、審計的核心。本文透過實際操作（FLUSH LOGS、INSERT、USE db）對比 mysqlbinlog 原始輸出與 SHOW BINLOG EVENTS 結構化視圖，詳細解析事件結構、位置計算、事務邊界、非事務引擎行為差異。

一、實驗環境與操作序列

mysql> FLUSH LOGS;                              -- 產生新 binlog.000021
mysql> INSERT INTO test.t1 VALUES(1,'a');    -- 無 USE db
mysql> USE test;
mysql> INSERT INTO test.t1 VALUES(2,'b');    -- 有 USE db
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000021 | 546      |              |                  |
+------------------+----------+--------------+------------------+

二、mysqlbinlog 完整輸出解析

# at 4                                    -- 物理偏移 4，即檔案開頭
# FORMAT_DESCRIPTION_EVENT (binlog v4, server 5.6.31-log)

# at 120                                  -- 事務 1 開始
# QUERY_EVENT: BEGIN

# at 195                                  -- 事務 1 DML
# QUERY_EVENT: insert into test.t1 values(1,'a')

# at 298                                  -- 事務 1 提交
# XID_EVENT: COMMIT (xid=25)

# at 329                                  -- 事務 2 開始
# QUERY_EVENT: BEGIN

# at 408                                  -- 事務 2 DML (含 USE db)
# QUERY_EVENT: use `test`; insert into test.t1 values(2,'b')

# at 515                                  -- 事務 2 提交
# XID_EVENT: COMMIT (xid=33)

# 檔案結尾 ROLLBACK (mysqlbinlog 自動補全，防止部分恢復留下未提交事務)

三、關鍵觀察點

1. 位置即檔案大小：end_log_pos=546 對應 ls -l mysql-bin.000021 大小 546 bytes。
2. 同一 INSERT，有無 USE db 導致 binlog 內容不同：無 USE 時 SQL 含完整 db.tbl；有 USE 時 SQL 為短名。影響「基於庫的部分復制」判斷邏輯（binlog-ignore-db / replicate-ignore-db）。
3. 恢復截止點必須是 XID_EVENT 的 end_log_pos：如恢復事務 2，--stop-position=546（而非 515）。若停在 515，mysqlbinlog 會輸出 ROLLBACK 導致資料不生效。
4. 事務緩存機制：InnoDB 事務 Commit 時一次性寫入 binlog；Rollback 丟棄緩存。MyISAM 非事務引擎操作即時寫入 binlog，即使後續 Rollback，binlog 仍保留（主從會重放，造成主從不一致風險）。

四、SHOW BINLOG EVENTS 結構化視圖

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                          |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 | 4   | Format_desc | 1         | 120         | Server ver: 5.6.31-log, Binlog ver: 4         |
| mysql-bin.000021 | 120 | Query       | 1         | 195         | BEGIN                                         |
| mysql-bin.000021 | 195 | Query       | 1         | 298         | insert into test.t1 values(1,'a')             |
| mysql-bin.000021 | 298 | Xid         | 1         | 329         | COMMIT /* xid=25 */                           |
| mysql-bin.000021 | 329 | Query       | 1         | 408         | BEGIN                                         |
| mysql-bin.000021 | 408 | Query       | 1         | 515         | use `test`; insert into test.t1 values(2,'b') |
| mysql-bin.000021 | 515 | Xid         | 1         | 546         | COMMIT /* xid=33 */                           |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+

五、增量恢復實戰參數

# 從位置 120 開始，到 546 結束（含事務 2 完整提交）
mysqlbinlog --start-position=120 --stop-position=546 mysql-bin.000021 | mysql -uroot -p

# 指定資料庫過濾（ROW 格式需 -vv --base64-output=decode-rows）
mysqlbinlog -d test --start-position=120 --stop-position=546 mysql-bin.000021 | mysql -uroot -p test

六、Binlog 相關參數補充

總結：讀懂 mysqlbinlog 輸出、理解 Pos/End_log_pos 語義、掌握事務邊界（BEGIN/XID）與非事務引擎差異，是實施精準時間點恢復、排查主從延遲、審計資料變更的前提技能。