基本語法
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases db1 db2 ...
mysqldump [OPTIONS] --all-databases
核心參數分類
輸出內容控制
| 參數 | 短參 | 說明 |
--no-data | -d | 只導出結構(CREATE TABLE),無數據 |
--no-create-info | -t | 只導出數據(INSERT),無建表語句 |
--complete-insert | -c | INSERT 包含欄位名,便於異構遷移 |
--extended-insert | -e | 多行 INSERT(預設開啟),效能更好 |
--add-drop-table | | 每個 CREATE 前加 DROP TABLE IF EXISTS |
--add-locks | | 每表加 LOCK/UNLOCK TABLES,導入更快 |
--where | -w | 導出滿足條件的行:-w "id>100" |
連接參數
-h, --host=localhost
-P, --port=3306
-u, --user=root
-p, --password[=pass] # 不寫 =pass 會提示輸入
-S, --socket=/path/to/socket
效能與兼容
| 參數 | 說明 |
--quick / -q | 不緩衝結果集直接輸出,**大表必加**,避免記憶體溢出 |
--opt | 組合優化:--quick --add-drop-table --add-locks --extended-insert --lock-tables(預設開啟) |
--single-transaction | InnoDB 熱備份關鍵:啟動事務一致性讀,無鎖導出 |
--master-data=2 | 輸出 CHANGE MASTER TO 註解(從庫同步座標) |
--routines / --triggers / --events | 導出存儲過程/觸發器/事件 |
--hex-blob | 二進位欄位用十六進制導出 |
--set-gtid-purged=OFF | 避免 GTID 相關問題 |
輸出格式
--tab=/path/to/dir # 每表生成 .sql(結構) + .txt(數據),需同機器運行
--fields-terminated-by=,
--fields-enclosed-by="
--lines-terminated-by=\n
實戰範例
1. 生產環境熱備份(推薦)
mysqldump --single-transaction --master-data=2 --routines --triggers \
--events --hex-blob --set-gtid-purged=OFF \
-u backup_user -p db_name > backup_$(date +%F).sql
2. 只導出結構供審核
mysqldump -d -u root -p db_name > schema_only.sql
3. 遷移特定表數據
mysqldump -t -c -u root -p db_name table1 table2 > data_only.sql
4. 跨服務器直接導入
mysqldump --single-transaction -u root -p db_name | \
mysql -h remote_host -u root -p target_db
5. 壓縮備份
mysqldump --single-transaction -u root -p db_name | gzip > backup_$(date +%F).sql.gz
# 解壓導入
gunzip < backup.sql.gz | mysql -u root -p target_db
導入數據
# 建立目標庫
mysqladmin -u root -p create target_db
# 導入
mysql -u root -p target_db < backup.sql
# 或壓縮包導入
gunzip < backup.sql.gz | mysql -u root -p target_db
常見問題
- Max allowed packet:導入大行失敗 →
max_allowed_packet=1G(臨時)
- 字符集亂碼:導出導入指定
--default-character-set=utf8mb4
- 版本不兼容:高版本導入低版本可能失敗,用
--compatible=mysql40
- 權限不足:備份用戶需
SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS