萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL Binlog 日誌實戰數據恢復:誤刪表/數據的完整演練流程

前置條件:必須開啟 Binlog

[mysqld]
log-bin = mysql-bin
binlog_format = ROW          -- 推薦 ROW 格式,記錄行變更細節
expire_logs_days = 14        -- 保留 14 天
max_binlog_size = 1G
sync_binlog = 1              -- 雙1保證不丟數據

重啟後驗證:SHOW MASTER STATUS; 顯示 Binlog 文件名與位點。

演練場景:誤 TRUNCATE 表並 DROP 表

1. 初始環境

CREATE DATABASE test;
USE test;
CREATE TABLE t1 (id INT PRIMARY KEY);
INSERT INTO t1 VALUES (1), (2);
-- 當前數據:1, 2

2. 完整備份(基線)

mysqldump -uroot -p --single-transaction --master-data=2 test > /tmp/test_full.sql
-- --master-data=2 會在備份文件頭部寫入 CHANGE MASTER TO 位點信息(註釋形式)

3. 模擬誤操作與 Binlog 切換


INSERT INTO t1 VALUES (3), (4), (5);  -- 新增 3 條
FLUSH LOGS;  -- 關閉當前 Binlog,生成新文件 mysql-bin.000002
-- 此時 mysql-bin.000001 含初始 1,2 + INSERT 3,4,5
-- mysql-bin.000002 為空,等待後續寫入

TRUNCATE TABLE t1;   -- 清空表
DROP TABLE t1;       -- 刪除表
-- 數據庫現狀:表 t1 不存在,數據全無

4. 恢復步驟

第一步:恢復全量備份(回到備份時間點)

mysql -uroot -p test < /tmp/test_full.sql
-- 此時表 t1 存在,數據:1, 2

第二步:回放 Binlog 增量(從備份位點到誤操作前)


# 查看備份文件中的位點信息
head -30 /tmp/test_full.sql
-- ...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
-- ...

# 回放 mysql-bin.000001 從位點 154 到 FLUSH LOGS 前
mysqlbinlog --no-defaults \
  --start-position=154 \
  --stop-position=1055 \   -- 此處需通過 more 查看 FLUSH LOGS 事件的位點
  /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p test

# 驗證:表 t1 應有 1,2,3,4,5
SELECT * FROM t1;

第三步:若誤操作在同一 Binlog 文件,精確截取


# 查看 Binlog 事件
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000001 | more
# 找到 TRUNCATE/DROP 事件的位點,設定 --stop-position 在其之前

# 精確恢復
mysqlbinlog --no-defaults \
  --start-position=154 \
  --stop-position=794 \   -- 假設 TRUNCATE 在 794
  /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p test

Binlog 管理常用命令速查

操作命令
查看當前 BinlogSHOW MASTER STATUS;
列出所有 BinlogSHOW BINARY LOGS;
查看 Binlog 內容mysqlbinlog --no-defaults -v binlog_file | more
按位點導出 SQLmysqlbinlog --start-position=X --stop-position=Y binlog_file > replay.sql
按時間導出mysqlbinlog --start-datetime="2023-11-19 10:00:00" --stop-datetime="2023-11-19 11:00:00" binlog_file
重置 Binlog(慎用!從庫會斷開)RESET MASTER; → 從 mysql-bin.000001 重新開始
手動切換 BinlogFLUSH LOGS;

生產環境恢復 SOP

  1. 立即停止寫入:應用層切流量、設只讀 SET GLOBAL read_only=1;。
  2. 確認備份可用:最近一次全備文件完整、可解壓、校驗和。
  3. 定位誤操作時間點:業務反饋、Binlog 事件審計、binlog2sql 解析生成閃回 SQL。
  4. 分級恢復:全備恢復 → Binlog 增量回放 → 驗證核心業務數據。
  5. 驗證一致性:關鍵表行數、Checksum、業務抽樣核對。
  6. 事後複盤:根因分析、權限收斂、延遲從庫/只讀實例作為延遲鏡像兜底。

工具推薦

  • binlog2sql:解析 Binlog 生成回滾/重做 SQL,支援指定表/時間/位點,GitHub。
  • mysqlbinlog:官方工具,功能全但輸出原始,需配合 -v --base64-output=DECODE-ROWS 可讀。
  • xtrabackup + xbstream:物理熱備份,恢復速度快於邏輯備份,配合 Binlog 增量實現 PITR(時間點恢復)。
copyright © 萬盛學電腦網 all rights reserved