您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 備份與恢復全景圖：mysqldump、mysqlhotcopy、二進制日志、文件拷貝、rsync 六大方案深度對比

編輯：MySQL 資料庫相關知識

備份是數據庫最後的防線。本文系統對比 6 類主流備份方案的原理、適用場景、優缺點、核心參數、恢復流程，並給出生產級策略建議，助你構建 RPO/RTO 可控的備份體系。

一、方案對比總覽

方案	類型	引擎支持	熱備份	增量	跨版本/異構	速度	典型場景
mysqldump	邏輯 (SQL)	全引擎	✓ (鎖表/事務)	✗ (需配合 Binlog)	✓ 最佳	中等	遷移、異構同步、中小庫全量
mysqlhotcopy	物理 (文件)	僅 MyISAM	✓ (鎖表)	✗	✗	極快	MyISAM 大表本機快速備份
BACKUP TABLE	物理 (文件)	僅 MyISAM	✓	✗	✗	快	舊版本兼容，不推薦新項目
SELECT INTO OUTFILE	邏輯 (文本)	全引擎	✗ (需鎖表)	✗	✓ (文本通用)	中等	數據導出、異構導入、ETL
二進制日志	增量 (事件)	全引擎	✓	✓ 原生	✗ (同版本)	極快	PITR、主從複製、增量備份
文件拷貝 (cp/rsync)	物理 (文件)	全引擎	✗ (需全局鎖/停庫)	✗ (rsync 偽增量)	✗ (同版本/同架構)	極快	大庫冷備份、文件級遷移
Percona XtraBackup	物理 (頁)	InnoDB/MyISAM	✓ (無鎖)	✓	✗	極快	企業級生產首選 (本文未提及但必補充)

二、核心方案深度解析

1. mysqldump —— 通用標準

生產常用參數組合

# MyISAM 全量
mysqldump -u$U -p$P --default-character-set=utf8mb4 \
  --opt --extended-insert=false --triggers -R --hex-blob -x \
  db_name > db_name.sql

# InnoDB 全量 (無鎖一致性)
mysqldump -u$U -p$P --default-character-set=utf8mb4 \
  --opt --extended-insert=false --triggers -R --hex-blob \
  --single-transaction db_name > db_name.sql

# 主從環境建立從庫 (含 Binlog 位置)
mysqldump -u$U -p$P --default-character-set=utf8mb4 \
  --opt --master-data=1 --single-transaction --flush-logs \
  db_name > db_name.sql

關鍵參數解讀

--single-transaction：InnoDB 熱備核心，啟動事務快照，不阻塞寫入。
--master-data=1：輸出 CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...; 直接建立從庫。
--hex-blob：二進制列 (BLOB/BINARY) 以十六進制導出，避免編碼損壞。
--extended-insert=false：單行 INSERT，便於部分恢復/審計，犧牲導入速度。

2. mysqlhotcopy —— MyISAM 極速備份

# 單庫
mysqlhotcopy -u$U -p$P -q db_name /backup/dir/

# 正則批量
mysqlhotcopy -u$U -p$P -q db_name./regex/ /backup/dir/

限制： 僅 Unix/NetWare、僅 MyISAM、需本機執行、需 SELECT/RELOAD/文件讀權限。

3. 二進制日志 —— 增量備份與 PITR 基石

啟用配置

[mysqld]
server-id = 1
log-bin = mysql-bin
log-bin-index = mysql-bin.index
binlog_format = ROW          # 推薦 ROW 格式
expire_logs_days = 7         # 自動清理

增量備份操作

# 刷新日志，切換新 Binlog 文件
FLUSH LOGS;

# 拷貝舊 Binlog 文件到備份存儲
cp /var/lib/mysql/mysql-bin.000001 /backup/binlog/

恢復 (Point-in-Time Recovery)

# 1. 還原全量備份
mysql -u$U -p$P db_name < full_backup.sql

# 2. 重放 Binlog 到指定時間/位置
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 14:30:00" \
            /backup/binlog/mysql-bin.000001 | mysql -u$U -p$P db_name

4. 文件級物理備份 (冷備/停機窗口)

# 1. 全局只讀鎖 + 刷新
FLUSH TABLES WITH READ LOCK;
# 2. 系統級拷貝 (cp/rsync/tar)
rsync -avz /var/lib/mysql/ /backup/mysql_$(date +%F)/
# 3. 解鎖
UNLOCK TABLES;

InnoDB 必須同時備份： ibdata*、ib_logfile*、*.ibd (獨立表空間)。

5. rsync 遠程同步備份

# 推送模式 (需備份端運行 rsync daemon)
rsync -avz --delete /var/lib/mysql/ backup_user@backup_srv::mysql_backup/

# 拉取模式 (需源端運行 rsync daemon)
rsync -avz --delete backup_user@db_srv::mysql_backup/ /local/backup/

注意：rsync 基於 mtime/size 判斷變更，不保證數據庫邏輯一致性，僅適用於冷備份同步或只讀副本同步。

三、備份策略設計 (RPO/RTO 導向)

業務級別	全量頻率	增量頻率	存儲保留	驗證機制
核心交易 (RPO<1min, RTO<30min)	每日 1 次 (低峰)	Binlog 實時流式備份 / XtraBackup 每小時	全量 30 天 + Binlog 7 天	每日自動恢復演練 + 校驗和比對
重要業務 (RPO<1h, RTO<4h)	每日 1 次	XtraBackup 每 4 小時 / Binlog 歸檔	全量 14 天 + 增量 3 天	每周恢復演練
一般業務 (RPO<24h, RTO<24h)	每周 1 次	mysqldump 每日 / Binlog 歸檔	全量 7 天	月度抽樣驗證

四、避坑指南

mysqldump 大表 OOM： 必加 --quick 或 --opt (含 quick)，禁止全量緩存內存。
字符集亂碼： 導出導入全鏈路顯式指定 --default-character-set=utf8mb4。
GTID 環境： 還原後需 RESET MASTER 或 SET GTID_PURGED 避免 GTID 衝突。
權限表遺漏： --all-databases 或額外導出 mysql 系統庫。
備份未驗證 = 無備份： 建立自動化恢復測試流水線 (Jenkins/GitLab CI)，定期跑通全鏈路。

五、總結

沒有銀彈，只有分層組合：物理全量 (XtraBackup/文件拷貝) + 邏輯增量 (Binlog) + 異地歸檔 (rsync/S3) + 定期演練。根據 RPO/RTO 選型，小庫用 mysqldump，大庫上 XtraBackup，核心業務必上 Binlog 流式備份 + PITR 驗證。備份系統本身要高可用、可觀測、可審計。