許多開發者初次遷移 MySQL 資料時嘗試寫腳本逐表導出,效率極低。MySQL 內建 mysqldump 才是標準高效工具。本文梳理完整導入導出流程、核心參數速查表、不同引擎備份策略及線上熱備方案。
mysqldump -u root -h 192.168.1.100 -p db_name > dump.sql
mysql -u root -p -e "CREATE DATABASE db_name CHARACTER SET utf8mb4;"
mysql -u root -p db_name < dump.sql
或進入 MySQL Shell 後:
USE db_name;
SOURCE /path/to/dump.sql;
| 參數 | 短參數 | 用途與建議 |
|---|---|---|
--opt | - | 快捷組合(含 --quick、--extended-insert、--lock-tables 等),預設開啟,大幅提升導出導入速度 |
--quick, -q | -q | 大表必加,逐行讀取直接輸出,避免記憶體緩衝全結果集導致 OOM |
--single-transaction | - | InnoDB 熱備份核心,啟動一致性快照,不阻塞寫入,需關閉 --lock-tables |
--lock-all-tables, -x | -x | 全局讀鎖,保證一致性但阻塞所有寫入,僅適合 MyISAM 或維護窗口 |
--lock-tables | - | 鎖定當前導出的表(MyISAM 適用),InnoDB 用 --single-transaction |
--extended-insert | - | 多行合併 INSERT,預設開啟,大幅減小檔案體積與導入時間 |
--complete-insert, -c | -c | 包含欄位名的完整 INSERT,便於閱讀但降低導入效率,不建議常用 |
--hex-blob | - | 二進位欄位用十六進位導出,避免編碼問題,有 BLOB/BINARY 必加 |
--triggers | - | 導出觸發器,預設開啟 |
--routines, -R | -R | 導出存儲過程/函數 |
--no-create-info, -t | -t | 僅導出資料,不含建表語句 |
--no-data, -d | -d | 僅導出結構,不含資料 |
--where | - | 按條件導出部分資料,如 --where="id > 1000" |
--master-data=1|2 | - | 輸出檔案含 CHANGE MASTER TO,1 為註釋 Binlog 位置,2 為註釋掉,用於構建從庫 |
--flush-logs | - | 導出前刷新 Binlog,配合 --master-data 實現增量恢復基點 |
--default-character-set | - | 指定導出字符集,非 latin1 必加,防亂碼 |
--compatible=name | - | 兼容模式:ansi、mysql323、postgresql、oracle 等,跨庫遷移時有用 |
--disable-keys | - | MyISAM 專用,導出時暫停索引更新,導入後重建,大幅加速 |
mysqldump -u user -p --default-character-set=utf8 --opt --extended-insert=false \
--triggers -R --hex-blob -x db_name > db_name.sql
mysqldump -u user -p --default-character-set=utf8 --opt --extended-insert=false \
--triggers -R --hex-blob --single-transaction db_name > db_name.sql
mysqldump -u user -p --default-character-set=utf8 --opt --master-data=1 \
--single-transaction --flush-logs db_name > db_name.sql
導出檔案頂部將包含:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345;
直接在從庫導入即可自動定位同步起點。
| 方式 | 命令 | 優點 | 缺點 |
|---|---|---|---|
| mysql 客戶端管道 | mysql -u user -p db < dump.sql | 標準、流式處理、記憶體佔用低 | 無進度顯示,錯誤中斷難續傳 |
| SOURCE 命令 | SOURCE /path/dump.sql; | 交互式、可見進度 | 需絕對路徑、權限受限、非標準 SQL |
建議:自動化腳本用管道方式;人工操作驗證用 SOURCE。
--tables 導出檔,多進程並行 mysql < filemysqldump ... | gzip | ssh target "gunzip | mysql -u user -p db" 一條龍跨機遷移--flush-logs 定期備份 Binlog,配合全量備份實現 PITR(時間點恢復)mysqldump 是 MySQL 邏輯備份的基石。掌握 --single-transaction(InnoDB 熱備)、--quick(防 OOM)、--master-data(構建從庫)三大核心參數組合,即可應對 90% 導入導出場景。生產環境務必建立「全量+增量+定期演練」的備份體系。