方法一：直接複製資料檔 (物理冷備份)

適用於 MyISAM、關機維護窗口、或需極速恢復的場景。

# 1. 刷盤並鎖表 (僅對 MyISAM 有效鎖定，InnoDB 需配合 --single-transaction)
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

# 2. 打包資料目錄
tar zcvf /backup/mysql_$(date +%F).tar.gz /var/lib/mysql

# 3. 解鎖
mysql -u root -p -e "UNLOCK TABLES;"

注意：InnoDB 還需備份 ib_logfile*、ibdata* 等系統表空間檔案，否則無法啟動。

方法二：mysqlhotcopy (僅 MyISAM、本機最快)

/usr/local/mysql/bin/mysqlhotcopy -u root -p'pwd' db_name /backup/

• 原理：LOCK TABLES → FLUSH TABLES → cp/scp
• 限制：只能備份 MyISAM，需在資料庫所在主機執行，需 SELECT, RELOAD, LOCK TABLES 權限。

方法三：mysqldump (邏輯熱備份、最通用、跨版本遷移首選)

核心參數速查

參數	作用	建議
--single-transaction	InnoDB 一致性快照，不鎖表	InnoDB 必加
--quick / -q	流式讀取，不緩存全表到記憶體	大表必加
--extended-insert	多行 INSERT，縮小檔案、加快還原	預設開啟
--default-character-set=utf8mb4	指定字元集	防亂碼必加
--routines -R	包含預存程序/函數	視需求
--triggers	包含觸發器	預設開啟
--master-data=2	輸出 CHANGE MASTER TO 註解 (含 Binlog 位置)	搭配主從搭建
--set-gtid-purged=OFF	不輸出 GTID 資訊	避免 GTID 衝突

常用組合範例

# 全量備份 (InnoDB 推薦)
mysqldump -u root -p \
  --single-transaction --quick --extended-insert \
  --default-character-set=utf8mb4 --routines --triggers \
  --master-data=2 \
  db_name | gzip > /backup/db_name_$(date +%F).sql.gz

# 僅結構
mysqldump -u root -p -d db_name > /backup/db_name_struct.sql

# 僅資料
mysqldump -u root -p -t db_name | gzip > /backup/db_name_data.sql.gz

還原

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

# 或直接導入未壓縮檔
mysql -u root -p db_name < /backup/db_name.sql

方法四：主從複寫 (實時熱備份、高可用基石)

• 架構：Master 寫入 → Binlog → Slave 重放
• 優點：RPO≈0、RTO 秒級、可做讀寫分離
• 缺點：非同步預設可能丟資料；需監控 Seconds_Behind_Master
• 進階：半同步複寫 (rpl_semi_sync_master=ON)、GTID 模式 (MySQL 5.6+)

方法五：自動化備份 Shell 腳本 (含郵件通知、壓縮、舊檔清理)

#!/bin/bash
# ==========================================
# MySQL 自動備份腳本 (每日全量 + 保留 30 天)
# 部署：/usr/local/bin/mysql_daily_backup.sh，crontab 0 2 * * *
# ==========================================

BAK_DIR="/backup/mysql"
DB_USER="root"
DB_PASS="your_password"
DB_NAME="your_db"
DATE=$(date +%F)
LOG_FILE="$BAK_DIR/backup_$DATE.log"
RETENTION_DAYS=30

mkdir -p "$BAK_DIR"

echo "=== 開始備份 $DATE ===" | tee -a "$LOG_FILE"

mysqldump -u"$DB_USER" -p"$DB_PASS" \
  --single-transaction --quick --extended-insert \
  --default-character-set=utf8mb4 --routines --triggers \
  "$DB_NAME" | gzip > "$BAK_DIR/${DB_NAME}_$DATE.sql.gz"

if [[ $? -eq 0 ]]; then
    SIZE=$(du -h "$BAK_DIR/${DB_NAME}_$DATE.sql.gz" | cut -f1)
    echo "✅ 備份成功：${DB_NAME}_$DATE.sql.gz ($SIZE)" | tee -a "$LOG_FILE"
    # 郵件通知 (需配置 mailx/mutt)
    # echo "備份成功" | mail -s "MySQL Backup OK - $DATE" -a "$BAK_DIR/${DB_NAME}_$DATE.sql.gz" admin@example.com
else
    echo "❌ 備份失敗" | tee -a "$LOG_FILE"
    # echo "備份失敗" | mail -s "MySQL Backup FAILED - $DATE" admin@example.com
    exit 1
fi

# 清理舊備份
find "$BAK_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "🧹 清理 $RETENTION_DAYS 天前備份完成" | tee -a "$LOG_FILE"

選型決策樹