MySQL 資料匯入匯出完整指南：mysqldump、mysqlimport、LOAD DATA、批次處理

一、mysqldump 邏輯備份（最通用）

基本語法

mysqldump -u user -p [options] db_name [tbl_name ...] > dump.sql

常見場景

關鍵參數

• --opt：開啟 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset（預設開啟）。
• --single-transaction：InnoDB 熱備份不鎖表（需啟用 binlog）。
• --master-data=2：記錄 binlog 位置，利於主從同步起點。
• --where="id > 1000"：條件式備份部分資料。
• --quick：大表逐行輸出，避免記憶體暴增。

二、mysqlimport / LOAD DATA 大量匯入（高效能）

mysqlimport（命令列工具）

# 檔案名 = 表名.txt，欄位預設 Tab 分隔
mysqlimport -u root -p --local --fields-terminated-by=',' --fields-enclosed-by='"' mydb users.csv

參數對照 LOAD DATA 子句。

LOAD DATA INFILE（SQL 指令，可嵌入程式）

LOAD DATA LOCAL INFILE '/path/file.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, email, created_at);

• LOCAL：客戶端讀檔傳給伺服器（適合遠程）。
• REPLACE / IGNORE：主鍵/唯一鍵衝突策略。
• LOW_PRIORITY：等無讀取鎖才寫入。
• 指定欄位順序：(col1, col3, col5) 對應檔案欄位，彈性應對結構差異。

三、批次處理（非互動執行 SQL 腳本）

# 檔案 batch.sql 含多條 SQL，每行以 ; 結尾
mysql -u root -p < batch.sql

適用於：部署腳本、從日誌恢復、自動化 CI/CD。

四、直接複製檔案（物理備份，限 MyISAM/同架構）

• 停庫或 FLUSH TABLES WITH READ LOCK + cp -R datadir/db /backup/。
• InnoDB 需複製整個 data 目錄含 ibdata1、ib_logfile*，且版本、配置需相同。
• 生產環境建議用 Percona XtraBackup 熱備份 InnoDB。

五、還原/恢復

六、最佳實務清單

1. 備份前 FLUSH LOGS 切換 binlog，配合 --master-data 記錄位置。
2. 生產庫用 --single-transaction 避免長時間鎖表。
3. 備份檔加密儲存、異地備份、定期演練恢復。
4. 命名規範：db_YYYYMMDD_HHMM.sql.gz。
5. 監控備份耗時、檔案大小、校驗和（md5/sha256）。