mysql 逻辑备份应用案例实战-工具盒子

mysql 逻辑备份应用案例实战

项目实战介绍：

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

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

项目实战背景：

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

项目故障说明：

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

故障发现流程：

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

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

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

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

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

故障处理思路：

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

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

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

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

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

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

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

项目实战模拟：

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

mysql> flush logs;
-- 将binlog日志文件进行刷新，创建一个新的日志文件
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;
mysql> select * from t2;
-- 检查用户创建的数据信息是否生成

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

[root@xiaoQ ~]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/database_backup/full_`date +%F`.sql

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

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;

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

mysql> drop database mdb;

项目实战复原：

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

[root@xiaoQ-01 database_backup]# ll
-rw-r--r-- 1 root root 51256606 11月 26 01:53 full_2022-11-26.sql
[root@xiaoQ ~]# mysql -uroot -p123456 
mysql> source  /database_backup/full_2022-11-26.sql 
-- 强调说明 强调说明 强调说明，此步骤操作了解作用后，请在后面进行操作，不要在此步骤就进行数据恢复

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

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

# 检索恢复binlog临界位置
[root@xiaoQ ~]# vim /database_backup/full_2022-11-26.sql 
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '9d14be39-6423-11ed-bb21-000c2996c4f5:1-6';
-- 表示在进行数据恢复操作时，会将gtid1-6的事件信息删除掉，因为在之前备份数据中已经有了1-6的事件数据信息；
-- 因此，从GTID的编号来看，可以从编号7事件开始进行数据增量恢复；
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=1312;
-- 输出信息表示增量数据的临界点在binlog.000013日志文件的1312位置，同时是备份结束时的位置点；

# 检索查看binlog日志文件获取误删除操作前的GTID
[root@xiaoQ-01 ~]# ll /data/3306/data/binlog.*
-rw-r----- 1 mysql mysql 1833 11月 26 01:55 /data/3306/data/binlog.000013
-rw-r----- 1 mysql mysql     64 11月 26 01:50 /data/3306/data/binlog.index
-- 具体binlog日志是哪个，以企业具体情况而定，不一定是binlog.000013
mysql> show binlog events in 'binlog.000013';
+------------------+-------+------------------+-------------+-----------------+--------------------------------------------------+
| Log_name        | Pos   | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-------+------------------+-------------+-----------------+--------------------------------------------------+
| binlog.000013 | 2060 | Gtid                 |                1 |               2137 | SET @@SESSION.GTID_NEXT= '9d14be39-6423-11ed-bb21-000c2996c4f5:10' |
| binlog.000013 | 2137 | Query              |                1 |               2238 | drop database mdb /* xid=840 */              |
+------------------+-------+------------------+-------------+-----------------+--------------------------------------------------+
-- 需要将GTID编号10的误删除数据库事件信息忽略，然后再进行数据信息的恢复

# 移动迁移binlog文件位置
[root@xiaoQ ~]# cp /var/lib/mysql/binlog.* /databases_backup/

# 操作截取binlog文件信息
[root@xiaoQ ~]# mysqlbinlog --skip-gtids --include-gtids='9d14be39-6423-11ed-bb21-000c2996c4f5:7-9'  /data/3306/data/binlog.000013 >/database_backup/add_bin.sql
 -- include-gtids是指定前面临界位置点，截取之后的日志文件信息

# 增量恢复binlog数据信息
mysql> set sql_log_bin=0;
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
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@xiaoQ ~]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/database_backup/full_`date +%F`.sql

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