備份工具對比
| 工具 | 類型 | 適用引擎 | 優點 | 缺點 |
mysqldump | 邏輯備份 (SQL) | 所有 | 跨版本相容、可編輯、支援壓縮 | 大資料庫匯入慢、鎖表影響寫入 |
mysqlhotcopy | 物理備份 (檔案複製) | MyISAM | 速度極快、不影響運行 (僅短暫鎖表) | 僅限 Unix/NetWare、不支援 InnoDB |
BACKUP TABLE | SQL 指令物理備份 | MyISAM | 線上備份 | 不備份索引、還原慢、已棄用 |
SELECT INTO OUTFILE | 資料導出文字檔 | 所有 | 格式靈活、易處理 | 不含結構、需 FILE 權限 |
| 二進制日誌 | 增量備份 | 所有 | 即時、支援時間點復原 | 需規劃保留策略、佔磁碟 |
| 直接複製資料目錄 | 物理冷備份 | 所有 | 最快、最完整 | 需停機或全域讀鎖、無增量 |
mysqldump 核心參數精選
# MyISAM 推薦
mysqldump -u$user -p$pwd --default-character-set=utf8 \
--opt --extended-insert=FALSE --triggers --routines --hex-blob \
--lock-all-tables db_name > backup.sql
# InnoDB 推薦 (熱備份不鎖表)
mysqldump -u$user -p$pwd --default-character-set=utf8 \
--opt --extended-insert=FALSE --triggers --routines --hex-blob \
--single-transaction --quick db_name > backup.sql
--opt = 開啟多項優化預設值。
--quick 大表直接串流輸出,不緩存記憶體。
--single-transaction InnoDB 一致性快照,不阻塞寫入。
--extended-insert=FALSE 每行獨立 INSERT,避免 max_allowed_packet 限制。
還原方式
# 方法 1: mysql 客戶端
mysql -u$user -p$pwd db_name < backup.sql
# 方法 2: SOURCE 指令 (在 mysql shell 內)
SOURCE /path/backup.sql;
壓縮備份與跨機還原
# 備份並壓縮
mysqldump --quick db_name | gzip > backup.sql.gz
# 遠端還原 (解壓管線導入)
gunzip < backup.sql.gz | mysql -h remote_host -u$user -p$pwd db_name
mysqlhotcopy 快速備份 (僅 MyISAM)
# 備份單庫
mysqlhotcopy -h localhost -u user -p pass db_name /backup/dir
# 正則備份多庫
mysqlhotcopy -h localhost -u user -p pass 'db_.*' /backup/dir
# 還原: 直接複製回 datadir 並修正權限
cp -rf /backup/dir/db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name
二進制日誌增量備份
- 啟用:
my.cnf 加入 log-bin=binlog、server-id=1,重啟。
- 切換日誌:
FLUSH LOGS; (產生新 binlog 檔)。
- 備份:複製舊 binlog 檔 +
binlog.index + 從庫的 master.info/relay-log.info。
- 查看:
mysqlbinlog binlog.000001。
- 還原:
mysqlbinlog binlog.000001 | mysql -u$user -p$pwd db_name。
備份策略建議
- 中型系統:首次全備 + 每日增量 (binlog) + 每周全備。
- 核心系統:每日全備 + 每小時增量,採主從架構在從庫備份不影響主庫。
- 定期演練還原,驗證備份可用性。
資料表維護與災難恢復
-- MyISAM
CHECK TABLE tbl; REPAIR TABLE tbl; OPTIMIZE TABLE tbl; ANALYZE TABLE tbl;
-- InnoDB 整理碎片
ALTER TABLE tbl ENGINE=InnoDB;
-- InnoDB 自動恢復 (my.cnf)
innodb_force_recovery = 1 # 1-6 遞增積極度,生產慎用