為什麼不用 mysqldump？

mysqldump 產生 SQL 語句，解析執行較慢。SELECT ... INTO OUTFILE 與 LOAD DATA INFILE 直接處理文字檔，速度快 5-10 倍，適合大量資料遷移、ETL、備份還原。

匯出資料 (INTO OUTFILE)

基本語法

SELECT * FROM table_name INTO OUTFILE 'file_path';

常用範例

-- 全表匯出（預設路徑：secure_file_priv 目錄）
SELECT * FROM phpernote INTO OUTFILE 'phpernote.txt';

-- 指定欄位匯出
SELECT id, name, email FROM users INTO OUTFILE 'users_export.txt';

-- 自訂格式（CSV）
SELECT * FROM orders
INTO OUTFILE 'orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

匯入資料 (LOAD DATA INFILE)

基本語法

LOAD DATA INFILE 'file_path' INTO TABLE table_name;

常用範例

-- 全欄位匯入（檔案欄位順序需與表一致）
LOAD DATA INFILE 'users_export.txt' INTO TABLE users;

-- 指定欄位匯入（須與匯出順序一致）
LOAD DATA INFILE 'users_export.txt' INTO TABLE users (id, name, email);

-- CSV 格式匯入
LOAD DATA INFILE 'orders.csv' INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 跳過標題行

權限與安全設定（關鍵）

常見錯誤

ERROR 1 (HY000): Can't create/write to file '/home/a' (Errcode: 13)

解決方案

1. 檢查 secure_file_priv：
   SHOW VARIABLES LIKE 'secure_file_priv';
   若為 NULL 表示禁用；若為路徑則只能讀寫該目錄下檔案。
2. 修改配置（需重啟）：
   [mysqld]
secure_file_priv = /data/export/
3. 目錄權限：
   chown mysql:mysql /data/export
chmod 750 /data/export
4. AppArmor/SELinux（Ubuntu/CentOS）：
   編輯 /etc/apparmor.d/usr.sbin.mysqld 加入：
   /data/export/* rw,
   重載：systemctl reload apparmor

進階技巧

• 平行匯入： 將大檔切分為多個小檔，多線程並行 LOAD DATA
• 暫停索引： 匯入前 ALTER TABLE t DISABLE KEYS，匯入後 ENABLE KEYS（僅 MyISAM 有效，InnoDB 無效）
• 交易控制： 使用 SET autocommit=0 批次提交減少 Redo Log 刷盤
• 資料驗證： 匯入後執行 CHECKSUM TABLE 對比來源

適用場景對照

場景	推薦工具	理由
全庫邏輯備份/遷移	mysqldump	含結構、觸發器、視圖、程序
單表大量資料遷移	INTO OUTFILE / LOAD DATA	速度最快，資源消耗最低
異構資料庫同步	CSV + LOAD DATA	通用格式，易於轉換
增量同步	binlog + Canal/Debezium	即時、低延遲