MySQL 匯入匯出命令大全：mysqldump、SELECT INTO OUTFILE、LOAD DATA INFILE

MySQL 匯入匯出命令大全：mysqldump、SELECT INTO OUTFILE、LOAD DATA INFILE

MySQL 資料遷移、備份還原、異構同步的三大核心工具完整指南，含參數詳解、壓縮管道、格式控制、權限需求。

一、mysqldump：邏輯備份標準工具

三種調用模式

# 1. 單庫/指定表
mysqldump [options] db_name [table1 table2...]

# 2. 多庫
mysqldump [options] --databases db1 db2 db3...

# 3. 所有庫
mysqldump [options] --all-databases

常用參數

實戰範例

# 1. 完整備份 (含結構+資料+主從資訊)
mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --events db_name > backup_$(date +%F).sql

# 2. 僅結構
mysqldump -uroot -p -d db_name > structure.sql

# 3. 壓縮管道備份
mysqldump -uroot -p db_name | gzip > backup_$(date +%F).sql.gz

# 4. 加速匯入參數 (需與目標庫配置一致)
mysqldump -uroot -p db_name -e --max_allowed_packet=1048576 --net_buffer_length=16384 > fast_import.sql

# 5. 遠端備份
mysqldump -h 10.0.0.1 -uroot -p db_name > remote_backup.sql

還原方式

# 1. 進入 mysql 後 source
mysql -uroot -p db_name
SOURCE /path/backup.sql;

# 2. 管道導入
mysql -uroot -p db_name < backup.sql

# 3. 解壓管道導入
gunzip -c backup.sql.gz | mysql -uroot -p db_name

二、SELECT ... INTO OUTFILE：資料導出文字檔

權限：需 `FILE` 權限，檔案生成在伺服器端，不可覆蓋既有檔案。

SELECT * 
FROM mysql.user
INTO OUTFILE '/tmp/user_export.txt'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• 僅導出資料，不含結構
• 常搭配 `LOAD DATA INFILE` 異構遷移
• 安全目錄限制：`secure_file_priv` 變數限制可讀寫路徑

三、LOAD DATA INFILE：高速資料導入

權限：需 `FILE` 權限，`LOCAL` 關鍵字表示從客戶端讀取檔案。

LOAD DATA LOCAL INFILE '/tmp/user_export.txt'
INTO TABLE test.user
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 跳過標題行

格式控制參數對照

四、大資料量遷移最佳實務

加速匯入參數調優 (針對 mysqldump 產出檔案)

-- 目標庫臨時調大 (匯入期間)
SET GLOBAL max_allowed_packet = 1G;
SET GLOBAL net_buffer_length = 1M;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;
SET GLOBAL unique_checks = 0;
SET GLOBAL foreign_key_checks = 0;

-- 匯入完成後恢復
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;
SET GLOBAL unique_checks = 1;
SET GLOBAL foreign_key_checks = 1;