mysqldump 核心參數速查

參數	作用	建議場景
--single-transaction	InnoDB 一致性快照，不鎖表	生產環境熱備份必加
--quick	逐行串流輸出，不緩存全表	大表導出必加
--hex-blob	二進位欄位十六進位導出	含 BLOB/BINARY/UUID 欄位
--routines	導出存儲過程/函數	完整遷移/備份
--triggers	導出觸發器（預設開啟）	完整遷移/備份
--master-data=2	輸出 CHANGE MASTER TO 註解（含 binlog 位置）	搭建從庫/時間點恢復
--set-gtid-purged=OFF	不輸出 GTID 資訊	異構遷移避免衝突
--no-data / -d	僅導出結構	Schema 遷移/審計
--no-create-info / -t	僅導出資料	資料遷移/同步

常用備份命令範例

1. 單庫完整備份（生產標準）

mysqldump -u backup_user -p --single-transaction --quick --hex-blob --routines --triggers \
  --master-data=2 db_name | gzip > /backup/db_name_$(date +%F_%H-%M).sql.gz

2. 多庫備份

mysqldump -u backup_user -p --databases db1 db2 db3 --single-transaction --quick | gzip > /backup/multi_$(date +%F).sql.gz

3. 全例備份（含系統庫）

mysqldump -u backup_user -p --all-databases --single-transaction --quick | gzip > /backup/full_$(date +%F).sql.gz

4. 單表備份

mysqldump -u backup_user -p db_name table_name --single-transaction --quick > /backup/table_name.sql

5. 只導結構 / 只導資料

# 結構
mysqldump -u root -p -d db_name > structure.sql
# 資料
mysqldump -u root -p -t db_name > data.sql

還原方案

方法一：mysql 客戶端管線（推薦）

# 解壓並導入
gunzip -c backup.sql.gz | mysql -u root -p db_name

# 或指定編碼
gunzip -c backup.sql.gz | mysql -u root -p --default-character-set=utf8mb4 db_name

方法二：SOURCE 指令（需進入 mysql 互動模式）

mysql -u root -p db_name
mysql> SOURCE /backup/backup.sql;

方法三：壓縮檔直接管線（節省磁碟 IO）

zcat backup.sql.gz | mysql -u root -p db_name

自動化備份腳本（生產就緒版）

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

# 配置區
DB_USER="backup_user"
DB_PASS="strong_password"
DB_HOST="127.0.0.1"
DB_PORT="3306"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=30
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
DATE=$(date +%F_%H-%M)
LOG_FILE="/var/log/mysql_backup.log"

# 要備份的資料庫（空字串=所有用戶庫，排除系統庫）
DATABASES=("app_db" "log_db" "report_db")

mkdir -p "$BACKUP_DIR"

exec > >(tee -a "$LOG_FILE") 2>&1

echo "=== Backup started at $(date) ==="

for DB in "${DATABASES[@]}"; do
    FILE="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
    echo "Backing up $DB -> $FILE"
    
    $MYSQLDUMP -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASS" \
        --single-transaction --quick --hex-blob --routines --triggers \
        --master-data=2 --set-gtid-purged=OFF \
        "$DB" | gzip > "$FILE"
    
    if [[ ${PIPESTATUS[0]} -eq 0 ]]; then
        SIZE=$(du -h "$FILE" | cut -f1)
        echo "SUCCESS: $DB ($SIZE)"
    else
        echo "FAILED: $DB"
        rm -f "$FILE"
    fi
done

# 清理舊備份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Old backups cleaned (>${RETENTION_DAYS} days)"

echo "=== Backup finished at $(date) ==="

部署排程

chmod +x /usr/local/bin/mysql_backup.sh
# crontab -e (root)
0 2 * * * /usr/local/bin/mysql_backup.sh  # 每日 02:00 執行

異地容災：同步到遠端儲存

rsync over SSH

rsync -avz --delete /backup/mysql/ user@backup-server:/remote/backup/mysql/

上傳到 S3/MinIO/OSS

aws s3 sync /backup/mysql/ s3://my-bucket/mysql-backups/ --storage-class GLACIER

加密備份

gpg --symmetric --cipher-algo AES256 backup.sql.gz

還原驗證演練（關鍵！）

1. 建立測試庫：CREATE DATABASE restore_test;
2. 還原：gunzip -c backup.sql.gz | mysql -u root -p restore_test
3. 核對：CHECKSUM TABLE、行數、關鍵業務查詢結果。
4. 記錄 RTO（恢復時間目標），優化流程。

總結

MySQL 備份鐵律：**單一事務熱備份 + 即時壓縮 + 自動化排程 + 異地多副本 + 定期演練還原**。掌握 mysqldump 核心參數組合，編寫健壯備份腳本，建立「備份即代碼」版本化管理，才是資料安全的真正保障。