Mysqldump 匯出語法筆記 (MySQL / MariaDB 5.5)

Posted on by Rico

本文整理了 MySQL / MariaDB 5.5 環境下 mysqldump 指令的匯出語法與實務建議，適合日後備查與快速套用。
主要涵蓋單一資料庫、全部資料庫、部分資料表、交易一致性、編碼設定與壓縮匯出等情境。

一、mysqldump 是什麼？

mysqldump 是 MySQL 與 MariaDB 內建的備份工具，用來匯出：

  • 資料表結構 (CREATE TABLE)
  • 資料內容 (INSERT INTO)
  • 儲存程序 (Stored Procedures)
  • 觸發器 (Triggers)
  • 事件 (Events)

匯出結果為純文字 SQL 檔，可直接使用 mysql 或 mariadb 指令匯入還原。

二、基本匯出語法

匯出單一資料庫

mysqldump -u user -p --host=127.0.0.1 --port=3306 \
  --databases dbname > dbname_$(date +%F).sql

匯出所有資料庫

mysqldump -u user -p --all-databases > all_$(date +%F).sql

匯出特定資料表

mysqldump -u user -p dbname table1 table2 > tables_$(date +%F).sql

三、交易一致性與鎖定策略

引數適用引擎說明
--single-transactionInnoDB匯出時不鎖表，資料一致性最佳
--lock-tablesMyISAM匯出前鎖表，避免資料變動
# InnoDB 範例
mysqldump -u user -p dbname --single-transaction --quick > dump.sql

# MyISAM 範例
mysqldump -u user -p dbname --lock-tables > dump.sql

四、常用參數建議

mysqldump -u user -p dbname \
  --single-transaction \
  --quick \
  --routines \
  --events \
  --triggers \
  --default-character-set=utf8 \
  --hex-blob \
  | gzip > dbname_$(date +%F).sql.gz
參數說明
--routines匯出 Stored Procedures / Functions
--events匯出排程事件
--triggers匯出觸發器（預設會匯出，但可保險加上）
--hex-blob將 BLOB / TEXT 資料轉為十六進位，避免亂碼
--quick逐筆讀取，避免記憶體爆掉
--default-character-set=utf8指定匯出檔的字元集
gzip匯出後立即壓縮

五、只匯出結構或只匯出資料

匯出資料表結構（不含資料）

mysqldump -u user -p dbname --no-data > schema.sql

匯出資料內容（不含結構）

mysqldump -u user -p dbname --no-create-info > data.sql

六、條件式匯出（WHERE 子句）

匯出符合條件的部分資料：

mysqldump -u user -p dbname orders \
  --where="order_date >= '2025-01-01'" > orders_2025.sql

七、匯出使用者與權限設定

在 MySQL 5.5 / MariaDB 5.5 中，沒有 mysqlpump，
所以必須直接匯出系統資料庫 mysql：

mysqldump -u root -p --databases mysql > mysql_priv_$(date +%F).sql

⚠️ 若跨不同版本匯入，系統表結構可能不相容。
建議改用 SHOW GRANTS 匯出使用者權限：

mysql -u root -p -e "SHOW GRANTS FOR 'user'@'host';"

八、匯出壓縮檔（gzip）

直接在匯出時壓縮，可節省儲存空間：

mysqldump -u user -p dbname | gzip > dbname_$(date +%F).sql.gz

還原時：

gunzip -c dbname_2025-09-16.sql.gz | mysql -u user -p dbname

九、常見問題與建議

問題原因 / 建議
匯出過慢加上 --quick，逐筆輸出避免緩衝
匯出資料亂碼加上 --default-character-set=utf8
匯出中服務卡住若為 MyISAM，請使用 --lock-tables；InnoDB 用 --single-transaction
匯出 BLOB 資料錯誤加上 --hex-blob 避免編碼問題
匯出時 CPU 飆高加上 --quick 並避免同時執行大型查詢

十、還原指令對照

動作指令
匯入 .sqlmysql -u user -p dbname < dump.sql
匯入 .sql.gz`gunzip -c dump.sql.gz

十一、Windows 系統範例

mysqldump.exe -u user -p dbname --single-transaction --quick ^
  --routines --events --triggers --default-character-set=utf8 ^
  > C:\backup\dbname_%DATE:~0,10%.sql

十二、實務建議

  1. 每日自動備份：可搭配 cron 或 Windows 工作排程。
  2. 多資料庫備份：用 --databases 一次備份多個 DB。
  3. 版本一致性：匯出與匯入端版本差距太大時，建議先升級或降級。
  4. 測試還原：定期在測試環境驗證備份檔可正確還原。

十三、完整範例（壓縮、含程序與事件）

mysqldump -u backupuser -p'abcd123' \
  --single-transaction --quick \
  --routines --events --triggers \
  --default-character-set=utf8 --hex-blob \
  --databases nuface \
  | gzip > /backup/nuface_$(date +%F).sql.gz

十四、結語

mysqldump 雖然是老牌工具，但對於中小型資料庫仍然穩定、可靠。
只要掌握幾個重點參數，就能在不影響服務的情況下完成熱備份。

📌 常用組合建議

mysqldump --single-transaction --quick --routines --events --triggers --default-character-set=utf8

這一行基本上就是 最安全、最實用的標準模板。

📎 延伸閱讀