MySQL 增量備份實戰：基於 Binlog 的每日增量 + 週度全備恢復流程完整指南

增量備份核心原理

利用 MySQL 二進制日誌記錄所有 DML/DDL 變更，定期 FLUSH LOGS 切換日誌檔案，備份新產生的 binlog 檔案即為增量備份。

前置需求

[mysqld]
log_bin = mysql-bin
binlog_format = ROW          -- 推薦 ROW，語句級不準確
expire_logs_days = 14        -- 自動清理舊日誌
max_binlog_size = 1G         -- 單檔上限
server_id = 1                -- 必須唯一
sync_binlog = 1              -- 每次提交刷盤 (核心業務)

備份策略：週日全備 + 每日增量

步驟 1：週日全備 (備份基準點)

#!/bin/bash
# full_backup.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H%M)
mysqldump --single-transaction --flush-logs --master-data=2 --all-databases \
  --routines --triggers --events \
  | gzip > "${BACKUP_DIR}/full_${DATE}.sql.gz"

# 記錄 binlog 位置 (輸出檔頭部註解)
# -- Position to start replication or point-in-time recovery from
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=154;

• --flush-logs：全備前切換新 binlog，確保增量從此檔開始
• --master-data=2：在輸出註解記錄 binlog 位置 (不加 CHANGE MASTER 語句)

步驟 2：每日增量備份 (切換 binlog + 複製檔案)

#!/bin/bash
# incr_backup.sh
BINLOG_DIR="/var/lib/mysql"
BACKUP_DIR="/backup/mysql/binlog"
mkdir -p "$BACKUP_DIR"

# 1. 切換 binlog (產生新檔，舊檔固化)
mysqladmin -uroot -p'password' flush-logs

# 2. 複製「上一個」binlog 檔案到備份目錄
# 邏輯：flush-logs 後，最新的是 .000024，需備份 .000023
LATEST=$(ls -1 ${BINLOG_DIR}/mysql-bin.* | tail -1)
PREV=$(ls -1 ${BINLOG_DIR}/mysql-bin.* | tail -2 | head -1)
cp "$PREV" "${BACKUP_DIR}/$(basename $PREV)_$(date +%F_%H%M)"

# 3. 同步複製 index 檔 (可選)
cp "${BINLOG_DIR}/mysql-bin.index" "${BACKUP_DIR}/mysql-bin.index_$(date +%F_%H%M)"

步驟 3：恢復流程 (以週三故障為例)

# 1. 還原週日全備
gunzip -c /backup/mysql/full_2024-01-07_0200.sql.gz | mysql -uroot -p

# 2. 依序重放增量 binlog
mysqlbinlog /backup/mysql/binlog/mysql-bin.000023_2024-01-08_0200 | mysql -uroot -p
mysqlbinlog /backup/mysql/binlog/mysql-bin.000024_2024-01-09_0200 | mysql -uroot -p
mysqlbinlog /backup/mysql/binlog/mysql-bin.000025_2024-01-10_0200 | mysql -uroot -p

# 3. 若故障發生在增量備份之間，可進一步重放當前活躍 binlog
mysqlbinlog /var/lib/mysql/mysql-bin.000026 | mysql -uroot -p

進階：時間點恢復 (PITR)

# 僅恢復到特定時間 (如誤刪表發生在 10:30)
mysqlbinlog --start-datetime="2024-01-10 02:00:00" \
            --stop-datetime="2024-01-10 10:30:00" \
            /var/lib/mysql/mysql-bin.000026 | mysql -uroot -p

# 或按位置
mysqlbinlog --start-position=154 --stop-position=123456 \
            /var/lib/mysql/mysql-bin.000026 | mysql -uroot -p

生產環境強化建議

• 異地備份：rsync -avz /backup/mysql/ remote:/backup/mysql/
• 校驗完整性：全備後 mysqlbinlog --verify-binlog-checksum、增量檔 sha256sum
• 自動化腳本：crontab + 鎖檔防重複 + 失敗告警 (郵件/企業微信/钉钉)
• 定期演練：每季在測試環境完整演練恢復流程，驗證 RTO/RPO
• GTID 模式 (5.6+)：啟用 gtid_mode=ON 簡化增量定位，無需手動記錄文件/位置

常見錯誤排查