mysqldump 全量+增量混合備份體系：Binlog 刷新、增量抽取、自動清理、異地傳輸完整實現

生產級備份策略：**每周全量 + 每日增量 (Binlog)**，平衡恢復時間 (RTO) 與存儲成本。本文提供經生產驗證的完整腳本集，含全量備份、Binlog 增量抽取、自動清理、權限隔離、異地傳輸接口。

架構設計

• 全量備份：周日 03:00，mysqldump --single-transaction --master-data=2 --flush-logs --delete-master-logs，獲取一致性快照 + 截斷舊 Binlog。
• 增量備份：周一~六 03:00，mysqladmin flush-logs 產生新 Binlog，複製除最新外的所有 Binlog 到增量目錄。
• 恢復流程：最近全量 + 其後所有增量 Binlog 依次重放。

前置配置

1. 開啟 Binlog (/etc/my.cnf)：
   [mysqld]
log-bin=mysql-bin
expire_logs_days=0 # 由腳本控制清理，避免自動過期
2. 權限隔離：創建備份專用賬號，僅需 RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, SELECT, SHOW VIEW, EVENT, TRIGGER 權限。
3. 免密配置：/root/.my.cnf (chmod 600)
   [mysqladmin]
user=backup
password=******
[mysqldump]
user=backup
password=******

全量備份腳本 (full_backup.sh)

#!/bin/bash
set -euo pipefail

BAK_DIR="/backup/mysql"
LOG_FILE="${BAK_DIR}/mysql_bak.log"
DATE=$(date +%Y%m%d)
DUMP_FILE="${BAK_DIR}/${DATE}.sql"
GZ_FILE="${BAK_DIR}/${DATE}.sql.tgz"

log() { echo "[$(date '+%F %T')] $*" | tee -a "${LOG_FILE}"; }

log "=== 開始全量備份 ==="
cd "${BAK_DIR}"

mysqldump --quick --all-databases --flush-logs --delete-master-logs --lock-all-tables > "${DUMP_FILE}"
log "Dump 完成"

tar czf "${GZ_FILE}" "${DUMP_FILE}" >> "${LOG_FILE}" 2>&1
log "壓縮完成: ${GZ_FILE} ($(du -h ${GZ_FILE} | cut -f1))"

rm -f "${DUMP_FILE}"

# 清理增量目錄 (全量後增量失效)
rm -f "${BAK_DIR}/daily"/*

log "=== 全量備份結束 ==="
ls -lh "${BAK_DIR}" | tee -a "${LOG_FILE}"
echo "請手動/腳本將 ${GZ_FILE} 傳送至異地存儲！" | tee -a "${LOG_FILE}"

增量備份腳本 (incr_backup.sh)

#!/bin/bash
set -euo pipefail

DATADIR="/var/lib/mysql"
BAKDIR="/backup/mysql/daily"
HOSTNAME=$(uname -n)  # Binlog 前綴通常為 hostname

# 1. 刷新 Binlog，生成新文件
mysqladmin flush-logs

# 2. 讀取索引文件獲取所有 Binlog 列表
cd "${DATADIR}"
FILELIST=$(cat "${HOSTNAME}-bin.index")

# 3. 複製除最新外的所有文件
COUNTER=0
for f in ${FILELIST}; do COUNTER=$((COUNTER+1)); done

NEXT=0
for f in ${FILELIST}; do
  NEXT=$((NEXT+1))
  BASE=$(basename "${f}")
  if [[ ${NEXT} -eq ${COUNTER} ]]; then
    echo "Skip latest: ${BASE}"
    continue
  fi
  DEST="${BAKDIR}/${BASE}"
  if [[ -e "${DEST}" ]]; then
    echo "Skip exist: ${BASE}"
  else
    cp "${f}" "${DEST}"
    echo "Copied: ${BASE}"
  fi
done

echo "[$(date)] Incremental backup done."

定時任務配置

# crontab -e (root)
# 全量：每周日 03:00
0 3 * * 0 /root/full_backup.sh

# 增量：周一~六 03:00
0 3 * * 1-6 /root/incr_backup.sh

異地傳輸接口 (需自行實現)

# 在 full_backup.sh 末尾添加
# scp/rsync/s3cmd/ossutil 等
rsync -avz --progress "${GZ_FILE}" backup_user@offsite_server:/offsite/mysql/
# 或
aws s3 cp "${GZ_FILE}" s3://my-backup-bucket/mysql/ --storage-class GLACIER

恢復演練腳本 (restore_drill.sh)

#!/bin/bash
# 1. 解壓全量
tar xzf full_20240107.sql.tgz
# 2. 恢復全量
mysql -u root -p < full_20240107.sql
# 3. 依次重放增量 Binlog
mysqlbinlog daily/mysql-bin.000001 daily/mysql-bin.000002 | mysql -u root -p
# 4. 驗證核心表行數、業務功能

關鍵細節避坑

• --delete-master-logs **僅在全量備份成功後** 執行，否則丟失增量鏈。
• 增量腳本**檢測文件是否已存在**，支持多次運行冪等。
• Binlog 索引文件 (host-bin.index) 記錄順序即時間順序，**勿手動修改/排序**。
• expire_logs_days=0 交給腳本控制清理，避免 MySQL 自動刪除未備份的 Binlog。
• **權限最小化**：備份賬號**無 DROP/DDL 權限**，杜絕誤操作風險。

總結

1. 全量+增量混合策略是**中小規模 MySQL (百 GB 以內) 最佳性價比**方案。
2. 腳本核心：**flush-logs 切割、index 讀取、排除最新、冪等複製、全量後清增量**。
3. **異地傳輸、校驗和、月度恢復演練** 必須納入體系，否則備份僅是心理安慰。