萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 數據導出導入神器 mysqldump 完整參數指南與實戰技巧

基本語法

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-cINSERT 包含欄位名,便於異構遷移
--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-transactionInnoDB 熱備份關鍵:啟動事務一致性讀,無鎖導出
--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
copyright © 萬盛學電腦網 all rights reserved