MySQL 自動化備份實戰：邏輯備份 + 二進制日志增量備份 + 定時任務

前言

生產環境數據無價，備份策略必須「多層次、可驗證、可自動化」。本文提供成熟的 Shell 腳本方案：每周全量邏輯備份 + 每日兩次二進制日志增量備份，配合 crontab 定時執行。

一、備份策略設計

二、核心腳本

2.1 全量備份腳本 full_backup.sh

#!/bin/bash
# 配置區
DB_USER="root"
DB_PASS="your_password"
DB_NAME="your_database"
BACKUP_DIR="/backup/mysql/full"
DATE=$(date +%Y_%m_%d_%H%M%S)
FILE_PREFIX="${BACKUP_DIR}/${DATE}"

# 執行備份
mysqldump -u${DB_USER} -p${DB_PASS} \
  --single-transaction \
  --flush-logs \
  --master-data=2 \
  --routines --triggers --events \
  ${DB_NAME} > ${FILE_PREFIX}.sql

# 壓縮並清理
gzip ${FILE_PREFIX}.sql
# 可選：刪除 28 天前備份
find ${BACKUP_DIR} -name "*.gz" -mtime +28 -delete

echo "[$(date)] Full backup completed: ${FILE_PREFIX}.sql.gz"

關鍵參數解析：

• --single-transaction：InnoDB 熱備不鎖表，保證一致性視圖
• --flush-logs：切換 binlog，配合 --master-data=2 記錄 binlog 位點（註釋形式）
• --master-data=2：輸出 CHANGE MASTER TO 註釋，便於從庫搭建

2.2 二進制日志增量備份腳本 incr_backup.sh

#!/bin/bash
# 前置條件：my.cnf 已啟用 log_bin，且 binlog 格式建議 ROW 或 MIXED
MYSQL_USER="root"
MYSQL_PASS="your_password"
MYSQL_BIN_DIR="/var/lib/mysql"          # datadir，binlog 所在目錄
BACKUP_DIR="/backup/mysql/incremental"
DATE=$(date +%Y_%m_%d_%H%M%S)

# 1. 切換 binlog，產生新文件
mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} flush-logs

# 2. 找出上一個 binlog 文件（即剛切換前的文件）
cd ${MYSQL_BIN_DIR}
# 假設 binlog 命名為 mysql-bin.000001 格式
LAST_BINLOG=$(ls -1 mysql-bin.* | sort -V | tail -2 | head -1)

if [[ -f "${LAST_BINLOG}" ]]; then
  cp ${LAST_BINLOG} ${BACKUP_DIR}/
  gzip ${BACKUP_DIR}/${LAST_BINLOG}
  echo "[$(date)] Incremental backup: ${LAST_BINLOG}.gz"
else
  echo "[$(date)] ERROR: No binlog found to backup" >&2
  exit 1
fi

# 3. 清理 14 天前增量備份
find ${BACKUP_DIR} -name "*.gz" -mtime +14 -delete

注意：

• 需確保 BACKUP_DIR 存在且 MySQL 進程有讀權限
• 腳本假設 binlog 文件命名規則標準，異常命名需調整正則
• 生產環境建議加入校驗和（md5sum）並上傳異地存儲

三、定時任務配置

# 編輯 crontab（root 用戶）
crontab -e

# 全量備份：每周六 02:00
0 2 * * 6 /backup/scripts/full_backup.sh >> /var/log/mysql_full_backup.log 2>&1

# 增量備份：每日 12:30 和 23:30
30 12,23 * * * /backup/scripts/incr_backup.sh >> /var/log/mysql_incr_backup.log 2>&1

四、恢復演練（關鍵！）

備份未經恢復驗證等於廢紙。建議每季度演練一次：

1. 準備乾淨 MySQL 實例
2. 恢復最近全量備份：gunzip -c full_backup.sql.gz | mysql -u root -p
3. 按順序應用增量 binlog：mysqlbinlog binlog.00000X.gz | mysql -u root -p
4. 驗證關鍵表行數、業務關鍵指標

五、進階建議

• 使用 xtrabackup 替代 mysqldump 進行物理熱備（大庫 > 500GB 推薦）
• 啟用 binlog_row_image=MINIMAL 減少 binlog 體積
• 備份文件加密（openssl 或 gpg）並異地存儲（OSS/S3/異地機房）
• 監控備份成功率、耗時、文件大小，異常告警