MySQL Binlog 高效遍歷搜尋與數據檢查實戰指南

前言

在生產環境中，開發人員發現某筆資料莫名其妙被新增，但無法確認來源，且業務邏輯不應產生此類資料。為了追查根因，最直接的方法是檢查 MySQL 二進制日誌。然而，Binlog 累積數月份的數據，若逐一使用 mysqlbinlog 解析極其低效，因此編寫腳本自動化遍歷搜尋成為必要。

實作步驟

1. 複製 Binlog 至暫存目錄

先將所有 Binlog 檔案複製到臨時目錄操作，避免影響線上服務：

cp /home/data/mysql/binlog/mysql-bin.* /tmp/bl/

2. 編寫遍歷搜尋腳本

建立 find_guolichao.sh 腳本，核心邏輯如下：

• 遍歷目錄下所有 mysql-bin.0* 檔案
• 使用 mysqlbinlog --base64-output=DECODE-ROWS -v 解碼 Row 格式日誌
• 多重 grep 過濾目標表、欄位與值（範例：AD_ADVERTISEMENT 表的 AD_LINK 欄位值為 ad_init_user）
• 將搜尋結果同時寫入總日誌 z_grep.log 與單檔日誌 z_grep_single.log，便於快速定位

[root@wgq_idc_dbm_3_61 tmp]# vim find_guolichao.sh
#!/bin/bash
cd /tmp/bl
for path in `ls . |grep mysql-bin.0`
do
    echo "" >> z_grep.log
    echo "begin ..."
    echo $path >> z_grep.log
    /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v $path |grep AD_ADVERTISEMENT |grep AD_LINK |grep ad_init_user >> z_grep.log
    /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v $path |grep AD_ADVERTISEMENT |grep AD_LINK |grep ad_init_user >> z_grep_single.log
    echo "end." >> z_grep.log
done

3. 執行腳本並分析結果

[root@wgq_idc_dbm_3_61 tmp]# bash -x find_guolichao.sh
[root@wgq_idc_dbm_3_61 tmp]# ll ./bl/z_grep*
-rw-r--r--. 1 root root 33534 1月 27 15:59 ./bl/z_grep.log
-rw-r--r--. 1 root root 0 1月 27 15:59 ./bl/z_grep_single.log

z_grep_single.log 大小為 0，表示近兩個月的 Binlog 中未找到目標資料，推測該資料為兩個月前寫入，需至歷史備份中進一步查找。

MySQL Binlog 數據檢查常用命令總結

Binlog 簡介與格式

• Binlog（二進制日誌）：記錄所有更改數據庫的操作。
• Statement 格式：記錄 SQL 語句，但無法保證所有語句在從庫重放成功（如 UPDATE ... LIMIT 1）。
• Row 格式：記錄每行實際變更，複雜 UPDATE/DELETE 操作下優勢明顯。

登入 MySQL 檢查 Binlog

-- 查看第一個 Binlog 文件內容
SHOW BINLOG EVENTS;

-- 查看指定 Binlog 文件
SHOW BINLOG EVENTS IN 'mysql-bin.000002';

-- 查看當前正在寫入的 Binlog 文件
SHOW MASTER STATUS\G

-- 獲取 Binlog 文件列表
SHOW BINARY LOGS;

使用 mysqlbinlog 工具檢查

注意事項：

• 切勿檢查正在寫入的 Binlog 文件
• 不要加 --force 強制訪問
• Row 格式請加 -vv 參數

本地檢查

# 按時間範圍
mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d 資料庫名 二進制文件

# 按 Position 範圍
mysqlbinlog --start-position=107 --stop-position=1000 -d 資料庫名 二進制文件

遠程檢查

mysqlbinlog -u username -p password -h 主機 -P 3306 \
--read-from-remote-server \
--start-datetime='2013-09-10 23:00:00' --stop-datetime='2013-09-10 23:30:00' \
mysql-bin.000001 > t.binlog

掌握這些技巧，能大幅提升 Binlog 排查效率，快速定位數據異常根因。