MySQL 备份与恢复实战：XtraBackup 和 mysqldump 详解

二、实战案例 1：使用 Percona XtraBackup 进行热备份

1. 工具简介

官方站点：https://www.percona.com/

Percona XtraBackup 是一款开源免费的 MySQL 热备份工具，由 Percona 公司开发维护，专为 InnoDB 和 XtraDB 存储引擎优化，具有以下核心优势：

• 非阻塞备份：在备份过程中不阻塞数据库的读写操作
• 热备份能力：无需停止 MySQL 服务即可完成备份
• 增量备份支持：可基于上一次备份进行增量备份，节省空间和时间
• 跨服务器迁移：支持在线表迁移和复制环境搭建
• 低资源占用：备份过程对服务器性能影响小

2. 安装步骤

配置 YUM 仓库

参考：MySQL Linux 版的安装与配置

准备测试数据

mysql> create database company;
mysql> CREATE TABLE company.employee5(
 id int primary key AUTO_INCREMENT not null,
 name varchar(30) not null,
 sex enum('male','female') default 'male' not null,
 hire_date date not null,
 post varchar(50) not null,
 job_description varchar(100),
 salary double(15,2) not null,
 office int,
 dep_id int
);
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100), ('tom','male','20180203','instructor','teach',5500,501,100), ('robin','male','20180202','instructor','teach',8000,501,100), ('alice','female','20180202','instructor','teach',7200,501,100), ('aofa','male','20180202','hr','hrcc',600,502,101), ('harry','male','20180202','hr',NULL,6000,502,101), ('emma','female','20180206','sale','salecc',20000,503,102), ('christine','female','20180205','sale','salecc',2200,503,102), ('zhuzhu','male','20180205','sale',NULL,2200,503,102), ('gougou','male','20180205','sale','',2200,503,102);
mysql> select * from employee5;

验证安装结果

rpm-ql percona-xtrabackup-24

安装 Percona XtraBackup

方式一：

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.5/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

方式三：下载软件压缩包，上传后解压，下载所有解压出的 rpm 包通过本地安装。

unzip percona-xtrabackup24.rpm.zip
cd percona-xtrabackup
yum localinstall -y *.rpm

安装 Percona 仓库：

yum -y install https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.5/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

安装依赖包：

yum install -y yum-utils
yum install mysql-community-libs-compat -y
# 如出现秘钥校验不匹配下载失败加上 --nogpgcheck 选项跳过秘钥验证

3. 完全备份实战

# 创建备份目录
mkdir -p /xtrabackup/full
# 执行完全备份
innobackupex --user=root --password='Abc@666666' /xtrabackup/full
# 查看备份目录结构 (会自动生成以日期为名的备份文件)
ls -la /xtrabackup/full/
# 查看备份生成的二进制日志位置信息（了解）
cat /xtrabackup/full/$(ls -t /xtrabackup/full/ | head -1)/xtrabackup_binlog_info

至此，你的数据库就全部备份了下来，下面环境模拟数据库遭到破坏，需要使用备份来恢复数据。

# 停止 MySQL 服务
systemctl stop mysqld
# 清理数据目录（模拟数据损坏）
rm -rf /var/lib/mysql/*
rm -rf /var/log/mysqld.log

此时就算启动也无法登录数据库服务。

4. 完全恢复实战

# 确保停止 MySQL 服务且清空数据目录（如重启过数据库就在清理一遍，确保数据库是干净的，否则会恢复失败）
systemctl stop mysqld
rm -rf /var/lib/mysql/*
rm -rf /var/log/mysqld.log
# 准备备份（生成回滚日志来指定备份点，确保数据一致性）
innobackupex --apply-log /xtrabackup/full/2026-02-28_15-45-40/
# 恢复备份文件
innobackupex --copy-back /xtrabackup/full/$(ls -t /xtrabackup/full/ | head -1)/
# 修复文件权限
chown -R mysql:mysql /var/lib/mysql
# 启动 MySQL 服务
systemctl start mysqld
# 验证恢复结果
mysql -uroot -p'Abc@666666' -e "SHOW DATABASEES;"

–apply-log 的核心作用：让备份文件「一致化」

innobackupex --apply-log 会模拟 MySQL 启动时的「崩溃恢复流程」，对备份文件做两件关键事：

• 重做（redo）：把 redo log 里的已提交事务，应用到数据文件中（补全已提交但未刷盘的数据）；
• 回滚（undo）：把 undo log 里的未提交事务，从数据文件中回滚（清理未完成的脏数据）。执行完后，备份目录里的文件就变成了「备份时刻的一致性数据」—— 相当于'备份时刻数据库瞬间停服，拿到的干净备份'。

–copy-back 的作用：把一致化的文件恢复到数据库目录

只有经过 --apply-log 处理后的'干净文件'，复制到 MySQL 的数据目录（/var/lib/mysql）后，数据库启动时才会识别到「完整、一致的数据」，正常启动且数据无丢失、无混乱。

来一个通俗一点的例子

把 MySQL 想象成你家包饺子的厨房，你要备份「包好的饺子」（对应数据库里的有效数据）：

备份（innobackupex 直接备份） 你趁家人还在厨房包饺子（数据库不停服、还在读写数据），直接把案板上的饺子全装到盒子里（备份）。 这时候盒子里的饺子有两种：

1. 已经包好、捏紧皮的（对应「已提交的有效数据」，比如用户刚提交的订单）；
2. 只包了一半、皮还没捏紧的（对应「没提交的无效数据」，比如用户刚点下单但还没付款的临时数据）；甚至还有掉出来的饺子馅（对应「临时缓存数据」）。这个盒子里的饺子是「乱的、没法直接吃的」—— 这就是你备份目录里的原始数据。

–apply-log（整理备份 = 把乱饺子整理成能吃的） 你把装饺子的盒子拿到客厅，做两件事：① 把没包完的半成品饺子拆了、馅倒掉（对应「回滚未提交的无效数据」）；② 把捏松的饺子皮捏紧、缺馅的补满（对应「补全已提交但没存好的有效数据」）；最后盒子里只剩「完整、能直接煮的饺子」—— 这就是「一致性备份数据」。这一步就是 --apply-log 的核心：把备份里的「乱数据」整理成「干净可用的完整数据」。

–copy-back（恢复备份 = 把整理好的饺子放回厨房） 你把整理好的完整饺子，端回厨房的盘子里（对应把干净数据复制到 MySQL 的数据目录）。这时家人回来，直接煮饺子就能吃（对应数据库启动后，能正常用、数据不丢、不混乱）。

如果跳过 --apply-log，直接用 --copy-back 把「半成品 + 成品」的乱饺子放回厨房：厨房（数据库）一看全是烂饺子、散馅，根本没法煮（启动失败）；就算勉强煮了，吃的时候不是漏馅就是没熟（数据混乱、丢数据，甚至数据库崩溃）。

一句话总结： –apply-log = 把备份的「乱数据」理干净；–copy-back = 把干净的数据放回数据库；

5. 增量备份实战

测试数据还是用 company 那个数据库。

执行第二次增量备份（周三）

# 插入新数据
INSERT INTO employee5 ( id, name, sex, hire_date, post, job_description, salary, office, dep_id ) VALUES (12, 'zhaodezhu', 'male', CURDATE(), 'hr', 'recruitment', 7500.00, 502, 101);
# 删除数据
DELETE FROM employee5 WHERE name ='alice';
# 修改日期（模拟周三）
date -s "2026-03-04 10:00:00"
# 执行增量备份，基于周二的增量备份
innobackupex --user=root --password='Abc@666666' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2026-03-03_10-03-26
# 查看周三备份目录
ls /xtrabackup/2026-03-02_10-00-41 2026-03-03_10-03-26 2026-03-04_10-02-00

执行第一次增量备份（周二）

# 插入新数据（模拟在周二备份前数据产生的变化）
INSERT INTO employee5 ( id, name, sex, hire_date, post, job_description, salary, office, dep_id ) VALUES (11, 'zhangxiaoxiao', 'female', CURDATE(), 'instructor', 'teach advanced', 8500.00, 501, 100);
# 修改日期（模拟周二）
date -s "2026-03-03 10:00:00"
# 执行增量备份，基于周一的完全备份
innobackupex --user=root --password='Abc@666666' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2026-03-02_10-00-41
# 查看周二备份目录
ls /xtrabackup/2026-03-03_10-03-26

执行基础完全备份（周一）

# 清理旧备份
rm -rf /xtrabackup/*
# 修改日期（模拟周一）
date Mon Mar 2 10:00:02 CST 2026
# 执行完全备份
innobackupex --user=root --password='Abc@666666' /xtrabackup
# 查看周一备份目录
ls /xtrabackup/2026-03-02_10-00-41

6. 增量恢复实战

# 停止 MySQL 服务
systemctl stop mysqld
# 清理数据目录（模拟数据库损坏）
rm -rf /var/lib/mysql/*
# 准备完全备份（使用--redo-only 避免回滚未提交事务）
innobackupex --apply-log --redo-only /xtrabackup/2026-03-02_10-00-41
# 应用周二增量备份
innobackupex --apply-log --redo-only /xtrabackup/2026-03-02_10-00-41 --incremental-dir=/xtrabackup/2026-03-03_10-03-26
# 应用周三增量备份
innobackupex --apply-log --redo-only /xtrabackup/2026-03-02_10-00-41 --incremental-dir=/xtrabackup/2026-03-04_10-02-00
# 最终准备备份（回滚未提交事务）
innobackupex --apply-log /xtrabackup/2026-03-02_10-00-41
# 恢复备份文件
innobackupex --copy-back /xtrabackup/2026-03-02_10-00-41
# 修复文件权限
chown -R mysql:mysql /var/lib/mysql
# 启动 MySQL 服务
systemctl start mysqld
# 验证恢复结果（可以看到我们数据都回来了）
mysql -uroot -p'Abc@666666' -e"select * from company.employee5;"

本次操作完成了周一到周三共三天数据的恢复，整体逻辑如下：

1. 首先对周一的数据做了全量备份（这是整个恢复的基准）；
2. 周二、周三的备份均为「增量备份」—— 仅备份前一天数据的变化部分（周二基于周一、周三基于周二）；
3. 恢复时需按顺序处理备份文件：
   • 先对周一的全量备份执行 --redo-only 预处理（只重做已提交事务，不回滚未提交事务）；
   • 再以周一备份为基础，执行 --redo-only 叠加周二的增量备份（此时周二的增量数据已整合到周一备份中）；
   • 接着继续执行 --redo-only 叠加周三的增量备份；
   • 最后对整合了周一、周二、周三数据的备份文件执行 --apply-log 完整预处理（回滚所有未提交事务）；
4. 完成上述'准备备份'的预处理后，再执行恢复命令，将整理好的一致性数据拷贝回数据库目录，最终完成全量 + 增量的完整恢复。

再用一个通俗的例子讲：

1. 周一的全量备份：你包了一盒饺子（有成品、有半成品），先执行 --redo-only（只补成品，不扔半成品）；
2. 叠加周二增量：把周二新包好的饺子放进周一的盒子里（还是 --redo-only，只补不扔）；
3. 叠加周三增量：把周三新包好的饺子也放进去（依然 --redo-only）；
4. 最终整理：对装了周一 + 周二 + 周三所有成品饺子的盒子，执行 --apply-log（补全捏松的饺子 + 扔掉所有半成品）；
5. 恢复备份：把这盒'干净、完整的饺子'端回厨房（拷贝回数据库），就能正常煮了。

核心总结

• --redo-only = 往饺子盒里'只加新包好的饺子，不扔没包完的'，专为叠加增量准备；
• --apply-log = 把饺子盒整理干净（补好的留下、没包完的扔掉），整理完才能端去煮（恢复）。

如果只想恢复到周二的数据应该怎么操作？ 上面的操作中我们不回滚周三的备份就行，也就是跳过（# 应用周三增量备份）这一步。

三、实战案例 2：使用 mysqldump + binlog 进行备份与恢复

1. 工具优势

• 自动记录二进制日志位置：通过 --master-data 参数自动记录备份时的二进制日志位置
• 数据一致性保证：通过锁表机制或事务隔离确保备份数据的一致性
• 灵活的备份范围：可选择备份整个实例、特定数据库或特定表
• 跨版本兼容性：生成的 SQL 文件可在不同版本的 MySQL 之间使用

2. 备份语法与参数

# 基本语法
mysqldump -h 服务器 -u 用户名 -p 密码 [选项] 数据库名 [表名]> 备份文件.sql

核心参数说明：

• -A, --all-databases：备份所有数据库
• -B, --databases：指定多个数据库进行备份
• --single-transaction：使用事务确保 InnoDB 表的一致性
• --master-data=1|2：记录二进制日志位置（1 为注释，2 为 SQL 语句）
• --opt：启用多种优化选项，提高备份速度
• -R, --routines：备份存储过程和函数
• -F, --flush-logs：备份前刷新二进制日志
• --triggers：备份触发器
• --events：备份事件调度器
• 更多参数可查看工具说明书：mysqldump --help

3. 全库备份实战

我们先准备两个 root 密码：

• 密码一：Abc@666666 当前数据库密码
• 密码二：Qaz@666666 备用密码

测试数据重新准备好，避免和之前的案例混淆。

# 创建备份目录
mkdir -p /backup
# 执行全库备份，包含二进制日志位置信息（备份文件以日期命名）
mysqldump -p'Abc@666666' \
  --all-databases --single-transaction \
  --master-data=2 \
  --flush-logs \
  > /backup/`date +%F-%H`-mysql-all.sql
# 查看备份文件中的二进制日志位置信息
ls /backup/2026-03-05-03-mysql-all.sql
# 我们插入三条数据模仿数据变更行为
INSERT INTO company.employee5( name, sex, hire_date, post, job_description, salary, office, dep_id ) VALUES ('lisa', 'female', CURDATE(), 'instructor', 'teach advanced', 9000.00, 501, 100), ('wangwu', 'male', CURDATE(), 'hr', 'training', 7000.00, 502, 101), ('sunny', 'female', CURDATE(), 'sale', 'sale premium', 18000.00, 503, 102);

观察备份细节

1. LOCK TABLES user WRITE; ——观察各种锁机制，用来保证数据一致性
2. CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; ——二进制日志截断位置。第 22 行
3. 查看备份文件： vim /backup/2026-03-05-03-mysql-all.sql

4. 恢复实战

使用二进制日志恢复增量数据 从二进制日志截取记录 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154 可以看出，二进制日志的截取位置指向日志文件 mysql-bin.000002（对应偏移量 154）。

# 应用二进制日志恢复数据
mysqlbinlog mysql-bin.000001 mysql-bin.000002 --start-position=154 | mysql -p'Abc@666666'

恢复全库备份

# 导入备份文件
mysql -p'Qaz@666666' < /backup/2026-03-05-03-mysql-all.sql
# 刷新权限
mysql -uroot -p'Qaz@666666' -e"FLUSH PRIVILEGES;"

初始化数据库并重置密码

# 启动数据库
systemctl start mysqld
# 获取临时密码（也可用以前的方法修改密码）
TEMP_PASS=$(grep'temporary password' /var/log/mysqld.log |tail-1|awk'{print $NF}')
# 设置新密码（使用第二个密码）
mysqladmin -uroot -p"$TEMP_PASS" password 'Qaz@666666'

停止并清理数据库（模拟数据库损坏或全新数据库）

systemctl stop mysqld
rm -rf /var/lib/mysql/*

备份二进制日志文件

# 复制二进制日志文件到安全位置
cp /var/lib/mysql/*bin* ~/

此时我们可以发现登录数据库时报错，原因是恢复数据时，也恢复了旧的密码，且我们后续增加的三条数据也恢复了。

5. 误操作处理建议（比较少见）

当数据库因误删、误改等操作导致数据损坏或丢失时，可通过二进制日志（binlog）进行数据恢复。以下是标准操作流程及关键注意事项，可最大程度降低数据损失风险。

1. 编辑日志文件，移除误操作使用文本编辑器打开 binlog.sql，按事务块删除误操作，而非仅删除单条 SQL 语句，避免事务不完整导致恢复报错。
   • 核心原则：二进制日志以 at 事务块为单位（如截图中 # at 123、# at 154 等），每个 at 块对应一组关联的数据库操作。若误操作语句归属于 at 219，需从 at 219 起始位置到下一个 at 块（如 at 320）前的所有内容一并删除。
   • 典型场景：若误操作是 DROP DATABASE 或 DELETE 语句，需定位其所属的 at 事务块，建议完整删除该事务内的所有操作，而非仅删除目标语句。

应用处理后的日志

# 应用处理后的二进制日志
mysql -uroot -p'QianFeng@1234' < binlog.sql

提取二进制日志（通过 mysqlbinlog 工具精准提取包含误操作的日志片段，避免全量日志恢复带来的冗余操作。）

# 提取指定时间段的二进制日志
mysqlbinlog --start-datetime='2026-01-01 00:00:00' --stop-datetime='2026-01-02 00:00:00' ~/mysql-bin.0000* > binlog.sql

这种操作虽并不常见，如果遇到这不失为一种办法，能尽可能的挽回损失，但恢复数据也很容易报错。

• 经过我测试发现，只是删除一条很容易在恢复时发生报错，更建议连着 at 一起删除，例如这条语句归属于 at 219，那就从 at 219 删到 at 320 之前。
• 容易发生报错的原因可能是你在删除某条语句或者表时，后续还对相关的表或者语句有些关联的操作语句，恢复的时候虽然有报错但还是恢复成功了的。

四、备份策略最佳实践

1. 备份方式选择

• 大型数据库（>100GB）：优先选择 Percona XtraBackup 进行物理备份
• 中小型数据库：可选择 mysqldump 进行逻辑备份
• 混合策略：结合使用完全备份和增量/差异备份

2. 备份计划制定

• 完全备份：每周或每月执行一次，根据数据重要性和变化频率调整
• 增量备份：每天执行，捕获日常数据变化
• 差异备份：每 2-3 天执行一次，平衡备份速度和恢复效率

3. 备份验证机制

• 定期恢复测试：每月至少进行一次完整的恢复演练
• 备份文件校验：使用 md5sum 等工具验证备份文件完整性
• 监控备份状态：设置备份失败告警机制

4. 备份存储策略

• 异地存储：将备份文件复制到异地服务器，防止本地灾难
• 多介质存储：结合使用磁盘、磁带等多种存储介质
• 加密存储：对敏感数据的备份进行加密处理
• 生命周期管理：制定备份文件保留策略，定期清理过期备份

5. 自动化与监控

• 脚本自动化：使用 Shell 脚本和 crontab 实现备份自动化
• 监控系统集成：将备份状态集成到监控系统，实时掌握备份情况
• 告警机制：设置备份失败、存储空间不足等告警

五、总结

MySQL 数据库备份是 DBA 日常工作中不可或缺的重要环节，直接关系到数据安全和业务连续性。本文通过详细的实战案例，演示了使用 Percona XtraBackup 和 mysqldump 两种工具进行备份和恢复的完整流程，涵盖了从安装配置到实际操作的各个环节。

在实际生产环境中，DBA 应根据数据库规模、业务需求和资源状况，选择合适的备份策略和工具，并建立完善的备份验证和监控机制。定期的备份演练和策略优化，是确保在数据灾难发生时能够快速、完整恢复的关键。

一个好的备份策略不仅要能备份数据，更要能在需要时可靠地恢复数据。只有通过持续的实践和优化，才能构建起真正有效的数据安全保障体系。