利用binlog2sql 工具进行MySQL 数据恢复

需求： 如何从日志文件中恢复单库、单表、或者部分行数据信息

利用binlog2sql 工具进行MySQL 数据恢复

利用binlog2sql工具可以处理上面的企业需求，此软件是利用python语言开发的，主要用来处理binlog日志信息；

从软件应用方面来说主要包含两个核心功能

- 可以友好的展示或者管理二进制日志信息（binlog），进而可以过滤出单独表的信息，甚至表中指定行的信息；
- 可以快速的实现DML操作语句的闪回功能，即实现通过日志信息翻转方式，进行数据信息的恢复；

说明：binlog2sql工具是模拟了一个从库，进行日志信息分析，需要保证数据库服务启动状态，且不支持离线方式分析日志内容；

数据库异常恢复情况环境准备：

# 下载第三方日志分析工具
[root@xiaoq ~]# cd /opt/
[root@xiaoq ~]# git clone https://github.com/danfengcao/binlog2sql.git
[root@xiaoq ~]# cd /opt/binlog2sql
-- 此工具在mariadb中可以通过打补丁方式，进行部署安装；但是在mysql 8.0中暂时还没有集成，需要单独安装

# 部署第三方工具运行环境
[root@xiaoq ~]# yum install -y python3
[root@xiaoq ~]# pip3 install -r requirments.txt
[root@xiaoq ~]# pip3 show pymysql
[root@xiaoq ~]# pip3 install --upgrade pymysql    （此步骤可以忽略）
-- 以上pip3下载软件缓慢，可以优化pip3下载源
-- 下载源优化方法：https://developer.aliyun.com/mirror/pypi?spm=a2c6h.13651102.0.0.3e221b11H9Q7La

# 在指定数据库中创建多个数据表
mysql> use test1;
mysql> create table t11 (id int);
mysql> insert into t11 values (1),(2);
mysql> commit;

数据库日志信息工具分析查看：（解析日志事件SQL）

[root@xiaoq binlog2sql]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t11 --start-file='binlog.000003'
INSERT INTO `test1`.`t11`(`id`) VALUES (1); #start 4704 end 4954 time 2022-11-21 23:47:51 gtid 
INSERT INTO `test1`.`t11`(`id`) VALUES (2); #start 4704 end 4954 time 2022-11-21 23:47:51 gtid 
-- 表的数据信息导出后，可以直接复制命令信息恢复，或者导出sql文件进行导入恢复；

数据库模拟异常情况破坏操作：

# 在指定数据库的相应数据表中做修改操作
mysql> use test1;
mysql> update t1 set id=10 where id=1;
mysql> commit;

# 在指定数据库的相应数据表中做删除操作
mysql> use test1;
mysql> delete from t1 where id=3;
mysql> commit;

数据库日志信息工具分析查看：（解析日志事件SQL）

[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid 
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid 
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid 

# 只想查看删除操作信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- sql-type参数只能过滤DML类型语句信息，一般常见过滤的是 insert update delete

# 只想查看修改操作信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid

# 只想查看插入操作信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid

数据库日志信息工具回滚操作：（生成指定事件回滚语句-闪回操作）

假设在某个企业的应用场景中，有3000万行数据，占用200G的存储空间，其中误删除了10行数据信息，请问如何进行恢复数据？

 误删除操作语句反转操作
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- 获取删除操作语句信息

[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003' -B
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- 在获取删除操作语句命令后加 -B 参数，正好获得了反转语句的操作信息

# 误修改操作语句反转操作
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid 
-- 获取修改操作语句信息

[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003' -B
UPDATE `test1`.`t1` SET `id`=1 WHERE `id`=10 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
-- 在获取修改操作语句命令后加 -B 参数，正好获得了反转语句的操作信息

# 误插入操作语句反转操作
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid 
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid 
-- 获取插入操作语句信息

[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003' -B
DELETE FROM `test1`.`t1` WHERE `id`=4 LIMIT 1; #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
DELETE FROM `test1`.`t1` WHERE `id`=2 LIMIT 1; #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
DELETE FROM `test1`.`t1` WHERE `id`=1 LIMIT 1; #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
-- 在获取插入操作语句命令后加 -B 参数，正好获得了反转语句的操作信息

欢迎来撩 ： 汇总all