MySQL mysqldump 備份還原完整指南：參數詳解、進階用法與 SELECT INTO OUTFILE 替代方案

mysqldump 核心價值

• 將整個資料庫/資料表導出為單一 SQL 腳本檔
• 包含 DDL (CREATE) 與 DML (INSERT) 完整重建指令
• 支援 WHERE 條件篩選匯出
• 簡單、快速、跨版本相容

基本語法

mysqldump [選項] 資料庫名稱 [資料表名稱...] > 輸出檔案.sql
mysqldump [選項] --databases 資料庫1 [資料庫2...] > 輸出檔案.sql
mysqldump [選項] --all-databases > 輸出檔案.sql

常用範例

# 整個資料庫備份
mysqldump -p Meet_A_Geek > MeetAGeek_Dump_File.txt

# 單一資料表備份
mysqldump -p Meet_A_Geek Orders > MeetAGeek_Orders.txt

# 條件篩選備份
mysqldump -p --where="Order_ID > 2000" Meet_A_Geek Orders > Special_Dump.txt

核心參數完整對照表

進階實戰範例

# 匯出 2000 年後訂單為 CSV 供 Excel 使用
mysqldump -p --where="Order_Date >= '2000-01-01'" \
          --tab=/home/mark --no-create-info \
          --fields-terminated-by=, Meet_A_Geek Orders

Schema 概念

Schema = 以 DDL (CREATE) 完整描述資料庫結構的語句集合。mysqldump 實質上是將 Schema 逆向工程為 SQL 腳本。

替代方案：SELECT INTO OUTFILE

適用於需自訂輸出格式、多表 JOIN、報表產生場景。

基本語法

SELECT * INTO OUTFILE '/path/Orders.txt'
FIELDS TERMINATED BY ',' 
FROM Orders 
WHERE Order_Date >= '2000-01-01';

報表產生器範例

-- 建立 Report_G.rpt
USE Meet_A_Geek;
INSERT INTO Customers (Customer_ID, Last_Name, First_Name) VALUES (NULL, 'Kinnard', 'Vicky');
INSERT INTO Customers (Customer_ID, Last_Name, First_Name) VALUES (NULL, 'Kinnard', 'Steven');
INSERT INTO Customers (Customer_ID, Last_Name, First_Name) VALUES (NULL, 'Brown', 'Sam');
SELECT Last_Name INTO OUTFILE '/home/mark/Report.rpt' FROM Customers WHERE Customer_ID > 1;

-- 執行
mysql < Report_G.rpt

還原/匯入指令

# 完整還原
mysql -p Meet_A_Geek < MeetAGeek_Dump_File.txt

# 單表還原
mysql -p Meet_A_Geek < MeetAGeek_Orders.txt

最佳實務建議

• 例行備份腳本加入 --opt --quick --lock-tables --flush-logs
• 大表必須加 --quick 避免記憶體耗盡
• 異地備份：壓縮後傳輸至異地儲存
• 定期演練還原流程，驗證備份可用性
• 二進制日誌 (--flush-logs) 配合時間點復原 (PITR)