Windows MySQL 備份與還原命令實戰手冊

環境假設

• 安裝路徑：C:\MySQL
• 用戶/密碼：root / 123456
• 資料庫：database_name
• 備份目標：D:\backup_20240115.sql

一、邏輯備份（mysqldump）

1. 單庫完整備份

cd /d C:\MySQL\bin
mysqldump -u root -p123456 --databases database_name > D:\backup_20240115.sql

關鍵參數：

• --databases：輸出含 CREATE DATABASE / USE，還原更方便
• --single-transaction：InnoDB 熱備份不鎖表（必加）
• --routines --triggers --events：備份存儲過程/觸發器/事件
• --master-data=2：輸出 CHANGE MASTER TO 註解，支援基於備份搭建從庫
• --hex-blob：二進制欄位十六進制輸出，避免字符集問題
• --compress：網路傳輸壓縮

2. 多表備份

mysqldump -u root -p123456 database_name table1 table2 > D:\backup_tables.sql

3. 全例備份

mysqldump -u root -p123456 --all-databases > D:\backup_all.sql

4. 只備份結構（不含數據）

mysqldump -u root -p123456 --no-data database_name > D:\backup_schema.sql

二、還原操作

1. 還原單庫（目標庫不存在）

# 登入 MySQL 建庫
mysql -u root -p123456 -e "CREATE DATABASE database_name CHARACTER SET utf8mb4;"
# 導入
mysql -u root -p123456 database_name < D:\backup_20240115.sql

2. 還原覆蓋現有庫（謹慎）

mysql -u root -p123456 -e "DROP DATABASE database_name; CREATE DATABASE database_name CHARACTER SET utf8mb4;"
mysql -u root -p123456 database_name < D:\backup_20240115.sql

3. 還原單表

mysql -u root -p123456 database_name < D:\backup_tables.sql

三、物理備份（停機/熱備份工具）

• 停機複製：net stop MySQL → 複製 datadir 整個目錄 → net start MySQL
• Percona XtraBackup：InnoDB 熱備份開源工具，支援增量、壓縮、流式傳輸，生產環境首選。
• MySQL Enterprise Backup：官方商業工具。

四、自動化腳本範例（PowerShell）

$date = Get-Date -Format "yyyyMMdd_HHmm"
$bakFile = "D:\backup\db_$date.sql"
$zipFile = "D:\backup\db_$date.zip"

& "C:\MySQL\bin\mysqldump.exe" -u root -p123456 `
  --single-transaction --routines --triggers --events --hex-blob `
  --databases database_name > $bakFile

Compress-Archive -Path $bakFile -DestinationPath $zipFile -Force
Remove-Item $bakFile

# 保留近 7 天
Get-ChildItem D:\backup\db_*.zip | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-7) } | Remove-Item

配合 Windows 任務排程器每日執行。

五、常見坑點

1. 備份文件編碼：PowerShell > 重導向預設 UTF-16，MySQL 讀取報錯。解決：cmd /c "mysqldump ... > file.sql" 或 Out-File -Encoding utf8。
2. 大表導入慢：關閉索引/外鍵/唯一檢查：SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; SET AUTOCOMMIT=0; 導入後恢復。
3. 版本不兼容：高版本備份導入低版本可能失敗，避免跨大版本還原。
4. 權限不足：備份需 SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS 等權限。

六、驗證備份可用性

「沒測試過恢復的備份，不是備份，是廢紙。」

• 定期（月度）在測試環境全量恢復驗證
• 檢查關鍵表行數、核心業務查詢結果一致性
• 記錄 RTO/RPO 指標