MySQL 命令列備份與還原完整實戰指南（Windows/Linux 跨平台）

核心工具：mysqldump、mysql、source

MySQL 官方提供三大命令列工具組成備份還原鐵三角：

• mysqldump：邏輯備份（SQL 腳本）
• mysql：客戶端連線工具
• source：mysql 客戶端內建命令，執行 SQL 腳本

一、資料庫導出（備份）

Windows 環境

REM 1. 進入 bin 目錄（或已加入 PATH 環境變數）
cd C:\Program Files\MySQL\MySQL Server 8.0\bin

REM 2. 備份整個資料庫
mysqldump -u root -p mydb > mydb_backup.sql

REM 3. 備份單張表
mysqldump -u root -p mydb users > users_backup.sql

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

REM 5. 壓縮備份（管道配合 gzip）
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

Linux 環境

# 1. 進入資料目錄（視安裝路徑調整）
cd /var/lib/mysql

# 2. 備份並壓縮
mysqldump -u root -p mydb | gzip > mydb_backup_$(date +%F).sql.gz

# 3. 定時備份（crontab -e）
0 3 * * * /usr/bin/mysqldump -u root -p'password' mydb | gzip > /backup/mydb_$(date +\%F).sql.gz

二、資料庫導入（還原）

方法一：mysql 客戶端 source 命令（互動式）

mysql -u root -p
mysql> CREATE DATABASE mydb_restored;
mysql> USE mydb_restored;
mysql> SOURCE /path/to/mydb_backup.sql;

方法二：管道直接導入（非互動式，適合腳本）

# Linux
mysql -u root -p mydb_restored < mydb_backup.sql

# Windows (PowerShell)
Get-Content mydb_backup.sql | mysql -u root -p mydb_restored

方法三：壓縮檔直接解壓導入

gunzip < mydb_backup.sql.gz | mysql -u root -p mydb_restored

三、進階備份策略

四、常見坑點避雷

1. 權限不足：備份帳號需 SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS 權限
2. 大表鎖表：InnoDB 必加 --single-transaction 避免阻塞寫入
3. 字符集亂碼：導出入同加 --default-character-set=utf8mb4
4. 版本不相容：高版本 mysqldump 導出的 SQL 可能無法導入低版本，建議版本一致
5. GTID 問題：主從環境導入時需 SET @@SESSION.GTID_NEXT=... 或使用 --set-gtid-purged=OFF

五、驗證備份可用性

# 1. 檢查檔案大小非 0
ls -lh mydb_backup.sql.gz

# 2. 抽樣檢查 SQL 完整性
zcat mydb_backup.sql.gz | head -20
zcat mydb_backup.sql.gz | tail -10

# 3. 定期演練恢復（建議月度）
# 建立臨時庫驗證，確認資料筆數、檢查和一致

延伸閱讀

• MySQL 重複記錄的處理方法
• MySQL 查詢快取機制簡介
• 5 種常見的 MySQL 日誌類型