MySQL 課程 – 備份與還原

主題：匯入與匯出 SQL 檔

簡介

在日常的開發與維運工作中，資料庫的備份與還原 是不可或缺的基礎能力。
無論是新功能上線前的資料快照、測試環境的資料同步，或是因為硬體故障需要緊急復原，能夠快速且正確地 匯出（export） 與 匯入（import） SQL 檔，都是保障服務穩定性的關鍵。

MySQL 提供了多種工具與指令（mysqldump、mysql、source、LOAD DATA INFILE 等），配合適當的參數設定，既能產生結構完整、可讀性高的 .sql 檔，也能在不同環境之間無縫搬移資料。本文將從概念說明、實作範例、常見陷阱與最佳實踐，帶領讀者一步步掌握 MySQL 匯入/匯出的全流程。

核心概念

1. 匯出（Export）與匯入（Import）的基本原理

步驟	說明
匯出	由 MySQL 伺服器將資料庫或資料表的結構（DDL）與資料（DML）寫入文字檔（`.sql`），檔案內容通常是一連串 `CREATE TABLE`、`INSERT INTO` 等 SQL 陳述式。
匯入	讀取 `.sql` 檔，將裡面的 SQL 陳述式依序送給 MySQL 執行，重新建立結構並寫入資料。

注意：匯出檔案是純文字，故可使用版本控制、檢視差異或跨平台傳輸；但若資料量龐大，檔案大小與匯入速度會受到磁碟 I/O 與網路帶寬的限制。

2. 常用工具與指令

工具	用途	常見參數
`mysqldump`	將資料庫/資料表匯出成 SQL 檔	`--single-transaction`、`--routines`、`--triggers`、`--compact`
`mysql`	直接執行 SQL 檔進行匯入	`--binary-as-hex`、`--max_allowed_packet`
`source`（於 MySQL 客戶端）	在互動式環境執行檔案	`source /path/to/file.sql;`
`LOAD DATA INFILE`	高效匯入大量 CSV/TSV 檔	`FIELDS TERMINATED BY ','`、`LINES TERMINATED BY '\n'`

3. 匯出範例：完整資料庫、單表、或僅結構

3.1 匯出整個資料庫（含觸發器、預存程序、事件）

# 匯出 mydb 資料庫，產生 mydb_full.sql
mysqldump -u root -p --single-transaction --routines --triggers --events \
    mydb > mydb_full.sql

--single-transaction 在 InnoDB 引擎下使用快照，避免長時間鎖表；--routines、--triggers、--events 則確保把預存程序、觸發器與事件一起備份。

3.2 僅匯出特定資料表的結構（不含資料）

mysqldump -u root -p --no-data mydb orders > orders_schema.sql

--no-data 只產生 CREATE TABLE，適合建立測試環境的空表。

3.3 匯出多個資料表（含資料）至同一檔案

mysqldump -u root -p mydb customers orders products > subset.sql

3.4 使用壓縮減少檔案大小

mysqldump -u root -p mydb | gzip > mydb.sql.gz

壓縮後的檔案在匯入前需先解壓，或直接使用 gunzip -c 讀取。

3.5 匯出為 CSV（適合與其他系統交換）

mysql -u root -p -e "
SELECT * FROM mydb.orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\n';
"

INTO OUTFILE 只能寫入 MySQL 伺服器本機的目錄，且需要檔案寫入權限。

4. 匯入範例：從 `.sql` 檔恢復資料

4.1 使用 `mysql` 指令一次匯入整個檔案

# 匯入 mydb_full.sql（已存在的資料庫）
mysql -u root -p mydb < mydb_full.sql

若檔案過大，可加上 --max_allowed_packet=1G 以避免「Packet too large」錯誤。

4.2 在 MySQL 客戶端使用 `source`

mysql> CREATE DATABASE IF NOT EXISTS mydb;
mysql> USE mydb;
mysql> source /path/to/mydb_full.sql;

source 會即時顯示執行進度，適合手動檢查錯誤。

4.3 匯入壓縮檔（不解壓）

gunzip -c mydb.sql.gz | mysql -u root -p mydb

4.4 使用 `LOAD DATA INFILE` 匯入 CSV

LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(order_id, customer_id, order_date, total_amount);

IGNORE 1 LINES 用於略過 CSV 標題列。

4.5 匯入大檔時的加速技巧

# 關閉外鍵檢查與唯一鍵檢查（僅在匯入期間）
mysql -u root -p -e "
SET foreign_key_checks = 0;
SET unique_checks = 0;
SOURCE /path/to/large_dump.sql;
SET foreign_key_checks = 1;
SET unique_checks = 1;
"

關閉檢查可以大幅提升匯入速度，但務必在匯入完成後恢復。

常見陷阱與最佳實踐

陷阱	可能的後果	解決方案 / 最佳實踐
字符集不一致	匯入時出現「Incorrect string value」或資料損壞	在匯出與匯入時都明確指定 `--default-character-set=utf8mb4`，或在 `mysqldump` 中使用 `--skip-set-charset` 後自行設定。
大檔案導致「MySQL server has gone away」	匯入中斷，資料不完整	調整 `max_allowed_packet`（如 `SET GLOBAL max_allowed_packet=1G;`）或分割檔案（`split -l 5000`）再逐段匯入。
匯入時外鍵或觸發器衝突	匯入失敗或資料不一致	暫時停用外鍵檢查 (`SET foreign_key_checks = 0;`)；若有觸發器影響，可先匯入結構，再匯入資料，最後再匯入觸發器。
使用 `INTO OUTFILE` 時權限不足	無法寫入檔案、報錯「The MySQL server is running with the --secure-file-priv option」	確認 `secure_file_priv` 設定，或將路徑改為允許的目錄；若無法修改，可改用 `SELECT ... INTO DUMPFILE` 或外部工具。
備份檔案未加密或未妥善保存	敏感資料外洩	使用壓縮加密工具（如 `gzip -c

最佳實踐清單

定期測試還原：僅有備份而未測試還原是無效的。每月抽一次備份檔案，於測試環境完整還原一次。
使用事務性備份：對 InnoDB 資料庫，建議加上 --single-transaction，避免鎖表。
保留備份版本：至少保留最近 3 次全備份與每日增量備份，以應對不同時間點的回復需求。
自動化腳本：將備份與匯入流程寫成 Bash/Python 腳本，配合排程（cron）執行，降低人為錯誤。
紀錄備份資訊：在備份檔名或伴隨的 metadata.txt 中寫入資料庫版本、備份時間、執行者等資訊，方便日後追蹤。

實際應用場景

場景	為何需要匯入/匯出	典型流程
開發環境同步	開發者需要與測試環境保持相同的測試資料	1. `mysqldump -u ... mydb > dev_snapshot.sql` 2. 傳送至開發機 3. `mysql -u ... mydb < dev_snapshot.sql`
跨地域資料遷移	公司搬遷資料中心或將服務切換到雲端	1. 在舊機使用 `mysqldump --single-transaction --routines --triggers` 產生完整備份 2. 傳至新機（使用 SCP/rsync） 3. 在新機調整 `character_set_server` 後匯入
災難復原（DR）	主機故障，需要在備援機器快速恢復服務	1. 定時（每 6 小時）產生增量備份 2. 備援機自動檢測最新備份 3. `mysql` 匯入增量檔，恢復至最近狀態
資料分析與報表	把 MySQL 資料匯出為 CSV 供 BI 工具使用	`SELECT ... INTO OUTFILE '/tmp/report.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';`
版本升級測試	在升級 MySQL 8.0 前，需要先在測試環境驗證備份檔案相容性	產生 `mysqldump --compatible=mysql40`（或 `--skip-triggers`）的備份，於測試機上匯入驗證無錯誤。

總結

匯入與匯出 SQL 檔是 MySQL 備份與還原 中最基礎卻最關鍵的技能。透過 mysqldump、mysql、source、LOAD DATA INFILE 等工具，我們可以：

快速產生完整或部份的資料備份（結構、資料、觸發器、預存程序等）
安全、可靠地在不同環境之間搬移資料（開發、測試、正式、災備）
利用最佳實踐（事務備份、字符集統一、檔案加密、定期還原測試）降低風險、提升效率

在實務上，建議將備份腳本寫成自動化流程，結合排程與版本管理，並且每次匯入前先檢查字符集與外鍵檢查設定，以免因環境差異造成資料錯誤。只要掌握上述概念與技巧，即可在日常開發與運維工作中，輕鬆完成 MySQL 資料的匯入與匯出，為系統的可靠性與彈性奠定堅實基礎。祝你在資料庫管理的路上越走越順！