萬盛學電腦網

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

MySQL 數據導入導出實戰:mysqldump 核心參數詳解與最佳實踐

許多開發者初次遷移 MySQL 資料時嘗試寫腳本逐表導出,效率極低。MySQL 內建 mysqldump 才是標準高效工具。本文梳理完整導入導出流程、核心參數速查表、不同引擎備份策略及線上熱備方案。

一、標準導出導入流程(2 分鐘完成)

1. 導出整個資料庫

mysqldump -u root -h 192.168.1.100 -p db_name > dump.sql

2. 目標伺服器建庫

mysql -u root -p -e "CREATE DATABASE db_name CHARACTER SET utf8mb4;"

3. 導入資料

mysql -u root -p db_name < dump.sql

或進入 MySQL Shell 後:

USE db_name;
SOURCE /path/to/dump.sql;

二、mysqldump 核心參數速查表

參數短參數用途與建議
--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 專用,導出時暫停索引更新,導入後重建,大幅加速

三、不同引擎推薦備份命令模板

MyISAM 表(需鎖表保證一致性)

mysqldump -u user -p --default-character-set=utf8 --opt --extended-insert=false \
--triggers -R --hex-blob -x db_name > db_name.sql

InnoDB 表(熱備份,不阻塞寫入)

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 < file
  • 壓縮傳輸:mysqldump ... | gzip | ssh target "gunzip | mysql -u user -p db" 一條龍跨機遷移
  • 增量備份:配合 --flush-logs 定期備份 Binlog,配合全量備份實現 PITR(時間點恢復)
  • 物理備份替代:超大庫(>500GB)建議 Percona XtraBackup 或 MySQL Enterprise Backup

六、總結

mysqldump 是 MySQL 邏輯備份的基石。掌握 --single-transaction(InnoDB 熱備)、--quick(防 OOM)、--master-data(構建從庫)三大核心參數組合,即可應對 90% 導入導出場景。生產環境務必建立「全量+增量+定期演練」的備份體系。

copyright © 萬盛學電腦網 all rights reserved