MySQL 備份與恢復全景指南：邏輯/物理、冷/熱、工具選型與實戰命令

資料庫備份是 DBA 的生命線。本文系統梳理 MySQL 備份的分類體系、主流工具對比、物理文件構成、恢復策略與增量備份原理，構建完整的備份知識圖譜。

備份分類體系

主流工具對比

物理文件構成（物理備份必知）

• 日誌文件（6 類）：error log、binary log、update log、query log、slow query log、InnoDB redo log
• 數據文件：
  • MyISAM：.frm(結構)、.MYD(數據)、.MYI(索引)
  • InnoDB：.ibd(獨立表空間)、ibdata*(共享表空間)
• 複製相關：master.info、relay-log*、relay-log.index
• 系統文件：my.cnf、pid、socket

核心工具實戰命令

mysqldump（邏輯備份標準）

# 全庫備份（單線程、含鎖）
mysqldump -uroot -p --single-transaction --routines --triggers --events --all-databases > full.sql

# 單庫備份
mysqldump -uroot -p db_name > db.sql

# 恢復
mysql -uroot -p < db.sql

xtrabackup / innobackupex（物理熱備標準）

# 完整備份（innobackupex 封裝 xtrabackup，支援 MyISAM）
innobackupex --defaults-file=/etc/my.cnf --user=root --password=xxx /data/backups/

# 增量備份（基於 LSN）
innobackupex --incremental /data/inc_backups/ --incremental-basedir=/data/backups/2023-01-01_00-00-00/

# 恢復準備（apply log）
innobackupex --apply-log /data/backups/2023-01-01_00-00-00/
innobackupex --apply-log --incremental-dir=/data/inc_backups/2023-01-02_00-00-00/ /data/backups/2023-01-01_00-00-00/

# 恢復（停機、拷貝數據目錄、修改權限、啟動）

二進制日誌時間點恢復 (PITR)

# 按時間點
mysqlbinlog --stop-datetime="2023-01-01 10:00:00" /var/log/mysql/bin.000001 | mysql -uroot -p

# 按位置點
mysqlbinlog --stop-position=368312 /var/log/mysql/bin.000001 | mysql -uroot -p

LVM 快照備份（物理備份另類選項）

針對大數據量、極短備份窗口：

# 1. 刷新表並加全局讀鎖
FLUSH TABLES WITH READ LOCK;

# 2. 創建 LVM 快照（秒級）
lvcreate -L10G -s -n mysql_snap /dev/vg0/mysql

# 3. 釋放鎖
UNLOCK TABLES;

# 4. 掛載快照進行拷貝/備份，不影響主庫服務

備份策略建議

1. 核心業務：每日全量 xtrabackup + 每小時增量 + Binlog PITR，RPO < 1 小時，RTO < 30 分鐘。
2. 非核心/小數據量：每日 mysqldump 全量 + Binlog 歸檔。
3. 必須定期演練恢復——未驗證的備份 = 沒有備份。
4. 異地多副本：至少 3 副本、2 種介質、1 異地（3-2-1 原則）。

工具選型無銀彈，關鍵在於「業務容忍度、數據量級、運維能力」三角權衡中找到平衡點。