MySQL mysqldump 邏輯備份完整指南：從基礎導出到跨伺服器遷移、主從同步座標捕獲

mysqldump 是 MySQL 官方邏輯備份工具，產出可重放的 SQL 腳本（CREATE DATABASE、CREATE TABLE、INSERT...），支援跨版本、跨架構遷移。本文系統整理 13 大常用場景、完整參數表、一致性保證機制與生產級最佳實務。

核心語法結構

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases DB1 [DB2 ...]
mysqldump [OPTIONS] --all-databases

13 大實戰場景

1. 導出所有資料庫（含系統庫）

mysqldump -uroot -p --all-databases > /tmp/all.sql

2. 導出指定多個資料庫

mysqldump -uroot -p --databases db1 db2 > /tmp/user.sql

輸出含 CREATE DATABASE IF NOT EXISTS 判斷語句。

3. 導出單庫指定表

mysqldump -uroot -p --databases db1 --tables a1 a2 > /tmp/db1.sql

輸出無 CREATE DATABASE，直接 DROP TABLE、CREATE TABLE、INSERT。

4. 條件導出（單表 WHERE 子句）

mysqldump -uroot -p --databases db1 --tables a1 --where="id=1" > /tmp/a1.sql

5. 導出後產生新 Binlog（-F）

mysqldump -uroot -p --databases db1 -F > /tmp/db1.sql

配合 --master-data 可實現增量備份切分點。

6. 僅導出結構（--no-data / -d）

mysqldump -uroot -p --no-data --databases db1 > /tmp/struct.sql

7. 跨伺服器直接遷移（管道流）

mysqldump -h h1 -uroot -p --databases db1 | mysql -h h2 -uroot -p db2

• 目標庫 db2 必須預先建立。
• -C 啟用壓縮傳輸（低頻寬環境加速）。

8. 追加從庫同步座標（--dump-slave）

# 在從庫執行
mysqldump -uroot -p --dump-slave=1 --databases db1 > /tmp/db1.sql
# 輸出含 CHANGE MASTER TO ... 陳述式

mysqldump -uroot -p --dump-slave=2 --databases db1 > /tmp/db1.sql
# 註解形式輸出

自動啟用 --lock-all-tables，除非指定 --single-transaction。

9. 追加主庫 Binlog 座標（--master-data）

# 在主庫執行
mysqldump -uroot -p --master-data=1 --databases db1 > /tmp/db1.sql
# 輸出 CHANGE MASTER TO ...

mysqldump -uroot -p --master-data=2 --databases db1 > /tmp/db1.sql
# 註解形式

自動啟用 --lock-all-tables，除非指定 --single-transaction。

10. 預設優化組合（--opt，預設開啟）

等同於：

--add-drop-table --add-locks --create-options --quick
--extended-insert --lock-tables --set-charset --disable-keys

可用 --skip-opt 關閉。

11. 交易一致性備份（--single-transaction，InnoDB 專用）

mysqldump -uroot -p --single-transaction --quick --databases db1 > /tmp/db1.sql

• 啟動 BEGIN，可重複讀隔離級別，不阻塞寫入。
• 僅適用多版本引擎（InnoDB）。
• 與 --lock-tables 互斥。
• 大表必加 --quick 避免緩衝區爆滿。

12. 導出存儲過程與函數（--routines / -R）

mysqldump -uroot -p --routines --all-databases > /tmp/all.sql

13. 導出觸發器與事件（預設開啟）

• --triggers（預設開啟，--skip-triggers 關閉）
• --events / -E（需顯式開啟）

完整參數速查表（節錄高頻）

生產級最佳實務

1. InnoDB 必用 --single-transaction --quick --master-data=2：無鎖一致性 + Binlog 座標（註解形式不干擾導入）。
2. 壓縮存儲：mysqldump ... | gzip > backup_$(date +%F).sql.gz
3. 並行導出：mydumper（第三方工具，支援多執行緒、一致性快照）。
4. 驗證備份可用性：定期在測試環境 mysql < backup.sql 恢復驗證。
5. 備份保留策略：每日全量 + 每小時增量（Binlog），保留 30 天全量、7 天增量。

mysqldump 雖非熱備份極速之選，但其「純 SQL、跨版本、人可讀、可編輯」特性使其成為邏輯備份、遷移、審計、開發環境構建的標準工具。掌握 --single-transaction、--master-data、--dump-slave 三大一致性參數組合，是 DBA 從初級進階的關鍵門檻。

1. 上一篇文章： Mac 安裝 MySQL 完整總結：官方 DMG 安裝、Workbench 配置、徹底卸載指令
2. 下一篇文章： MySQL 資料庫核心概念與常用顯示命令全攻略

MySQL 資料庫相關知識

Windows 10 下 MySQL 5.6 解壓縮版安裝配置圖文教程

MySQL Windows 版提供 MSI 安裝包與 ZIP

MySQL REPLACE INTO 深度解析 (一)：核心原理、執行邏輯與隱藏陷阱

REPLACE INTO 是 MySQL 獨有的「插入或更新

MySQL 資料庫核心概念與常用顯示命令全攻略

MySQL 資料庫概述 MySQL 作為全球最流行的開源關係

PHP mysqli 連接 MySQL 資料庫完整實作：連接、查詢、預處理、交易、錯誤處理與安全最佳實務

PHP 連接 MySQL 官方推薦使用 mysqli (My