MySQL 資料庫備份完整指南：mysqldump、熱備份與複製策略

定期備份的重要性

資料庫崩潰可能導致災難性損失，定期備份是降低風險的關鍵。MySQL 提供多種備份方式，各有優劣，需根據業務需求選擇最適合的策略。

主要備份方法比較

1. mysqldump 邏輯備份

將資料庫轉儲為 SQL 語句（CREATE TABLE + INSERT），生成可移植的文字檔案。雖然速度較慢，但跨平台相容性極佳，適合中小型資料庫或需遷移異構環境的場景。

常用指令範例：

# 備份整個資料庫並壓縮
mysqldump --opt testdb | gzip > /data/backup/testdb.bak

# 僅備份指定資料表
mysqldump --opt testdb mytable1,mytable2 | gzip > /data/backup/testdb_mytable.bak

# 直接跨伺服器遷移（無中間檔案）
mysqladmin -h remote_host create testdb
mysqldump --opt testdb | mysql -h remote_host testdb

# 透過 SSH 管道遠端備份
ssh remote_host mysqladmin create testdb
mysqldump --opt testdb | ssh remote_host mysql testdb

關鍵參數說明： --opt 會啟用多項優化，包含 --add-drop-table，自動在 CREATE TABLE 前加入 DROP TABLE IF EXISTS，方便重複匯入不報錯。

2. 實體檔案複製（冷/熱備份）

直接複製資料目錄檔案，速度極快，但必須確保備份期間資料庫靜止或唯讀，否則會產生不一致狀態。

操作範例：

# 複製整個資料庫目錄
cp -r db /backup/db

# 僅備份單一資料表
cp table_name.* /backup/db

# 遠端複製（需硬體架構相同或使用可移植表類型）
scp -r db remotehost:/usr/local/mysql/data

3. mysqlhotcopy 工具

Perl DBI 腳本，支援不關閉服務的熱備份，自動處理表鎖定與日誌刷新，確保檢查點同步。適用於 MyISAM 表。

mysqlhotcopy db /backup/

4. BACKUP TABLE / RESTORE TABLE

僅支援 MyISAM，需 FILE 與 SELECT 權限。備份時會鎖定表，多表同時備份可能產生不一致，建議搭配 LOCK TABLES ... READ 使用。

特殊引擎備份注意事項

• InnoDB：需同時備份 .frm、日誌檔、表空間設定檔
• BDB：需備份資料檔與日誌檔

進階策略：主從複本備份

利用 SLAVE STOP 暫停複寫，在從伺服器進行備份，完成後 SLAVE START 恢復同步，完全不影響主庫效能。

資料庫重命名技巧

MySQL 無直接 RENAME DATABASE 指令，可透過 mysqldump 轉儲 → 建立新庫 → 匯入 → 刪除舊庫完成。或直接修改資料目錄名稱（不適用 InnoDB/BDB），並同步更新 mysql 權限表中的 db、tables_priv、columns_priv、host 四張表。