一、数据库逻辑备份应用案例¶

1.1 项目实战介绍¶

模拟企业生产场景，数据库管理人员误删除了数据库数据信息，通过mysqldump全备的部分数据信息，进行部分数据信息恢复；

再结合binlog日志文件增量数据信息，实现数据库增量数据恢复，最终实现数据库全部数据的完整复原。

1.2 项目实战背景¶

在某某小型企业工作环境时，企业数据库服务数据存储量小于50G，每天会在23点进行前一天数据的全备操作，并已开启binlog功能；

1.3 项目故障说明¶

在某周周三下午14点左右，由于开发人员连接数据库实例错误，导致企业数据库服务生产数据被误删除了，亟待相关人员解决；

1.4 故障发现流程¶

用户发现故障问题出现：这种企业的网站业务管理的技术人员实力是极差的；

运营人员故障问题发现：这种企业的运营人员或产品经理必然是企业的核心；

开发人员故障问题发现：这种企业的开发人员必然是整个企业业务的主导者；

运维人员故障问题发现：这种企业的运维人员必然已经通晓玩转企业的架构；

安全人员故障问题发现：这种企业的安全维护团队必然是整个企业精英团队；

1.5 故障处理思路¶

需要在网站首页或者应用程序首页显示业务端维护页；

检查利用mysqldump命令全备的数据文件、以及查看binlog日志功能是否已经开启；

利用部分全备数据和增量数据完成数据库所有数据复原恢复工作；

数据库数据完整复原恢复进行数据信息核验工作，一般此类工作可以交由相关业务部门进行核验测试；

数据信息核验工作完毕后，可以在此时业务中断状态下，进行一次停机冷备操作，彻底完成一次数据物理备份；

所有相关线上业务进行恢复运行，并进行业务恢复后的功能性测试，一般交由测试人员进行完成；

撤销维护页面通知消息，实现用户可以正常访问。

1.6 项目实战模拟¶

01 模拟时间-某周周一~周二，正常网站用户访问网站进行数据库信息录入

-- 将binlog日志文件进行刷新，创建一个新的日志文件
mysql> flush logs;

-- 模拟创建用户存储数据的数据库信息
mysql> create database mdb;
mysql> use mdb;

-- 模拟创建用户存储数据的数据表信息
mysql> create table t1 (id int);
mysql> create table t2 (id int);

-- 模拟用户向数据表中添加新的数据
mysql> insert into t1 values(1),(2),(3);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;

-- 检查用户创建的数据信息是否生成
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

02 模拟时间-某周周二晚零点，企业数据库管理员进行一次数据库服务数据全备操作

-- 针对此类报错需要在mysqldump时添加--set-gtid-purged=OFF
ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

[root@master ~]# mysqldump -uroot -p123456 -B mdb --set-gtid-purged=OFF --source-data=2 --single-transaction -R -E --triggers --max_allowed_packet=128M >/database_backup/full_nogtid_`date +%F`.sql

[root@master ~]# mysqldump -uroot -p123456 -B mdb  --source-data=2 --single-transaction -R -E --triggers --max_allowed_packet=128M >/database_backup/full_gtid_`date +%F`.sql

以上mysqldump备份中的特殊参数说明：

序号	参数信息	官方说明	解释说明
01	-R	Dump stored routines (functions and procedures)	表示进行数据库存储过程备份
02	-E	Dump events	表示进行数据库事件信息备份
03	--triggers	Dump triggers for each dumped table.	表示进行触发器信息备份

03 模拟时间-某周周二晚零点之后，模拟用户继续访问网站业务产生了增量的数据信息

-- 新增数据信息
mysql> use mdb;
mysql> create table t3 (id int);
mysql> insert into  t3 values(1),(2),(3);
mysql> insert into  t2 values(4),(5),(6);
mysql> commit;

-- 查看数据信息
mysql> select * from mdb.t3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from mdb.t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

04 模拟时间-某周周三下午14点，模拟系统相关技术人员误删除了数据库，并且已紧急跑路

mysql> drop database mdb;

1.7 项目实战复原¶

01 修复操作-查看找寻数据库服务全备数据，并进行全备数据恢复

[root@master ~]# ll /database_backup/full_2025-03-10.sql
-rw-r--r-- 1 root root 3061 Mar 10 15:43 /database_backup/full_2025-03-10.sql

-- 强调说明 强调说明 强调说明，此步骤操作了解作用后，请在后面进行操作，不要在此步骤就进行数据恢复
[root@master ~]# mysql -uroot -p123456
mysql> source  /database_backup/full_2025-03-10.sql

-- 查看全备的数据是否恢复成功
mysql> use mdb;
mysql> show tables;
+--------------------+
| Tables_in_mdb |
+--------------------+
| t1                        |
| t2                        |
+--------------------+
2 rows in set (0.00 sec)

02 修复操作-查看找寻数据库服务增量备份，并进行增量数据恢复

# 检索恢复binlog临界位置
[root@master ~]# vim /database_backup/full_2025-03-10.sql
-- 表示在进行数据恢复操作时，会将gtid1-505的事件信息删除掉，因为在之前备份数据中已经有了1-505的事件数据信息；
-- 因此，从GTID的编号来看，可以从编号505件开始进行数据增量恢复；
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-505';

-- 输出信息表示增量数据的临界点在3306-binlog.000027日志文件的1130位置，同时是备份结束时的位置点；
CHANGE MASTER TO MASTER_LOG_FILE='3306-binlog.000027', MASTER_LOG_POS=1130;

# 检索查看binlog日志文件获取误删除操作前的GTID
-- 具体binlog日志是哪个，以企业具体情况而定，不一定是binlog.000027
[root@master ~]# ll /data/3306/logs/3306-binlog.*
-rw-r----- 1 mysql mysql     1873 Mar 10 15:45 /data/3306/logs/3306-binlog.000027
-rw-r----- 1 mysql mysql      245 Mar 10 15:40 /data/3306/logs/3306-binlog.index

-- 需要将GTID编号10的误删除数据库事件信息忽略，然后再进行数据信息的恢复,观察到结束位置点信息为3fb53cbb-eb7c-11ef-828e-0050562b04b9:507
mysql> show binlog events in '3306-binlog.000027';
| 3306-binlog.000027 | 1664 | Xid            |         6 |        1695 | COMMIT /* xid=4510 */                                               |
| 3306-binlog.000027 | 1695 | Gtid           |         6 |        1772 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:508' |
| 3306-binlog.000027 | 1772 | Query          |         6 |        1873 | drop database mdb /* xid=4515 */                                    |
+--------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+

# 移动迁移binlog文件位置
[root@master ~]# cp /data/3306/logs/3306-binlog.* /database_backup/

# 操作截取binlog文件信息
-- include-gtids是指定前面临界位置点，截取之后的日志文件信息
[root@master ~]# mysqlbinlog --skip-gtids --include-gtids='3fb53cbb-eb7c-11ef-828e-0050562b04b9:505-507'  /data/3306/logs/3306-binlog.000027> /database_backup/add_bin.sql

# 增量恢复binlog数据信息
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
mysql> set sql_log_bin=0;
-- 进行数据信息的全量恢复
mysql> source /database_backup/full_2025-03-10.sql

-- 完成数据信息的增量恢复
mysql> source /database_backup/add_bin.sql

03 修复操作-进行测试核验数据信息是否完全恢复，并进行最终全量备份

# 核验检查恢复后的数据信息
mysql> use mdb;
mysql> show tables;
mysql> select * from t1;
mysql> select * from t2;
mysql> select * from t3;

# 完成核验之后数据完整备份
[root@master ~]# mysqldump -uroot -p -A --source-data=2 --single-transaction -R -E --triggers --max_allowed_packet=128M >/database_backup/full_`date +%F`.sql

说明：数据库数据修复复原完毕后，别忘让开发人员或测试人员进行业务功能测试，最终让运维人员恢复业务上线。

二、数据库逻辑备份痛点分析¶

假设某个企业进行数据库服务的数据备份，将会采用数据库全备方案，每次全备会生成大约50G的数据信息；

并且每次数据库服务进行全备耗时大约15~30分钟，因此如果有需要进行数据恢复时，耗费时间大约3~5小时左右（备份时间的3-5倍）；

但是，在实际生产环境中，只是误删除（误修改）了一个10M大小的数据表，如何进行部分数据信息的快速恢复；

说明：此时需要实现部分单表数据信息恢复时，在实际企业生产环境中，并没有做指定的单表数据信息备份操作；

痛点解决思路：

只能通过已有的全备数据信息，配合已有binlog日志信息，进行指定表数据信息的恢复操作；

基于全备数据信息，可以将指定数据表的建表语句和插入语句提取出来，进行单表数据信息恢复（恢复全备前的数据）；
基于增量日志信息，可以将指定数据表的所有相关事件信息进行截取，进行单表数据信息增量恢复；

处理方法参考：

基于全备数据信息，获取指定数据表的建表语句和插入语句信息：

[root@master ~]# sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `master`/!d;q' /database_backup/full.sql > /database_backup/createtable.sql
-- 获取指定表的建表语句信息；
[root@master ~]# grep -i 'INSERT INTO `master`' /database_backup/full.sql > /database_backup/data_insert.sql
-- 获取指定表的插入语句信息；
[root@master ~]# grep -i 'UPDATE `master`' /database_backup/full.sql > /database_backup/data_delete.sql
-- 获取指定表的修改语句信息；
[root@master ~]# grep -i 'DELETE FROM `master`' /database_backup/full.sql > /database_backup/data_delete.sql
-- 获取指定表的删除语句信息；

基于增量日志信息，获取指定数据表的增量变化的日志数据信息：

[root@master ~]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d 数据库 -t 数据表 --start-file='binlog.00000N'
-- binlog2sql 截取指定单表的binlog数据，进行恢复/分析操作

MySQL逻辑备份案例复盘：故障恢复流程与痛点分析