mysqldump 備份 MySQL 完整指南：從手動到自動化、增量與異地容災

為什麼選擇 mysqldump？

• 官方原生工具、無額外依賴、跨平台支援。
• 邏輯備份（SQL 文本），可視、可編輯、跨版本遷移相容性佳。
• 支援單表、單庫、全例、僅結構、僅資料、壓縮、事務一致性等靈活選項。
• 適合中小型資料庫（< 50GB），大型庫建議搭配 XtraBackup 物理熱備。

一、核心參數速查表

二、常用備份場景指令範例

1. 全例備份（含結構、資料、常規物件、Binlog 位點）

mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --set-gtid-purged=OFF \
  > full_backup_$(date +%F_%H-%M-%S).sql

2. 單庫備份

mysqldump -u root -p \
  --databases mydb \
  --single-transaction --master-data=2 \
  --routines --triggers --events \
  > mydb_$(date +%F).sql

3. 單表備份

mysqldump -u root -p \
  mydb --tables users orders \
  --single-transaction \
  > mydb_users_orders_$(date +%F).sql

4. 僅結構備份（Schema 遷移、代碼審查）

mysqldump -u root -p \
  --databases mydb \
  --no-data \
  --routines --triggers --events \
  > mydb_schema_$(date +%F).sql

5. 僅資料備份（資料遷移、測試數據製造）

mysqldump -u root -p \
  --databases mydb \
  --no-create-info \
  --single-transaction \
  > mydb_data_$(date +%F).sql

6. 壓縮備份（節省 70-90% 空間）

# gzip 即時壓縮
mysqldump -u root -p --all-databases --single-transaction | gzip > full_$(date +%F).sql.gz

# 解壓還原
gunzip -c full_2024-01-15.sql.gz | mysql -u root -p

三、還原操作指南

1. 全量還原

mysql -u root -p < full_backup_2024-01-15.sql

2. 指定庫還原（備份檔含多庫）

mysql -u root -p -D target_db < mydb_backup.sql

3. 異機還原（生產 → 測試/開發）

# 方法一：管道直傳 (需雙向網路通暢)
mysqldump -h prod_host -u root -p --databases mydb --single-transaction | mysql -h test_host -u root -p -D mydb

# 方法二：備份檔傳輸後還原
scp mydb_backup.sql test_host:/tmp/
ssh test_host "mysql -u root -p -D mydb < /tmp/mydb_backup.sql"

四、自動化備份腳本（生產級）

備份腳本 /usr/local/bin/mysql_backup.sh

#!/bin/bash
set -euo pipefail

# 配置區
MYSQL_USER="backup_user"
MYSQL_PASS="strong_password"
MYSQL_HOST="127.0.0.1"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
COMPRESS="gzip"
LOG_FILE="/var/log/mysql_backup.log"

# 確保目錄存在
mkdir -p "$BACKUP_DIR"

# 日期變數
DATE=$(date +%F_%H-%M-%S)
FILENAME="full_backup_${DATE}.sql"

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

log "=== 開始備份 ==="

# 執行備份
if mysqldump -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
    --all-databases \
    --single-transaction \
    --master-data=2 \
    --routines --triggers --events \
    --hex-blob \
    --set-gtid-purged=OFF \
    > "${BACKUP_DIR}/${FILENAME}" 2>>"$LOG_FILE"; then
    
    log "備份檔案生成: ${FILENAME} ($(du -h "${BACKUP_DIR}/${FILENAME}" | cut -f1))"
    
    # 壓縮
    if [[ "$COMPRESS" == "gzip" ]]; then
        gzip "${BACKUP_DIR}/${FILENAME}"
        log "壓縮完成: ${FILENAME}.gz ($(du -h "${BACKUP_DIR}/${FILENAME}.gz" | cut -f1))"
        FILENAME="${FILENAME}.gz"
    fi
    
    # 清理舊備份
    find "$BACKUP_DIR" -name "full_backup_*.sql*" -mtime +$RETENTION_DAYS -delete
    log "清理 ${RETENTION_DAYS} 天前備份完成"
    
    log "=== 備份成功 ==="
else
    log "!!! 備份失敗，請檢查日誌 !!!"
    exit 1
fi

chmod +x /usr/local/bin/mysql_backup.sh

Cron 定時任務（每天凌晨 2:30）

crontab -e
# 加入：
30 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup_cron.log 2>&1

五、增量備份策略（Binlog 基準）

前提：啟用 log_bin、binlog_format=ROW、expire_logs_days 足夠長。

1. 全量基準：每週日 02:30 執行全量備份，記錄 Binlog 位點（--master-data=2 輸出註解）。
2. 增量收集：每日 03:00 執行 mysqlbinlog --start-position=XXX --stop-position=YYY 或 FLUSH LOGS 切割後複製新 Binlog。
3. 自動化工具：mysqlbinlog --read-from-remote-server --raw --stop-never 即時串流備份 Binlog 至備份機。

六、進階備份架構：主從 + 從庫備份

黃金法則：備份不影響主庫效能

1. 架構：一主多從，指定一從庫專職備份（--single-transaction 不鎖表、無主庫壓力）。
2. 從庫配置：log_slave_updates=ON、read_only=ON、skip_slave_start（備份前手動啟動同步）。
3. 流程：停止從庫 SQL 線程 → 備份 → 啟動 SQL 線程追趕主庫。

七、異地容災與驗證

• 異地同步：rsync -avz /backup/mysql/ dr-host:/backup/mysql/ 或 S3/MinIO/OSS 上傳。
• 定期演練：每季度隨機抽取備份在測試環境全量還原、數據核對、應用驗證。
• RPO/RTO 量化：RPO = Binlog 同步間隔（秒級），RTO = 還原時間（依資料量、磁碟速度估算）。

八、常見坑與避坑指南

總結

mysqldump 是 MySQL 邏輯備份基石，**單一交易模式 + Binlog 位點 + 壓縮 + 自動化腳本 + 異地同步 + 季度演練** 構成最小可用備份體系。超過 50GB 單庫、極低 RTO 需求、或需即時增量，請引入 XtraBackup 物理熱備或 MyDumper 並行邏輯備份。記住：**備份的價值在於可還原，未驗證的備份等於無備份。**