MySQL 資料庫備份完整指南：mysqlhotcopy vs mysqldump 選型與實戰

MySQL 備份兩大主流工具 mysqlhotcopy（物理熱備份）與 mysqldump（邏輯備份）的原理、適用場景、優缺點對比與實戰命令範例。

方法一：mysqlhotcopy（僅限 MyISAM）

特點

• 鎖表 → 同步資料至磁碟 → 複製檔案 → 解鎖，速度極快。
• 自動處理鎖定與日誌同步，備份期間服務可讀（寫入阻塞）。
• 僅支援 MyISAM/Archive 引擎，**InnoDB 不支援**。

常用命令

# 備份指定資料庫
mysqlhotcopy -u root -p'password' db1 db2 /backup/dir

# 備份所有資料庫
mysqlhotcopy -u root -p'password' --regexp=".*" /backup/dir

方法二：mysqldump（通用，支援所有引擎）

特點

• 生成 SQL 文字檔，跨版本、跨平台遷移極佳。
• 速度較慢，大資料庫備份/還原耗時長。
• 支援單表、單庫、全庫、遠端同步多種模式。

核心參數與範例

# 基礎備份（含 Blob 處理）
mysqldump -u root -p'password' --hex-blob db1 > db1.sql

# 全庫備份
mysqldump -u root -p'password' --all-databases > all.sql

# 單表備份
mysqldump -u root -p'password' db1 table1 > table1.sql

# 遠端直接同步（無中間檔）
mysqladmin -h remote_host create db1
mysqldump --opt db1 | mysql -h remote_host db1

# SSH 通道遠端同步
ssh remote_host mysqladmin create db1
mysqldump --opt db1 | ssh remote_host mysql db1

關鍵參數說明

• --hex-blob：二進位欄位以十六進位編碼，避免 SQL 檔案格式錯誤，還原 100% 正確。
• --opt：啟用 --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset 組合，預設開啟。
• --single-transaction（InnoDB 專用）：一致性快照備份，**不鎖表**，生產環境首選。
• --master-data=2：輸出 CHANGE MASTER 註解，便於從庫搭建。

選型決策矩陣

最佳實踐建議

1. 純 MyISAM 環境：首選 mysqlhotcopy，配合 cron 定時備份。
2. 含 InnoDB 環境：必須用 mysqldump --single-transaction 或 xtrabackup（參見文章 247001）。
3. 異地備份/遷移：mysqldump | gzip | ssh 管道流式傳輸。
4. 嚴禁直接複製資料目錄檔案（cp -r /var/lib/mysql），極易導致資料不一致、損壞。
5. 定期演練還原流程，驗證備份可用性。