您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

mysqldump 備份還原與導入導出語句大全詳解 (補充版)

編輯：MySQL 資料庫相關知識

基礎備份與還原命令

# 備份 (導出)
mysqldump -u用戶名 -p密碼 -h主機 數據庫 [表] > 路徑/文件.sql

# 還原 (導入) - 注意：還原用 mysql 而非 mysqldump
mysql -u用戶名 -p密碼 -h主機 數據庫 < 路徑/文件.sql

關鍵糾正： 原文「mysqldump 還原」用法錯誤，**還原必須用 mysql 客戶端**，mysqldump 只能導出。

條件導出與導入

# 按 WHERE 條件導出表部分數據
mysqldump -u用戶名 -p密碼 -h主機 數據庫 表 --where="條件" > 文件.sql
# 例：導出 tag='88' 的記錄
mysqldump -uroot -p1234 dbname mytable --where="tag='88'" > part.sql

# 只導結構 (無數據)
mysqldump -u用戶名 -p密碼 -h主機 數據庫 表 -d > schema.sql
# 例
mysqldump -uroot -p1234 dbname mytable -d > schema.sql

主要參數深度解析

參數	作用	推薦指數
`--compatible=name`	兼容模式：ansi,mysql323,mysql40,postgresql,oracle,mssql,db2,maxdb,no_key_options,no_table_options,no_field_options	⭐⭐ (遷移時)
`--complete-insert, -c`	完整 INSERT 含列名，利於異構導入，但文件大、受 max_allowed_packet 限制	⭐ (慎用)
`--default-character-set=charset`	必加，指定導出字符集 (如 utf8mb4)，否則默認 latin1 導致亂碼	⭐⭐⭐⭐⭐
`--disable-keys`	INSERT 前後加禁用/啟用索引語句，MyISAM 大表導入加速神器	⭐⭐⭐⭐ (MyISAM)
`--extended-insert`	多行合併 INSERT (默認開啟)，關閉用 `--skip-extended-insert`	⭐⭐⭐⭐⭐
`--hex-blob`	二進制列十六進制輸出，有 BLOB/BINARY 必加	⭐⭐⭐⭐
`--lock-all-tables, -x`	全局讀鎖 (FLUSH TABLES WITH READ LOCK)，阻塞所有寫，建立從庫時用	⭐⭐⭐ (特定場景)
`--lock-tables, -l`	鎖定當前導出庫的表 (READ LOCAL)，僅 MyISAM 有效，InnoDB 用 single-transaction	⭐⭐ (MyISAM)
`--no-create-info, -t`	只導數據，不導 CREATE TABLE	⭐⭐⭐
`--no-data, -d`	只導結構，不導數據	⭐⭐⭐
`--opt`	優化組合短參 (默認開啟)，含 add-drop-table,add-locks,create-options,disable-keys,extended-insert,lock-tables,quick,set-charset	⭐⭐⭐⭐⭐
`--quick, -q`	大表必加，流式讀取不載入內存 (默認開啟)	⭐⭐⭐⭐⭐
`--routines, -R`	導出存儲過程/函數	⭐⭐⭐⭐
`--single-transaction`	InnoDB 熱備份核心，啟動事務獲取一致性快照，無阻塞，與 lock-tables 互斥	⭐⭐⭐⭐⭐ (InnoDB)
`--triggers`	導出觸發器 (默認開啟)	⭐⭐⭐⭐
`--master-data=1\|2`	輸出 CHANGE MASTER TO (1=語句, 2=註釋)，需 RELOAD 權限、binlog 開啟，主從搭建/PITR 必用	⭐⭐⭐⭐⭐

實戰備份腳本模板

MyISAM 表備份

/usr/local/mysql/bin/mysqldump \
  -uyejr -pyejr \
  --default-character-set=utf8 \
  --opt \
  --extended-insert=false \
  --triggers -R --hex-blob -x \
  db_name > db_name.sql

InnoDB 表熱備份 (推薦)

/usr/local/mysql/bin/mysqldump \
  -uyejr -pyejr \
  --default-character-set=utf8 \
  --opt \
  --extended-insert=false \
  --triggers -R --hex-blob \
  --single-transaction \
  db_name > db_name.sql

在線備份 + 記錄 Binlog 位置 (主從搭建/PITR)

/usr/local/mysql/bin/mysqldump \
  -uyejr -pyejr \
  --default-character-set=utf8 \
  --opt \
  --master-data=1 \
  --single-transaction \
  --flush-logs \
  db_name > db_name.sql

原理： 瞬間加全局讀鎖 → 讀取 binlog 位置 → 釋放鎖 → 寫入 CHANGE MASTER TO → 繼續備份。僅開始瞬間阻塞，極短。

還原方法對比

方法	命令	優點	缺點
mysql 客戶端	`mysql -u用戶 -p密碼 dbname < file.sql`	標準、穩定、支援大文件	單線程、大文件慢
SOURCE 語法	`mysql> SOURCE /path/file.sql;`	在 mysql 交互式執行	需絕對路徑、權限、非標準 SQL、大文件易卡死
並行導入工具	`mysqlpump` (5.7+), `mydumper/myloader`	多線程、大幅加速	需額外安裝、兼容性測試

常見問題排查

導入報錯 "Unknown collation: 'utf8mb4_0900_ai_ci'"： 目標 MySQL 版本過低 (5.7 以下不支援 0900 排序規則)，導出時加 --compatible=mysql57 或手動修改文件排序規則
導入卡在 "Waiting for table metadata lock"： 目標表被長事務/鎖佔用，檢查 SHOW PROCESSLIST kill 阻塞進程
GTID 模式下導入報錯： 加 --set-gtid-purged=OFF 避免 GTID 衝突
大文件導入中斷： 使用 pv file.sql.gz | gunzip | mysql -u root -p dbname 監控進度，或拆分文件分批導入