MySQL 數據備份與恢復全方案：mysqldump、XtraBackup、Binlog、物理拷貝深度對比

備份是數據庫生存底線。本文系統梳理 MySQL 免費備份工具生態，對比邏輯備份、物理備份、Binlog 增量、文件級拷貝四大流派，提供 MyISAM/InnoDB 異構表的差異化策略。

備份工具全景圖

mysqldump 深度參數指南

MyISAM 專用最佳參數

mysqldump -u$user -p$pass \
  --default-character-set=utf8mb4 \
  --opt \
  --extended-insert=false \
  --triggers \
  --routines \
  --hex-blob \
  --lock-all-tables \
  db_name > db_name.sql

• --opt = --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
• --lock-all-tables 全局讀鎖，保證一致性
• --disable-keys 導入時延遲索引構建，大幅提速

InnoDB 專用最佳參數

mysqldump -u$user -p$pass \
  --default-character-set=utf8mb4 \
  --opt \
  --extended-insert=false \
  --triggers \
  --routines \
  --hex-blob \
  --single-transaction \
  --master-data=2 \
  db_name > db_name.sql

• --single-transaction：啟動一致性快照，非阻塞，需 REPEATABLE READ 隔離級
• --master-data=2：輸出 Binlog 位點註釋，便於增量恢復/從庫搭建
• --quick：流式讀取，避免大結果集佔用內存

恢復方式

# 方式一：mysql 客戶端管道
mysql -u$user -p$pass db_name < db_name.sql

# 方式二：SOURCE 命令（需絕對路徑，mysql 進程用戶可讀）
mysql> SOURCE /backup/db_name.sql;

XtraBackup 企業級物理熱備份

# 全量備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=$user --password=$pass

# 增量備份（基於上次 LSN）
xtrabackup --backup --target-dir=/backup/inc_$(date +%F) \
  --incremental-basedir=/backup/full_2024-01-15 --user=$user --password=$pass

# 準備/合併（恢復前必須）
xtrabackup --prepare --target-dir=/backup/full_2024-01-15
xtrabackup --prepare --target-dir=/backup/full_2024-01-15 --incremental-dir=/backup/inc_2024-01-16

# 恢復（需停止 MySQL，datadir 為空）
xtrabackup --copy-back --target-dir=/backup/full_2024-01-15
chown -R mysql:mysql /var/lib/mysql

Binlog 增量備份與時間點恢復 (PITR)

# 1. 啟用 Binlog (my.cnf)
[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7

# 2. 切換 Binlog 產生新文件
FLUSH LOGS;

# 3. 定期備份 Binlog 文件（配合全量備份時間點）
rsync -av /var/lib/mysql/mysql-bin.* /backup/binlog/

# 4. 時間點恢復
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 14:30:00" \
            /backup/binlog/mysql-bin.000012 | mysql -u$user -p$pass db_name

主從架構下的備份最佳實踐

1. 從庫備份：不影響主庫性能，STOP SLAVE SQL_THREAD → 備份 → START SLAVE
2. 備份驗證：建立獨立驗證實例，定期恢復測試，校驗 CHECKSUM TABLE
3. 監控指標：備份耗時、備份大小、恢復演練 RTO/RPO、Binlog 落後量

InnoDB 碎片整理與災難恢復

# 表碎片整理（在線、非阻塞）
ALTER TABLE tbl_name ENGINE=InnoDB;

# 啟動強制恢復（my.cnf，僅讀模式導出數據）
[mysqld]
innodb_force_recovery = 4  # 1-6 遞增激進，4 為安全上限

備份策略模板

沒有驗證的備份 = 沒有備份。建立「備份-驗證-演練」閉環，才是真正的數據安全。