mysqldump 完整參數指南：從基礎備份到生產級遷移的最佳實踐

核心語法與三大運作模式

# 1. 備份單庫/多庫
mysqldump [options] --databases db1 db2 db3 > backup.sql

# 2. 備份單庫內指定表
mysqldump [options] db_name table1 table2 > backup.sql

# 3. 備份所有使用者庫 (含 mysql 系統庫)
mysqldump [options] --all-databases > full_backup.sql

關鍵參數分類詳解

生產級備份命令模板

場景一：全量備份 (每日 02:00，保留 7 天)

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

BACKUP_DIR="/backup/mysql/full"
RETENTION_DAYS=7
MYSQL_OPTS="--defaults-file=/etc/mysql/backup.cnf"  # 隔離備份帳號密碼

mkdir -p "$BACKUP_DIR"
FILENAME="${BACKUP_DIR}/full_$(date +%F_%H-%M-%S).sql.zst"

mysqldump $MYSQL_OPTS \
  --single-transaction \
  --routines --triggers --events \
  --hex-blob \
  --master-data=2 \
  --flush-logs \
  --all-databases \
  | zstd -T0 -19 -q -o "$FILENAME"  # Zstandard 極速高壓縮

# 刪除舊備份
find "$BACKUP_DIR" -name "full_*.sql.zst" -mtime +$RETENTION_DAYS -delete

# 校驗備份完整性
zstd -t "$FILENAME" && echo "$(date) Backup verified: $FILENAME" || exit 1

場景二：大表並行導出導入 (TB 級)

# 導出：每表獨立檔案，支援並行
mysqldump $MYSQL_OPTS \
  --tab=/backup/mysql/tab \
  --fields-terminated-by=',' \
  --fields-enclosed-by='\"' \
  --fields-escaped-by='\\' \
  --lines-terminated-by='\n' \
  --no-create-info \
  --quick \
  --single-transaction \
  large_db huge_table1 huge_table2

# 並行導入 (8 併發)
cd /backup/mysql/tab
ls *.txt | parallel -j 8 "mysqlimport $MYSQL_OPTS --local --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' large_db {}"

# 結構單獨導入
mysqldump $MYSQL_OPTS --no-data --routines --triggers --events large_db > schema.sql
mysql $MYSQL_OPTS large_db < schema.sql

場景三：增量備份 (Binlog + 定期全量)

# 1. 全量備份時 --flush-logs --master-data=2 記錄 Binlog 位置
# 2. 持續複製 Binlog (mysqlbinlog --raw --stop-never)
mysqlbinlog --raw --stop-never --host=db-master --user=repl --password \
  --result-file=/backup/mysql/binlog/ mysql-bin.000123

# 3. 恢復：全量 + Binlog 重放
mysql $MYSQL_OPTS < full_backup.sql
mysqlbinlog /backup/mysql/binlog/mysql-bin.000123 \
  --start-position=12345 \
  | mysql $MYSQL_OPTS

場景四：跨版本/跨引擎遷移 (MySQL 5.7 → 8.0)

# 1. 相容性檢查
mysqlcheck $MYSQL_OPTS --all-databases --check-upgrade

# 2. 導出 (排除 mysql 系統庫，避免權限表衝突)
mysqldump $MYSQL_OPTS \
  --single-transaction \
  --routines --triggers --events \
  --hex-blob \
  --column-statistics=0 \  # 8.0 客戶端備份 5.7 需關閉
  --databases $(mysql $MYSQL_OPTS -NBe "SHOW DATABASES LIKE 'app_%'") \
  > migration.sql

# 3. 目標庫預建帳號/權限 (勿導入 mysql.user)
# 4. 匯入前檢查
grep -i "DEFINER\|sql_mode\|TYPE=" migration.sql | head -20  # 手動修正

# 5. 匯入
mysql $MYSQL_OPTS < migration.sql

常見錯誤與對策

備份驗證與演練 (不可省略)

1. 校驗和：sha256sum backup.sql.zst 記錄至備份清單
2. 解壓測試：zstd -t backup.sql.zst 每日自動執行
3. 還原演練：每季度至少 1 次**在測試環境**完整還原驗證 RTO/RPO
4. 資料抽樣比對：SELECT COUNT(*), CHECKSUM TABLE 關鍵表源端 vs 目標端