前提條件
- **啟用 binlog：** `log-bin=mysql-bin` (my.cnf)。
- **binlog_format=ROW** (推薦) 或 `MIXED`，`STATEMENT` 恢復風險大。
- **備份基準點：** 全量備份時間 (如每日 02:00 `mysqldump --single-transaction --master-data=2`)。
核心工具：mysqlbinlog
# 查看當前 binlog 文件
mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER STATUS\G;
# 解析 binlog 內容 (文本輸出)
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000005 > /tmp/binlog.sql
恢復策略 1：指定時間點恢復 (PITR - Point In Time Recovery)
場景：今天 10:00 誤執行 `DROP TABLE` / `DELETE 無 WHERE`，需恢復到 09:59:59
# 1. 恢復昨夜全量備份
mysql -uroot -p < full_backup_20231015_0200.sql
# 2. 重放 binlog 到 09:59:59
mysqlbinlog --stop-datetime="2023-10-15 09:59:59" \
/var/lib/mysql/mysql-bin.000005 | mysql -uroot -p
# 3. (可選) 跳過錯誤事務，繼續重放後續正常業務
mysqlbinlog --start-datetime="2023-10-15 10:01:00" \
/var/lib/mysql/mysql-bin.000005 | mysql -uroot -p
恢復策略 2：指定位置恢復 (精準到事務)
原理：`--stop-position` / `--start-position` 對應 binlog 事件的 `end_log_pos`
# 1. 導出疑似時間段 binlog 供人工分析
mysqlbinlog --start-datetime="2023-10-15 09:55:00" --stop-datetime="2023-10-15 10:05:00" \
/var/lib/mysql/mysql-bin.000005 > /tmp/analyze.sql
# 2. 用編輯器打開 /tmp/analyze.sql，搜尋 DROP/DELETE/UPDATE 無 WHERE 等危險語句
# 記錄其前一個事件的 end_log_pos (如 368312) 和危險事件結束位置 (如 368315)
# 3. 恢復到危險事件前
mysqlbinlog --stop-position="368312" /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p
# 4. 跳過危險事件，恢復後續
mysqlbinlog --start-position="368315" /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p
關鍵參數速查
| 參數 | 含義 | 典型用法 |
|---|
| --stop-datetime | 恢復截止時間 (含) | PITR 截止誤操作前一秒 |
| --start-datetime | 恢復起始時間 (含) | 跳過誤操作後繼續 |
| --stop-position | 恢復截止 position (不含) | 精準跳過某事務 |
| --start-position | 恢復起始 position (含) | 從某事務後繼續 |
| --base64-output=DECODE-ROWS -v | ROW 格式解碼為可讀 SQL | 人工分析必加 |
| --database=db_name | 只解析指定庫事件 | 加速分析、減少輸出 |
生產級 PITR 演練流程 (季度必演)
- **模擬故障：** 測試庫模擬誤 `TRUNCATE TABLE core_table`，記錄精確時間。
- **執行恢復：** 按上述策略 2 (位置恢復) 恢復至誤操作前。
- **驗證數據：** 核對核心表行數、關鍵業務字段、關聯完整性。
- **記錄耗時：** 全量備份恢復時間 + binlog 重放時間 = **RTO (恢復時間目標)**。
- **優化 RTO：** 並行重放 (`mysqlbinlog | split -l 10000 | xargs -P 4 mysql`)、物理備份 (xtrabackup) 替代邏輯備份。
避坑指南
- **GTID 模式下：** `mysqlbinlog` 輸出包含 `SET @@SESSION.GTID_NEXT=...`，直接管道導入可能報錯 `GTID_NEXT` 未清理。解決：`--skip-gtids` 或目標庫 `SET GTID_NEXT=AUTOMATIC`。
- **大事務重放極慢：** 單事務 > 1GB 導致重放卡頓。預防：應用層拆分大事務、參數 `max_binlog_stmt_size` 限制。
- **字符集不一致：** binlog 記錄的字符集與恢復目標庫不一致導致亂碼。確保 `character_set_client` 一致。
- **權限：** 需 `FILE` 權限讀取 binlog 文件，或用 `mysqlbinlog --read-from-remote-server` 遠程讀取。
