Linux 下 MySQL 資料備份與還原三大方案：mysqldump、LVM 快照、XtraBackup 完整實戰

MySQL 生產級備份還原三大方案深度實戰

環境：CentOS 6.4 x64、MySQL 5.5.33。備份策略核心：**全量備份 + 二進制日誌增量備份 = 時間點恢復 (PITR)**。

方案一：mysqldump 邏輯備份（通用、相容性最好）

1. 全量備份指令

mysqldump -uroot -p \
  --lock-all-tables \
  --master-data=2 \
  --events --routines \
  --all-databases > /backup/full_$(date +%F).sql

參數解析：--lock-all-tables 全域讀鎖（MyISAM 溫備）、--master-data=2 記錄 Binlog 位置（註解形式）、--events/routines 含排程/函數。

2. 二進制日誌增量備份

# 方法一：導出 SQL
mysqlbinlog mysql-bin.000001 > /backup/binlog_$(date +%F).sql

# 方法二：Flush logs 滾動 + 複製檔案
mysql> FLUSH LOGS;
cp /var/lib/mysql/mysql-bin.000001 /backup/

# 增量範圍導出（指定位置）
mysqlbinlog --start-position=14203 --stop-position=14527 mysql-bin.000001 > /backup/inc.sql

3. 災難恢復演練

# 1. 停服務、清空資料目錄
service mysqld stop
rm -rf /var/lib/mysql/*

# 2. 還原全量
mysql -uroot -p < /backup/full_2024-01-15.sql

# 3. 還原增量
mysql -uroot -p < /backup/inc.sql

# 4. 啟動驗證
service mysqld start

缺點：還原慢、佔空間大、還原後索引需重建、MyISAM 僅溫備。

方案二：LVM 快照近熱備（秒級凍結、適合大庫）

前提：資料目錄在 LVM 邏輯卷上、Binlog 與資料同卷、sync_binlog=1。

操作流程

1. FLUSH TABLES WITH READ LOCK; FLUSH LOGS;
2. 記錄 SHOW MASTER STATUS; 位置
3. lvcreate -L 100M -s -n mysql-snap /dev/vg/mysql
4. UNLOCK TABLES;（鎖定時間極短）
5. 掛載快照：mount /dev/vg/mysql-snap /mnt/snap
6. 複製資料：cp -a /mnt/snap/* /backup/lvm_full/
7. 卸載銷毀快照
8. 後續增量用 mysqlbinlog 同方案一

方案三：Percona XtraBackup 物理熱備（企業級首選）

1. 安裝

yum install -y perl-DBD-MySQL
rpm -ivh percona-xtrabackup-2.4.x.rpm

2. 全量備份

innobackupex --user=root --password=pass /backup/xtra/
# 輸出目錄：/backup/xtra/2024-01-15_10-30-00/

關鍵檔案：xtrabackup_checkpoints(LSN範圍)、xtrabackup_binlog_info(Binlog位置)、backup-my.cnf。

3. 準備（Prepare）階段 —— 必須步驟

innobackupex --apply-log /backup/xtra/2024-01-15_10-30-00/
# 回滾未提交事務、同步已提交事務至資料檔，達到一致狀態

4. 增量備份

# 基於上一次全量/增量
innobackupex --incremental /backup/xtra --incremental-basedir=/backup/xtra/2024-01-15_10-30-00/

5. 增量合併準備

# 全量 prepare (redo-only)
innobackupex --apply-log --redo-only /backup/xtra/full_dir/

# 第一次增量合併
innobackupex --apply-log --redo-only /backup/xtra/full_dir/ --incremental-dir=/backup/xtra/inc1_dir/

# 第二次增量合併
innobackupex --apply-log --redo-only /backup/xtra/full_dir/ --incremental-dir=/backup/xtra/inc2_dir/

# 最後完整 prepare（回滾未提交）
innobackupex --apply-log /backup/xtra/full_dir/

6. 還原

service mysqld stop
rm -rf /var/lib/mysql/*
innobackupex --copy-back /backup/xtra/full_dir/
chown -R mysql:mysql /var/lib/mysql
service mysqld start

7. Binlog 尾部追趕

cat /backup/xtra/inc2_dir/xtrabackup_binlog_info
# mysql-bin.000003  538898
mysqlbinlog --start-position=538898 /var/lib/mysql/mysql-bin.000003 > tail.sql
mysql -uroot -p < tail.sql

三大方案對比總結

建議：核心業務首選 XtraBackup，配合 Binlog 實現 PITR；小型專案或跨版本遷移用 mysqldump；有 LVM 基礎設施可考慮快照方案。