MySQL二進制日志時間點恢復 (PITR) 實戰指南

前置條件：啟用二進制日志

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
sync_binlog=1
expire_logs_days=7

確認日志路徑：SHOW VARIABLES LIKE 'log_bin_basename';

核心工具：mysqlbinlog

將二進制日志轉為可執行 SQL 文本，支持按時間/位置過濾。

恢復策略一：按時間點恢復 (MySQL 4.1.4+)

場景：今日 10:00 誤執行 DROP TABLE，需恢復至 09:59:59

# 1. 恢復昨晚全量備份
mysql -u root -p db_name < full_backup_20230419.sql

# 2. 重放 Binlog 至錯誤前一秒
mysqlbinlog --stop-datetime="2023-04-20 09:59:59" \
    /var/log/mysql/mysql-bin.000123 | mysql -u root -p db_name

# 3. (可選) 重放錯誤後的正常業務
mysqlbinlog --start-datetime="2023-04-20 10:01:00" \
    /var/log/mysql/mysql-bin.000123 | mysql -u root -p db_name

恢復策略二：按位置精準恢復 (生產環境強推)

時間戳精度秒級，並發事務同一秒內無法區分。位置號 (log_pos) 精確到事件級。

步驟 1：導出可疑時段日志供人工審計

mysqlbinlog --start-datetime="2023-04-20 09:55:00" \
    --stop-datetime="2023-04-20 10:05:00" \
    /var/log/mysql/mysql-bin.000123 > /tmp/review.sql

步驟 2：文本編輯器定位破壞性語句

# 搜索 DROP/DELETE/TRUNCATE
grep -n -i "drop table\|delete from\|truncate table" /tmp/review.sql

# 記錄目標事件前後的 log_pos
# 例：DROP TABLE 位於 log_pos=368312，下一事件 log_pos=368315

步驟 3：分段重放跳過破壞點

# 重放至破壞事件前
mysqlbinlog --stop-position="368312" \
    /var/log/mysql/mysql-bin.000123 | mysql -u root -p db_name

# 跳過破壞事件，從後續事件重放
mysqlbinlog --start-position="368315" \
    /var/log/mysql/mysql-bin.000123 | mysql -u root -p db_name

多日志文件有序合併 (關鍵細節)

ls -t -r 字符串排序會導致 bin.9 排在 bin.10 之後。必須按數字後綴排序：

# Perl 一行命令
ls /var/log/mysql/mysql-bin.[0-9]* | \
perl -e 'print sort { ($a=~/(\d+)$/)[0] <=> ($b=~/(\d+)$/)[0] } <>' | \
xargs mysqlbinlog | mysql --one-database db_name

特殊場景處理

恢復單庫 (--one-database)

mysqlbinlog --database=target_db mysql-bin.000123 | mysql -u root -p target_db

注意：--database 過濾基於 USE db 语句，跨庫操作可能遺漏。ROW 格式 Binlog 不記錄 USE，需配合 --rewrite-db 或全量重放後手動清理。

文本日志替代方案

若啟用 --log-isam 生成文本格式變更日志，無需 mysqlbinlog 轉換：

cat /var/log/mysql/update.log | mysql --one-database db_name

自動化 PITR 腳本框架

#!/bin/bash
# pitr_restore.sh   [binlog_dir]
set -euo pipefail

BACKUP_FILE=$1
TARGET_DT=$2
BINLOG_DIR=${3:-/var/log/mysql}
DB_NAME=$(grep "CREATE DATABASE" $BACKUP_FILE | head -1 | awk '{print $3}' | tr -d ';')

# 1. 恢復全量備份
mysql -u root -p"$MYSQL_PWD" $DB_NAME < $BACKUP_FILE

# 2. 獲取備份完成時的 Binlog 坐標 (假設備份腳本記錄)
# MASTER_LOG_FILE=mysql-bin.000123, MASTER_LOG_POS=456

# 3. 重放增量日志
mysqlbinlog --start-position=456 --stop-datetime="$TARGET_DT" \
    $BINLOG_DIR/mysql-bin.[0-9]* | mysql -u root -p"$MYSQL_PWD" $DB_NAME

echo "PITR completed to $TARGET_DT"

最佳實踐清單

1. 備份腳本必須記錄 SHOW MASTER STATUS 輸出 (文件名+位置)
2. Binlog 格式強制 ROW，避免非確定性函數導致主從不一致
3. 定期演練恢復流程 (月度)，驗證 RTO/RPO 指標
4. 異地備份 Binlog (rsync/S3)，防單點存儲故障
5. 監控 Seconds_Behind_Master 與磁盤空間，防 Binlog 積壓導致磁盤滿