MySQL 數據庫導入導出完整指南：mysqldump 與 source 實戰

MySQL 數據庫導入導出完整指南：mysqldump 與 source 實戰

邏輯備份與恢復是 MySQL 運維核心技能。本文系統整理 mysqldump 導出參數、單庫/全庫/結構/數據分類導出、source 導入流程、大文件優化技巧及跨版本遷移注意事項。

一、 導出：mysqldump 核心參數

二、 常用導出命令模板

1. 單庫完整導出（生產標準）

mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  --master-data=2 \
  --set-gtid-purged=OFF \
  --quick \
  db_name > /backup/db_name_$(date +%F_%H%M).sql

2. 全庫導出（含系統庫）

mysqldump -u root -p \
  --single-transaction --routines --triggers --events \
  --all-databases > /backup/full_$(date +%F).sql

3. 只導結構（Schema 遷移/審核）

mysqldump -u root -p -d db_name > schema.sql

4. 只導數據（數據同步/覆蓋）

mysqldump -u root -p -t db_name > data.sql

5. 導出指定表/條件數據

# 指定表
mysqldump -u root -p db_name table1 table2 > tables.sql

# 條件導出（如歸檔 2023 年訂單）
mysqldump -u root -p db_name orders --where="YEAR(created_at) = 2023" > orders_2023.sql

6. 導出並壓縮（節省空間/傳輸）

mysqldump -u root -p --single-transaction db_name | gzip > db_name_$(date +%F).sql.gz

三、 導入：source / mysql 命令

1. 進入 mysql 客戶端導入

mysql -u root -p
# 進入後
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8mb4;
USE db_name;
SOURCE /backup/db_name.sql;

2. 管道直接導入（非交互、腳本友好）

mysql -u root -p db_name < /backup/db_name.sql

# 壓縮文件直接導入
zcat db_name.sql.gz | mysql -u root -p db_name

3. 只導入結構/數據

# 只建表
mysql -u root -p db_name < schema.sql

# 只導數據（需表已存在）
mysql -u root -p db_name < data.sql

四、 大文件導入優化技巧

1. 關閉自動提交、唯一檢查、外鍵檢查：

   SET autocommit=0;
   SET unique_checks=0;
   SET foreign_key_checks=0;
   SOURCE /backup/large.sql;
   COMMIT;
   SET unique_checks=1;
   SET foreign_key_checks=1;

2. 調大相關緩衝：SET GLOBAL max_allowed_packet=1G; SET GLOBAL net_buffer_length=1M;
3. 分文件導入：用 split 將大 SQL 拆分為多個小文件並行導入（需無外鍵依賴）。
4. 使用 LOAD DATA INFILE：對於純數據導入，CSV + LOAD DATA 比 SQL 文件快 10-20 倍。

五、 跨版本遷移避坑指南

六、 物理備份對比（xtrabackup）

邏輯備份（mysqldump）適合中小庫、跨版本、結構變更。大庫（>100GB）建議物理熱備：

# Percona XtraBackup（不阻塞寫入）
xtrabackup --backup --target-dir=/backup/full --user=root --password
# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full
# 恢復準備
xtrabackup --prepare --target-dir=/backup/full
# 複制回數據目錄
rsync -av /backup/full/ /data/mysql/

七、 總結：導出導入決策樹

1. 數據量 < 10GB、需跨版本/結構調整 → mysqldump + source
2. 數據量 > 50GB、同版本、極速恢復 → XtraBackup 物理備份
3. 增量同步/異構同步 → Canal + Kafka + 目標端消費
4. 雲上托管實例 → 云廠商原生備份/恢復/克隆功能