MySQL 二進制日誌維護與基於 Binlog 的數據恢復實戰

Binlog 是 MySQL 時間點恢復 (PITR) 與主從復制的核心。本文實戰演示 Binlog 清理、位置/時間點恢復、異地備份策略、誤操作 (DROP TABLE) 緊急恢復流程。

1. 確認無從庫再清理 Binlog

SHOW PROCESSLIST;
-- 無 "Binlog Dump" 線程表示無從庫同步

-- 清空所有 Binlog, 重置索引
RESET MASTER;
SHOW MASTER STATUS;  -- 僅剩 mysql-bin.000001, Position 154

2. 保留近期 Binlog (按文件名/位置/時間)

-- 刪除指定文件之前的所有 Binlog
PURGE MASTER LOGS TO 'mysql-bin.000018';

-- 或按時間
PURGE MASTER LOGS BEFORE '2024-01-15 10:00:00';

3. 異地備份 Binlog (防單點故障)

[mysqld]
log-bin = /disk2/binlogs/mysql-bin  # 獨立磁盤/掛載點
expire_logs_days = 7
max_binlog_size = 100M

-- 重啟後生效, 會在 /disk2/binlogs/ 生成 mysql-bin.000001, .index

4. Binlog 恢復實戰：誤 DROP TABLE 緊急恢復

步驟 1：定位關鍵位置

mysqlbinlog /disk2/binlogs/mysql-bin.000001 | less
-- 找到 CREATE TABLE 之後、DROP TABLE 之前的 position
-- 假設: CREATE 後 position=4, DROP 前 position=1000

步驟 2：按位置提取並重放 (推薦)

mysqlbinlog --start-position=4 --stop-position=1000 /disk2/binlogs/mysql-bin.000001 | mysql -u root -p
-- 注意: --start-position 必須從 4 開始 (Format Description Event), 不能直接從 CREATE 的 position 開始

步驟 3：或按時間範圍恢復

mysqlbinlog --start-datetime="2024-01-14 10:20:00" --stop-datetime="2024-01-14 10:25:00" /disk2/binlogs/mysql-bin.000001 | mysql -u root -p

5. 恢復過程常見報錯與處理

• ERROR 1062 (23000): Duplicate entry 'xxx' for key 'PRIMARY'：數據已存在 (重放包含已執行的 INSERT)，可忽略 或加 --force 繼續。
• ERROR 1032: Can't find record：DELETE/UPDATE 找不到行，說明數據狀態已偏移，需從更早位置恢復或全量恢復。
• 大事務恢復慢：調大 max_allowed_packet、net_read_timeout。

6. 避坑指南

1. 不要手動刪除 Binlog 文件，必須用 PURGE MASTER LOGS 或 RESET MASTER，否則 .index 文件不同步導致啟動報錯。
2. Binlog 格式建議 ROW：binlog_format=ROW，避免 MIXED/STATEMENT 下非確定性函數 (UUID、NOW) 導致主從不一致。
3. 定期驗證恢復演練：每季度在測試環境用 Binlog 恢復至指定時間點，校驗數據完整性。
4. GTID 模式下恢復更簡單：SET GTID_PURGED='...'; 配合 mysqlbinlog --include-gtids 精確提取事務集合。

#!/bin/bash
# daily_incr_backup.sh
mysqladmin flush-logs -u root -p'pwd'  # 產生新 Binlog
if [ $? -eq 0 ]; then
    # 移動除最新外的所有 Binlog 到備份目錄
    ls -1 /disk2/binlogs/mysql-bin.0* | head -n -1 | xargs -I{} mv {} /backup/binlogs/
fi

# 週日全備腳本
# mysqldump --single-transaction --master-data=2 --all-databases > /backup/full_$(date +%F).sql