mysqldump 導入導出數據庫完整指南：參數詳解與實戰場景

mysqldump 導入導出數據庫完整指南：參數詳解與實戰場景

mysqldump 是 MySQL 官方邏輯備份核心工具，支援全庫、多庫、單表、條件導出，並提供豐富參數控制輸出格式、一致性、效能與相容性。本文系統整理常用場景、核心參數表與最佳實踐。

一、常用操作場景速查

三種調用模式

mysqldump [options] db_name [tables]
mysqldump [options] --databases db1 [db2 ...]
mysqldump [options] --all-databases

二、核心參數分類詳解

備份一致性與鎖控制

• --single-transaction：InnoDB 熱備必備，啟動事務快照，不阻塞寫入（需配合 --quick）
• --lock-all-tables / -x：全局讀鎖，適用 MyISAM
• --lock-tables / -l：逐庫鎖表，無法保證跨庫一致性
• --master-data=[1|2]：記錄 Binlog 位置，支援 PITR（時間點恢復），1=CHANGE MASTER 語句，2=註釋形式
• --flush-logs：備份前切換 Binlog，配合 --master-data 使用

輸出格式優化

• --opt：預設開啟，等同 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
• --extended-insert / -e：多行 INSERT，大幅縮小檔案體積並加速導入
• --quick / -q：流式讀取大表，不緩存全量結果集
• --disable-keys / -k：導入前禁用索引，導入後重建，MyISAM 大表加速神器
• --hex-blob：二進制欄位以十六進制輸出，避免編碼問題
• --complete-insert / -c：含欄位名的完整 INSERT，便於異構遷移

過濾與相容性

• --where='condition'：條件導出，如 --where="id > 100"
• --compatible=mysql40|postgresql|oracle：輸出相容目標資料庫語法
• --no-create-db / -n：不輸出 CREATE DATABASE
• --no-create-info / -t：僅導出資料
• --no-data / -d：僅導出結構
• --routines / -r：導出存儲過程/函數（5.1.2+）
• --triggers：導出觸發器（預設開啟）

三、生產環境最佳實踐範本

InnoDB 線上熱備 + PITR 準備

mysqldump --all-databases \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --routines \
  --triggers \
  --hex-blob \
  --quick \
  --compress \
  > full_backup_$(date +%F).sql

大表分表導出腳本思路

# 按主鍵範圍分批導出，避免長事務
mysqldump -u root -p --quick --where="id BETWEEN 1 AND 100000" db1 big_table > part1.sql

遠程直接同步（無中間檔案）

mysqldump -h src_host -u root -p --single-transaction db1 | mysql -h dst_host -u root -p db1

四、導入效能優化技巧

• 導入前關閉 Binlog：SET sql_log_bin=0;（需 SUPER 權限）
• 導入後重建索引：配合 --disable-keys 參數
• 大檔案分批導入：split -l 100000 backup.sql part_ 逐個 source
• 使用 mysql -e "source /path/backup.sql" db_name 非交互式導入