您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

mysqlbinlog 批量遍歷搜尋 Binlog 記錄實戰：腳本自動化定位異常數據來源

編輯：MySQL 綜合教學

mysqlbinlog 批量遍歷搜尋 Binlog 記錄實戰：腳本自動化定位異常數據來源

面對數月累積的 Binlog，單檔手工 mysqlbinlog | grep 效率極低。本文提供生產級 Shell 腳本，實現 Binlog 批量解碼、關鍵字多條件過濾、結果分級記錄，快速定位「莫名其妙寫入」的來源。

場景還原

開發反饋：AD_ADVERTISEMENT 表 AD_LINK 欄位出現異常值 ad_init_user，業務邏輯不應寫入此值。
Binlog 保留 2 個月，約 60+ 檔案，單檔 100MB-1GB。
目標：全量掃描找出具體 Binlog 檔案、具體事件、大約時間點。

核心工具：mysqlbinlog 關鍵參數

mysqlbinlog --base64-output=DECODE-ROWS -v binlog_file
# --base64-output=DECODE-ROWS : 將 ROW 格式 Binlog 解碼為可讀偽 SQL (含列名、值)
# -v (--verbose)              : 顯示更多細節 (ROW 事件解碼必加)
# --start-datetime / --stop-datetime : 時間範圍過濾 (若知大約時間)

自動化搜尋腳本 (生產級)

#!/bin/bash
# find_binlog_event.sh
# 用法: ./find_binlog_event.sh [搜尋關鍵字1] [關鍵字2] ...

set -euo pipefail

BINLOG_SOURCE_DIR="/home/data/mysql/binlog"
WORK_DIR="/tmp/binlog_search_$(date +%s)"
KEYWORDS=("$@")  # 從命令列傳入關鍵字，如: AD_ADVERTISEMENT AD_LINK ad_init_user
MYSQLBINLOG="/usr/local/mysql/bin/mysqlbinlog"

# 參數檢查
if [ ${#KEYWORDS[@]} -eq 0 ]; then
    echo "Usage: $0  [keyword2] ..."
    exit 1
fi

# 建立工作目錄
mkdir -p "$WORK_DIR"
cd "$WORK_DIR"

# 複製 Binlog (避免影響主庫磁碟 IO，建議從備份機複製)
echo "[$(date)] Copying binlogs from $BINLOG_SOURCE_DIR ..."
cp "$BINLOG_SOURCE_DIR"/mysql-bin.* . 2>/dev/null || { echo "No binlogs found"; exit 1; }

# 結果檔案
SUMMARY_LOG="search_summary.log"
DETAIL_LOG="search_detail.log"
> "$SUMMARY_LOG"
> "$DETAIL_LOG"

# 構建 grep 管線 (多關鍵字 AND 邏輯)
GREP_PIPE="grep ${KEYWORDS[0]}"
for kw in "${KEYWORDS[@]:1}"; do
    GREP_PIPE="$GREP_PIPE | grep $kw"
done

echo "[$(date)] Starting scan with keywords: ${KEYWORDS[*]}"
echo "Grep pipeline: $GREP_PIPE"

# 遍歷所有 Binlog 檔案
for binlog in mysql-bin.*; do
    echo "[$(date)] Scanning $binlog ..."
    echo "=== BEGIN $binlog ===" >> "$SUMMARY_LOG"
    echo "$binlog" >> "$SUMMARY_LOG"
    
    # 執行解碼並過濾，寫入摘要與詳細日誌
    eval "$MYSQLBINLOG --base64-output=DECODE-ROWS -v $binlog | $GREP_PIPE" >> "$SUMMARY_LOG" 2>&1
    eval "$MYSQLBINLOG --base64-output=DECODE-ROWS -v $binlog | $GREP_PIPE" >> "$DETAIL_LOG" 2>&1
    
    echo "=== END $binlog ===" >> "$SUMMARY_LOG"
    echo "" >> "$SUMMARY_LOG"
done

echo "[$(date)] Scan complete."
echo "Summary log: $WORK_DIR/$SUMMARY_LOG"
echo "Detail log:  $WORK_DIR/$DETAIL_LOG"

# 快速判斷是否有結果
if [ -s "$DETAIL_LOG" ]; then
    echo "✓ FOUND MATCHES in detail log"
    head -20 "$DETAIL_LOG"
else
    echo "✗ NO MATCHES in current binlog retention (check older backups)"
fi

執行範例與結果分析

$ ./find_binlog_event.sh AD_ADVERTISEMENT AD_LINK ad_init_user
[2024-01-15 10:30:00] Copying binlogs from /home/data/mysql/binlog ...
[2024-01-15 10:30:05] Starting scan with keywords: AD_ADVERTISEMENT AD_LINK ad_init_user
[2024-01-15 10:35:22] Scanning mysql-bin.000001 ...
...
[2024-01-15 10:45:10] Scan complete.
Summary log: /tmp/binlog_search_1705312200/search_summary.log
Detail log:  /tmp/binlog_search_1705312200/search_detail.log
✗ NO MATCHES in current binlog retention (check older backups)

$ ls -lh /tmp/binlog_search_1705312200/
-rw-r--r-- 1 root root 33K Jan 15 10:45 search_summary.log
-rw-r--r-- 1 root root  0  Jan 15 10:45 search_detail.log  # 0 字節 = 無匹配

結論：近 2 個月 Binlog 無此異常寫入，需檢查更早備份 (如月度全備解壓後的 Binlog)。

進階優化策略

時間範圍縮小：若知大約日期，加 --start-datetime='2024-01-01 00:00:00' --stop-datetime='2024-01-15 23:59:59' 大幅減少解碼量。
並行處理：使用 xargs -P 4 或 GNU parallel 並行解碼多檔 (CPU 密集型，視核心數而定)。
增量索引：定期 (每日) 執行腳本解碼當日 Binlog，建立關鍵字倒排索引 (Elasticsearch/ClickHouse)，查詢秒級完成。
遠程解碼：mysqlbinlog -R -h master_host -u user -p --start-datetime=... mysql-bin.000001 直接從主庫讀取，避免複製檔案 (需 --read-from-remote-server)。

Binlog 格式對搜尋的影響

格式	搜尋難度	說明
STATEMENT	低	原始 SQL 可直接 grep，但動態 SQL (NOW(), UUID()) 難追蹤具體值
ROW	高	必須 `--base64-output=DECODE-ROWS -v` 解碼，解碼慢、輸出大，但能見真實列值
MIXED	中	部分 STATEMENT 部分 ROW，需統一用 ROW 解碼參數