MySQL 資料庫備份與還原實戰指南：從 phpMyAdmin 到命令列完整操作

為何需要掌握命令列備份？

雖然 phpMyAdmin 的匯出/匯入功能直觀易用，但當資料庫體積超過 PHP 執行時間限制（預設 30 秒）或上傳檔案大小限制時，Web 介面往往會因「逾時」或「記憶體不足」而失效。掌握 mysqldump 與 mysql 命令列工具，才是 DBA 與開發者的必備技能。

Linux 環境下的備份與還原

基本備份語法

mysqldump -u <用戶名> -p <資料庫名> > <備份檔.sql>

• -u：指定 MySQL 連線用戶名
• -p：提示輸入密碼（也可直接寫 -p密碼，但不建議在歷史紀錄留存明文密碼）
• >：Shell 重導向，將標準輸出寫入檔案

實用進階範例

# 1. 備份單一資料庫並壓縮（推薦生產環境使用）
mysqldump -u root -p mydb | gzip > mydb_$(date +%F).sql.gz

# 2. 備份多個指定資料庫
mysqldump -u root -p --databases db1 db2 > multi_db.sql

# 3. 備份所有資料庫（含系統庫）
mysqldump -u root -p --all-databases > all_dbs.sql

# 4. 僅備份結構（不含資料）
mysqldump -u root -p -d mydb > schema_only.sql

# 5. 排除特定資料表
mysqldump -u root -p mydb --ignore-table=mydb.log_table > no_log.sql

還原操作

# 未壓縮檔案
mysql -u root -p mydb < backup.sql

# 壓縮檔案（gz）
gunzip < backup.sql.gz | mysql -u root -p mydb

# 或使用 source 指令（需先進入 mysql client）
mysql -u root -p
mysql> CREATE DATABASE mydb;
mysql> USE mydb;
mysql> SOURCE /path/backup.sql;

Windows 環境下的操作步驟

備份

1. 開啟「命令提示字元」（cmd）
2. 切換至 MySQL bin 目錄：
   cd "C:\Program Files\MySQL\MySQL Server 5.x\bin"
3. 執行備份指令：
   mysqldump -u root -p dbname > backup.sql
4. 輸入密碼後等待完成，備份檔將生成於當前目錄

還原

1. 開啟「MySQL Command Line Client」，輸入密碼進入 mysql> 提示符
2. 建立目標資料庫：CREATE DATABASE dbname;
3. 切換資料庫：USE dbname;
4. 執行還原：SOURCE backup.sql;
5. 出現 mysql> 且無錯誤訊息即代表成功

大型資料庫優化技巧

• 使用 --single-transaction（InnoDB 專用）： 取得一致性快照而不鎖表，適合線上熱備份。
• 調大 max_allowed_packet： 防止大欄位（BLOB/TEXT）導致封包過大錯誤。
• 分表備份： 針對超大資料表，可搭配 --where 條件分批導出，降低單次交易體積。
• 並行備份工具： 考慮使用 mydumper/myloader 實現多執行緒並行備份/還原，大幅縮短時間窗口。

最佳實務建議： 建立自動化備份腳本（Shell/Python），搭配 cron 定時執行，並將備份檔同步至異地儲存（如 S3、NAS、異地機房），定期演練還原流程，確保備份可用性。