MySQL 備份與還原 – mysqldump 備份工具

簡介

在任何資料庫系統的運維工作中，備份是最基本也是最重要的任務。即使是最穩定的 MySQL 伺服器，也可能因硬體故障、操作失誤或惡意攻擊而失去資料。若沒有完善的備份機制，資料遺失的代價往往遠高於事前投入的時間與資源。

mysqldump 是 MySQL 官方提供的命令列備份工具，支援 完整資料庫、單一資料庫、甚至單表 的導出，且輸出為可讀的 SQL 檔案，方便在任何 MySQL 版本上還原。本篇文章將從概念說明、常見用法、陷阱與最佳實踐，帶領初學者到中級開發者快速上手 mysqldump，並提供實務範例。

---

核心概念

1. mysqldump 的工作原理

mysqldump 會向目標 MySQL 伺服器發送 SELECT 查詢，將資料表結構（CREATE TABLE）與資料（INSERT）逐行寫入輸出檔案。因為它產生的是純文字 SQL，不依賴於特定的二進位格式，所以可以在不同平台、不同 MySQL 版本之間搬移資料。

⚠️ 注意：mysqldump 產生的檔案大小往往比使用物理檔案快照（如 LVM snapshot）大，因為它包含了完整的 INSERT 語句。若需壓縮，請搭配 gzip、bzip2 或 xz 使用。

2. 常見參數說明

參數	功能	範例
-u、-p	指定使用者與密碼	-u root -pMyPass
--databases	指定一或多個資料庫（會自動加上 CREATE DATABASE）	--databases db1 db2
--single-transaction	在 InnoDB 引擎下使用一致性快照，避免鎖表	--single-transaction
--routines、--events	同時備份儲存過程、事件	--routines --events
--no-data	只備份結構，不匯出資料	--no-data
--where	只匯出符合條件的資料	--where="id>1000"
--compress	在客戶端與伺服器之間使用壓縮傳輸	--compress
-C	輸出時使用 gzip 壓縮（需要配合管道）	`

3. 基本備份流程

1. 決定備份範圍（全伺服器、單庫或單表）。
2. 選擇適當參數（如 --single-transaction、--routines）。
3. 執行 mysqldump，將結果導入檔案或管道。
4. 驗證備份檔案（檢查檔案大小、是否可解壓、是否能成功 mysql 還原）。
5. 安排排程（Cron、Task Scheduler）與備份保留策略（如 7 天、30 天）。

---

程式碼範例

以下範例均以 Linux Bash 為例，Windows 下可直接使用相同參數的 mysqldump.exe。

1️⃣ 完整備份整個 MySQL 伺服器（含所有資料庫）

#!/bin/bash
# 備份所有資料庫，使用 gzip 壓縮，檔名包含日期時間
DATE=$(date +"%Y%m%d_%H%M%S")
mysqldump -u root -pYourPassword --all-databases \
    --single-transaction --quick --lock-tables=false \
    | gzip > /backup/mysql_full_${DATE}.sql.gz

echo "✅ 完整備份完成：/backup/mysql_full_${DATE}.sql.gz"

說明：

• --single-transaction 只對 InnoDB 有效，避免整個備份期間鎖表。
• --quick 讓 mysqldump 逐行讀取資料，降低記憶體使用。
• --lock-tables=false 防止在大型資料庫上產生長時間的表鎖。

2️⃣ 只備份單一資料庫 sales_db（含儲存過程與事件）

#!/bin/bash
DB="sales_db"
DATE=$(date +"%Y%m%d")
mysqldump -u backup_user -pStrongPass \
    --databases $DB \
    --routines --events \
    --single-transaction \
    > /backup/${DB}_${DATE}.sql

# 檢查檔案是否成功產生
if [ -s /backup/${DB}_${DATE}.sql ]; then
    echo "✅ ${DB} 資料庫備份成功"
else
    echo "❌ 備份失敗"
fi

說明：

• --routines 會把儲存過程（Procedures）與函式（Functions）一起匯出。
• --events 會把 Event Scheduler 設定匯出。

3️⃣ 只備份特定資料表 orders 與 customers（不含資料）

mysqldump -u root -pYourPassword \
    --no-data \
    sales_db orders customers \
    > /backup/sales_schema.sql

說明：--no-data 只產生 CREATE TABLE 語句，適合作為 開發環境的結構同步。

4️⃣ 只匯出符合條件的資料（例如：2023 年之後的訂單）

mysqldump -u root -pYourPassword \
    sales_db orders \
    --where="order_date >= '2023-01-01'" \
    > /backup/orders_2023_plus.sql

說明：--where 只對單一資料表有效，適合 增量備份 或 資料分割。

5️⃣ 使用管道直接壓縮並上傳至遠端 S3（示範）

DATE=$(date +"%Y%m%d")
mysqldump -u root -pYourPassword --all-databases \
    --single-transaction \
    | gzip \
    | aws s3 cp - s3://my-mysql-backups/all_${DATE}.sql.gz

echo "✅ 備份已上傳至 S3：all_${DATE}.sql.gz"

說明：

• 透過管道 (|) 把 mysqldump 輸出直接送給 gzip，再交給 AWS CLI 上傳。
• 省去本機暫存磁碟空間，適合 雲端備份。

6️⃣ 還原備份檔案（最常見的還原指令）

# 若備份為 gzip 壓縮檔
gzip -dc /backup/mysql_full_20251125_030000.sql.gz | mysql -u root -pYourPassword

# 若備份為純文字檔
mysql -u root -pYourPassword < /backup/sales_db_20251125.sql

說明：使用 mysql 客戶端執行 SQL 檔案，即可將資料還原至目標伺服器。

---

常見陷阱與最佳實踐

陷阱	為什麼會發生	解決方式 / 最佳實踐
備份時鎖表	使用預設的 --lock-all-tables 會對 MyISAM 表加全局鎖，導致應用程式暫停。	對 InnoDB 使用 --single-transaction，對 MyISAM 可加上 --lock-tables 只鎖必要的表。
備份檔案過大	未壓縮且包含大量 INSERT。	使用管道 `
還原失敗：字符集不匹配	匯出時使用預設字符集，還原環境字符集不同。	加上 --default-character-set=utf8mb4（或目標字符集）確保一致。
忘記備份儲存過程/事件	mysqldump 預設不會匯出 Routines 與 Events。	記得加上 --routines --events。
備份時間過長	大型資料庫一次性備份。	使用 --single-transaction 搭配 --quick，或分表、分庫備份。
備份檔案遺失	未設定備份輪轉或保留策略。	建立 Cron 任務自動刪除超過保留天數的檔案（如 find /backup -type f -mtime +30 -delete）。

最佳實踐總結

1. 一致性：對 InnoDB 使用 --single-transaction，避免鎖表。
2. 安全性：不要在指令列直接寫明密碼，改用 .my.cnf 或環境變數。
3. 自動化：把備份腳本寫成可重用的 Shell 函式，配合 Cron / systemd timers。
4. 驗證：備份完成後立即執行 mysqlcheck --all-databases --check-upgrade，或在測試環境還原驗證。
5. 加密傳輸：若備份檔案需傳至遠端，使用 ssh、sftp 或雲端加密儲存（如 AWS S3 SSE）。

---

實際應用場景

場景	使用方式	為什麼適合
每日夜間全庫備份	`mysqldump --all-databases	gzip > /backup/full_$(date +%F).sql.gz` + Cron 0 2 * * *
大型電商平台的增量備份	mysqldump sales_db orders --where="order_date >= CURDATE() - INTERVAL 1 DAY"	只備份前一天的訂單，減少備份容量與時間。
開發環境的結構同步	mysqldump --no-data prod_db > dev_schema.sql	只需要表結構，快速在開發機建立相同 schema。
跨區域災備	`mysqldump --single-transaction prod_db	gzip
容器化環境（Docker）	在 Dockerfile 中加入 ENTRYPOINT ["mysqldump", "-u", "root", "-p$MYSQL_ROOT_PASSWORD", "--all-databases"] 並搭配 docker run --rm	讓備份成為一次性容器任務，易於編排與自動化。

---

總結

mysqldump 雖然是最早期的 MySQL 備份工具，但因其 純文字、跨平台、支援儲存過程與事件 的特性，在今天仍是 最通用、最可靠的備份選項。掌握以下要點，就能在實務環境中建立安全、快速且可維護的備份機制：

• 使用 --single-transaction 取得一致性快照，避免鎖表。
• 依需求加上 --routines、--events、--no-data 等參數，控制備份內容。
• 結合管道 (| gzip、| aws s3 cp) 實現即時壓縮與遠端儲存。
• 設計自動化腳本與保留策略，確保備份不會因人為疏失而遺失。
• 定期在測試環境還原驗證，確保備份檔案可用。

只要遵循上述 最佳實踐，即使在高流量或大型資料庫環境中，mysqldump 也能提供穩定可靠的備份與還原能力，讓開發與運維團隊對資料安全更有信心。祝你備份順利，資料永遠安全！