MySQL 資料庫備份與還原：mysqldump 完整指南與參數詳解

MySQL 資料庫備份與還原：mysqldump 完整指南與參數詳解

在專案營運過程中，資料庫備份與還原是不可或缺的環節。常見的處理方式主要有兩種：使用 SELECT ... INTO OUTFILE 搭配 LOAD DATA INFILE，以及使用官方工具 mysqldump。前者優在於可自訂導出格式、僅導出純資料不含建表結構，便於導入不同結構的資料表，靈活度較高；後者則是標準化的備份方案，功能強大、參數豐富，適合完整庫備份、結構備份及跨環境遷移。

一、使用 INTO OUTFILE / LOAD DATA INFILE 導出入資料

此方法適合需自訂欄位分隔符、行結束符等格式的場景。示例：

-- 導出符合條件的資料到檔案
SELECT * FROM mytable 
WHERE status != 0 AND name != '' 
INTO OUTFILE '/home/db_bak2012' 
FIELDS TERMINATED BY '|' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';

-- 導入資料到另一張表
LOAD DATA INFILE '/home/db_bak2012' 
INTO TABLE mytable_bak 
FIELDS TERMINATED BY '|' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';

二、mysqldump 常用實戰範例

1. 備份整個資料庫

mysqldump -u 用戶名 -p 資料庫名 > 導出檔名.sql
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2. 備份單一資料表

mysqldump -u 用戶名 -p 資料庫名 表名 > 導出檔名.sql
mysqldump -u wcnc -p smgp_apps_wcnc users > wcnc_users.sql

3. 僅導出資料庫結構（不含資料）

mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc > d:\wcnc_db.sql
# -d: 不導出資料；--add-drop-table: 每個 CREATE 前加 DROP TABLE

4. 還原資料庫（使用 source 指令）

# 進入 MySQL 控制台
mysql -u root -p
mysql> USE 資料庫名;
mysql> SET NAMES utf8;  -- 注意是 utf8 而非 UTF-8，避免亂碼
mysql> SOURCE d:\wcnc_db.sql;

5. 批量備份多個資料庫

mysqldump -uroot -p --databases test mysql  # 以空格分隔多個庫名

6. 備份所有資料庫

mysqldump -uroot -p --all-databases

三、mysqldump 核心參數詳細說明

以下整理常用參數，供快速查閱：

四、實務建議

• 大型 InnoDB 資料庫備份首選 --single-transaction --quick --routines --triggers --events 組合，確保一致性且不阻塞寫入。
• 跨版本遷移時加上 --compatible=ansi 降低相容性風險。
• Windows 環境下建議使用 --result-file 避免換行符問題。
• 定期備份腳本應包含錯誤檢查、日誌記錄與舊備份清理機制。