MySQL 二進制日誌記錄所有數據變更操作,是**時間點恢復**的核心依據。本文通過完整實戰案例,詳細演示如何啟用 Binlog、分析日誌事件、使用 mysqlbinlog 按位置/時間精準恢復、跳過錯誤操作(如誤 DELETE/DROP),構建企業級誤操作救援能力。
[mysqld]
log-bin = /var/log/mysql/mysql-bin # 日誌文件基名,建議獨立磁碟
binlog_format = ROW # ROW 格式:記錄行變更,最安全、支援精準恢復
expire_logs_days = 7 # 自動清理 7 天前日誌
max_binlog_size = 100M # 單文件大小上限
server-id = 1 # 主庫唯一 ID
重啟 MySQL:systemctl restart mysqld
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 156 |
+------------------+-------+
CREATE DATABASE test_recovery;
USE test_recovery;
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, val INT, data VARCHAR(20));
INSERT INTO t (val, data) VALUES (10, 'liang'), (20, 'jia'), (30, 'hui');
FLUSH LOGS; -- 產生新 binlog 文件 (mysql-bin.000002)
INSERT INTO t (val, data) VALUES (40, 'aaa'), (50, 'bbb'), (60, 'ccc');
DELETE FROM t WHERE id BETWEEN 4 AND 5; -- ⚠️ 誤刪除操作
INSERT INTO t (val, data) VALUES (70, 'ddd');
FLUSH LOGS; -- mysql-bin.000003
INSERT INTO t (val, data) VALUES (80, 'dddd'), (90, 'eeee');
DROP TABLE t; -- ⚠️ 誤刪表
FLUSH LOGS; -- mysql-bin.000004
# 導出文本分析
mysqlbinlog --base64-output=DECODE-ROWS -v /var/log/mysql/mysql-bin.000002 > /tmp/binlog_002.txt
mysqlbinlog --base64-output=DECODE-ROWS -v /var/log/mysql/mysql-bin.000003 > /tmp/binlog_003.txt
查看 /tmp/binlog_002.txt 關鍵片段:
# at 805
#240115 10:30:21 server id 1 end_log_pos 875 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1705285821/*!*/;
BEGIN
/*!*/;
# at 875
#240115 10:30:21 server id 1 end_log_pos 981 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1705285821/*!*/;
DELETE FROM `test_recovery`.`t` WHERE `id` BETWEEN 4 AND 5
/*!*/;
# at 981
#240115 10:30:21 server id 1 end_log_pos 1008 Xid = 15
COMMIT/*!*/;
關鍵位置:
875 (BEGIN)1008 (COMMIT)目標: 恢復所有數據,但**跳過** DELETE FROM t WHERE id BETWEEN 4 AND 5 與 DROP TABLE t。
mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -u root -p test_recovery
# 2a. 恢復 DELETE 之前的事件 (位置 4 ~ 875)
mysqlbinlog --stop-position=875 /var/log/mysql/mysql-bin.000002 | mysql -u root -p test_recovery
# 2b. 恢復 DELETE 之後的事件 (位置 1008 ~ 結尾)
mysqlbinlog --start-position=1008 /var/log/mysql/mysql-bin.000002 | mysql -u root -p test_recovery
# 分析第三個 binlog 找到 DROP TABLE 位置,假設為 574
mysqlbinlog --stop-position=574 /var/log/mysql/mysql-bin.000003 | mysql -u root -p test_recovery
mysql> SELECT * FROM test_recovery.t ORDER BY id;
+----+------+-------+
| id | val | data |
+----+------+-------+
| 1 | 10 | liang |
| 2 | 20 | jia |
| 3 | 30 | hui |
| 4 | 40 | aaa |
| 5 | 50 | bbb |
| 6 | 60 | ccc |
| 7 | 70 | ddd |
| 8 | 80 | dddd |
| 9 | 90 | eeee |
+----+------+-------+
9 rows in set (0.00 sec)
✅ 完美恢復,誤刪除的 id 4、5 及誤刪表後的數據全部找回。
# 恢復到 2024-01-15 10:30:00 之前(誤操作前)
mysqlbinlog --stop-datetime="2024-01-15 10:30:00" /var/log/mysql/mysql-bin.000002 | mysql -u root -p
# 恢復 2024-01-15 10:31:00 之後的操作
mysqlbinlog --start-datetime="2024-01-15 10:31:00" /var/log/mysql/mysql-bin.000002 | mysql -u root -p
⚠️ **時間點恢復精度受限於秒級**,同一秒內多筆交易無法精確區分,**生產環境強烈建議用位置恢復**。
| 參數 | 說明 | 典型用法 |
|---|---|---|
--start-position=N | 從位置 N 開始讀取(包含) | 跳過錯誤事務前的正常操作 |
--stop-position=N | 讀取到位置 N 停止(不包含) | 截止到錯誤事務開始前 |
--start-datetime="DT" | 從指定時間開始 | 大致時間點恢復 |
--stop-datetime="DT" | 讀取到指定時間停止 | 截止到誤操作前一秒 |
--base64-output=DECODE-ROWS -v | 解碼 ROW 格式為可讀 SQL | 分析 Binlog 內容 |
--database=db_name | 只輸出指定庫的事件 | 多庫環境過濾 |
SET GLOBAL read_only=ON)。mysqlbinlog -v 分析)。Binlog 是 MySQL 數據安全的「時光機」。掌握 mysqlbinlog --start-position/--stop-position 精準跳過錯誤事務,配合 FLUSH LOGS 切割日誌、binlog_format=ROW 記錄行級變更、定期全量備份做基線,**讓誤操作不再是災難,而是可控的恢復演練。**