mysqldump 參數深度解析與自動化備份腳本實戰 (含邏輯/物理/熱備份三模式)

mysqldump 參數超 50 個，掌握核心 10 個即可應對 90% 場景。本文逐一解析高頻參數，並提供三種備份模式 (邏輯/熱拷貝/物理) 的生產級腳本。

核心參數詳解 (按功能分組)

三種備份模式腳本

模式一：邏輯備份 (mysqldump) — 通用、跨版本、易遷移

mysqldump --opt --single-transaction --master-data=2 \
  --routines --triggers --events --hex-blob \
  --all-databases | gzip > full_$(date +%F).sql.gz

模式二：熱拷貝 — 停服務拷貝 datadir (MyISAM/靜態庫)

#!/bin/bash
systemctl stop mysql
tar czf /backup/mysql_physical_$(date +%F).tar.gz /var/lib/mysql
systemctl start mysql

⚠️ 需停機，僅適用維護窗口、純 MyISAM/只讀實例。

模式三：mysqlhotcopy — 僅 MyISAM/ISAM、本地文件系統、需 Perl

mysqlhotcopy -u root -p db_name /backup/mysqlhotcopy_$(date +%F)
tar czf /backup/mysqlhotcopy_$(date +%F).tar.gz /backup/mysqlhotcopy_$(date +%F)

⚠️ **已棄用 (MySQL 5.7 移除)**，InnoDB 不支持，勿用於新架構。

自動化腳本模板 (邏輯全量 + 保留策略)

#!/bin/bash
# /usr/local/bin/backup_mysql_daily.sh
set -euo pipefail

DB_USER="backup"
DB_PASS_FILE="/root/.my_backup.cnf"  # [client] password=xxx
BACKUP_DIR="/backup/mysql"
RETENTION=5

mkdir -p "${BACKUP_DIR}"
FILE="${BACKUP_DIR}/full_$(date +%F).sql.gz"

mysqldump --defaults-extra-file="${DB_PASS_FILE}" \
  --opt --single-transaction --master-data=2 \
  --routines --triggers --events --hex-blob \
  --all-databases | gzip > "${FILE}"

if [[ ${PIPESTATUS[0]} -eq 0 ]]; then
  echo "[$(date)] SUCCESS: ${FILE}"
  find "${BACKUP_DIR}" -name "full_*.sql.gz" -mtime +${RETENTION} -delete
else
  echo "[$(date)] FAILED" >&2
  exit 1
fi

定時任務

# crontab -e (root)
0 2 * * * /usr/local/bin/backup_mysql_daily.sh >> /var/log/mysql_backup.log 2>&1

驗證與告警

1. 文件大小突變告警：stat -c%s file.gz 對比歷史均值 ±50%。
2. 校驗和記錄：sha256sum file.gz >> checksums.log。
3. 月度恢復演練：腳本化恢復到測試庫，跑核心業務 SQL 驗證。

**核心原則**：邏輯備份用 --opt --single-transaction --master-data=2，物理備份用 XtraBackup，腳本化、校驗、演練、異地存儲，四件套缺一不可。