MySQL mysqldump 備份與還原完整指南：導出導入參數詳解與實戰案例

MySQL 官方備份利器：mysqldump 參數全解析、導出導入實戰與自動化腳本

mysqldump 是 MySQL 官方提供的邏輯備份工具，支援全庫/單表/結構/數據靈活導出，產出標準 SQL 腳本，跨版本、跨平台遷移通用。本文系統整理核心參數、常用場景、還原方法與生產級自動化備份策略。

核心語法

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name1 [db_name2 ...]
mysqldump [options] --all-databases

關鍵參數分類解析

常用場景實戰指令

1. 全庫熱備份 (InnoDB 生產標準)

mysqldump -u backup_user -p \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --hex-blob \
  --routines --triggers --events \
  --databases app_db > /backup/app_db_$(date +%F_%H%M).sql

2. 單表結構遷移

mysqldump -u user -p -d --add-drop-table app_db users > users_schema.sql

3. 大表分批導出 (配合 where)

# 每 100 萬行一批
mysqldump -u user -p --quick --where="id BETWEEN 1 AND 1000000" app_db big_table > part1.sql
mysqldump -u user -p --quick --where="id BETWEEN 1000001 AND 2000000" app_db big_table > part2.sql

4. 遠程壓縮傳輸直入目標庫

mysqldump -h src_host -u user -p --single-transaction --compress app_db | \
  mysql -h dst_host -u user -p app_db

還原/導入方法

1. SQL 檔案導入：

   mysql -u root -p target_db < backup.sql
   # 或進入 mysql 客戶端
   SOURCE /path/backup.sql;

2. 壓縮檔直接導入：

   gunzip < backup.sql.gz | mysql -u root -p target_db

3. 多線程並行導入 (大數據量)：

   # 需將備份檔拆分為多個表檔案
   sed -n '/^-- Table structure for table/,/^-- Table structure for table/p' backup.sql | split -l 10000 - part_
   # 並行導入
   ls part_* | xargs -P 8 -I {} sh -c 'mysql -u root -p target_db < {}'

自動化備份腳本 (cron 每日 02:00 執行)

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

BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=14
DB_USER="backup_user"
DB_PASS="$(cat /etc/mysql/backup.pass)"  # 密碼文件權限 600
DATABASES="app_db log_db"

mkdir -p "$BACKUP_DIR"
DATE=$(date +%F_%H%M)

for DB in $DATABASES; do
    FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    mysqldump -u "$DB_USER" -p"$DB_PASS" \
        --single-transaction --quick --hex-blob \
        --routines --triggers --events \
        --master-data=2 --flush-logs \
        --databases "$DB" | gzip > "$FILE"
    
    # 驗證備份完整性
    if ! gzip -t "$FILE"; then
        echo "ERROR: Backup corrupted: $FILE" | mail -s "MySQL Backup Failed" admin@example.com
        exit 1
    fi
done

# 清理舊備份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $(ls -lh $BACKUP_DIR/*_${DATE}.sql.gz)"

觸發器/存儲過程導出陷阱

現象：預設 --triggers 導出觸發器，但 --routines 需顯式指定。
重複導入錯誤：ERROR 1235: multiple triggers with same action time and event for one table
解決：導出時加 --triggers=false (僅導出結構/數據)，或目標庫先 DROP TRIGGER。

備份驗證鐵律

1. 定期演練恢復：月度在測試環境完整恢復，驗證數據一致性、應用可用性
2. 校驗和比對：pt-table-checksum 主從/備份一致性驗證
3. 關鍵指標：RPO (備份頻率)、RTO (恢復耗時)、備份成功率 100%

mysqldump 雖非物理備份最快，但通用性最強、人工可讀、跨版本遷移無障礙。配合 --single-transaction、--master-data=2、壓縮、校驗、演練，構建企業級備份體系。