MySQL 資料庫備份與還原：從檔案複製到 mysqldump 正規作法

前言：兩種備份哲學的對決

MySQL 備份主要分為物理備份（直接複製資料檔案）與邏輯備份（匯出 SQL 語句）。前者速度極快、操作簡單，但受限於儲存引擎、版本相容性與一致性風險；後者通用性強、跨版本遷移無虞，但耗時較長。本文將實測「檔案複製法」可行性，並詳細說明官方推薦的 mysqldump 正規流程，協助您根據業務場景選擇最適策略。

一、捷徑實驗：直接複製資料目錄（物理備份）

實驗環境

• 作業系統：Windows NT 4.0
• MySQL 版本：3.22.34
• 管理工具：phpMyAdmin 2.1.0
• 來源主機：HostA（含資料庫 TestA）
• 目標主機：HostB（已安裝 MySQL，無 TestA）

操作步驟

1. 於 HostA 確認 MySQL 資料目錄位置（實測為 C:\MySQL\data）
2. 找到對應資料庫的子目錄 C:\MySQL\data\TestA
3. 將整個 TestA 目錄複製至 HostB 同樣路徑 C:\MySQL\data\TestA
4. 重新整理 HostB 的 phpMyAdmin，觀察資料庫列表是否出現 TestA
5. 執行查詢、修改等操作驗證資料完整性

實驗結論與風險提示

二、正規作法：mysqldump 邏輯備份（官方推薦）

基本語法

mysqldump [OPTIONS] database [tables...]

• 未指定資料表 → 匯出整個資料庫結構與資料
• 指定資料表 → 僅匯出指定表

常用參數組合速查

實戰範例：生產環境標準備份腳本

#!/bin/bash
# 每日備份腳本範例
BACKUP_DIR="/backup/mysql/$(date +%F)"
mkdir -p "$BACKUP_DIR"

mysqldump --host=localhost \
          --user=backup_user \
          --password='StrongPassword' \
          --single-transaction \
          --master-data=2 \
          --routines --triggers --events \
          --extended-insert --quick \
          --hex-blob \
          --default-character-set=utf8mb4 \
          --databases db1 db2 db3 \
  | gzip > "$BACKUP_DIR/full_backup_$(date +%H%M).sql.gz"

# 保留 7 天備份
find /backup/mysql -type f -mtime +7 -name '*.gz' -delete

還原操作

# 解壓縮並還原單一資料庫
gunzip -c backup.sql.gz | mysql -u root -p target_db

# 或直接導入壓縮檔
zcat backup.sql.gz | mysql -u root -p target_db

三、進階備份策略對比表

四、最佳實踐建議

1. 分級備份：每日完整邏輯備份 + 每小時 Binlog 增量備份（啟用 --master-data）
2. 異地備份：備份檔案同步至異地儲存（S3、NAS、異地伺服器）
3. 定期演練：每季至少一次完整還原測試，驗證備份可用性與 RTO/RPO
4. 權限最小化：備份布號僅需 SELECT、LOCK TABLES、RELOAD、REPLICATION CLIENT、PROCESS 權限
5. 加密傳輯與靜態加密：傳輸層 TLS、備份檔案 GPG 加密

結語

「檔案複製法」雖然在特定舊版本 MyISAM 環境下可行，但面對現代 InnoDB 為主、高可用性要求的生產系統，mysqldump 邏輯備份配合 --single-transaction 才是穩健的基礎選擇。對於 TB 級資料量，建議導入 Percona XtraBackup 物理熱備份方案。無論採用何種工具，「備份未測試等於無備份」——請務必建立定期還原演練機制，確保災難發生時能從容復原。