MySQL 備份還原標準作業程序（SOP）：mysqldump 導出 + source 導入

無論 Windows 還是 Linux，mysqldump + source 是最通用、相容性最高的邏輯備份還原方案。本文提供兩大平台的標準化操作步驟、常用參數組合與避坑指南。

一、Windows 環境操作步驟

1. 開啟命令提示字元 (CMD)，進入 bin 目錄

cd /d "C:\Program Files\MySQL\MySQL Server 8.0\bin"
# 或依實際安裝路徑調整

2. 執行備份 (導出)

# 單庫備份（推薦參數組合）
mysqldump -u root -p --single-transaction --routines --triggers --events --master-data=2 --flush-logs --hex-blob db_name > backup_20240115.sql

# 輸入密碼後開始導出，無報錯即成功

參數速查：

• --single-transaction：InnoDB 熱備核心，不鎖表。
• --routines --triggers --events：備份存儲過程/觸發器/事件。
• --master-data=2：輸出 CHANGE MASTER TO 註解，記錄 binlog 位置。
• --flush-logs：備份前滾動 binlog，配合 master-data 實現精確 PITR。
• --hex-blob：二進位欄位十六進位輸出，避免編碼問題。

3. 執行還原 (導入)

# 進入 MySQL 客戶端
mysql -u root -p

# 在 mysql> 提示符下：
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE db_name;
SOURCE backup_20240115.sql;
# 重新出現 mysql> 且無 ERROR 即還原成功

注意：SOURCE 僅在 MySQL 客戶端互動模式可用，不可在 CMD 直接管道導入（需用 mysql -u root -p db_name < backup.sql）。

二、Linux 環境操作步驟

1. 備份 (導出)

cd /var/lib/mysql   # 視安裝路徑調整，僅為習慣目錄
mysqldump -u root -p --single-transaction --routines --triggers --events --master-data=2 --flush-logs --hex-blob db_name > backup_$(date +%F).sql

2. 還原 (導入) - 兩種方式

方式 A：進入客戶端 source（可見進度）

mysql -u root -p
# 密碼後
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE db_name;
SOURCE /path/to/backup_20240115.sql;

方式 B：管道直導（腳本自動化適用）

mysql -u root -p db_name < /path/to/backup_20240115.sql

三、進階場景：增量恢復 (PITR)

1. 全量備份時已獲取 binlog 位置（--master-data=2 註解中）。
2. 事故發生後，從全量備份還原。
3. 用 mysqlbinlog 從備份位置恢復至事故前：

mysqlbinlog --start-position=XXX --stop-position=YYY /var/lib/mysql/bin.000005 | mysql -u root -p db_name

四、避坑與最佳實務清單

坑點	後果	對策
未加 --single-transaction 直接備份 InnoDB	全表鎖定、業務阻塞	InnoDB 必加此參數
備份檔未指定字符集，還原時預設 latin1	中文亂碼	建庫指定 utf8mb4，--default-character-set=utf8mb4 導出
還原時未關閉 binlog	產生大量無用 binlog、主從同步異常	還原前 SET SESSION sql_log_bin=0;
大表還原極慢	單執行緒 INSERT、索引逐筆維護	還原前 DROP INDEX 非唯一索引，還原後 CREATE INDEX；或用 LOAD DATA 物理導入
備份檔未驗證	關鍵時刻發現備份損壞	定期 HEAD -n 20 backup.sql 檢查建表語句、抽樣 mysqlbinlog 驗證

自動化備份腳本範例

#!/bin/bash
# /usr/local/bin/mysql_daily_backup.sh
set -euo pipefail
BACKUP_DIR="/backup/mysql"
DB_USER="backup_user"
DB_PASS="Str0ngP@ss"
DB_NAME="prod_db"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"
FILE="$BACKUP_DIR/${DB_NAME}_$(date +%F_%H%M).sql.gz"

mysqldump -u"$DB_USER" -p"$DB_PASS" \
  --single-transaction --routines --triggers --events \
  --master-data=2 --flush-logs --hex-blob \
  --default-character-set=utf8mb4 \
  "$DB_NAME" | gzip > "$FILE"

# 刪除舊備份
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "[$(date)] Backup completed: $FILE"

加入 crontab -e：0 2 * * * /usr/local/bin/mysql_daily_backup.sh >> /var/log/mysql_backup.log 2>&1（每天凌晨 2 點備份）。