借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能

2023-10-04 15:36
文章标签 oracle 工具 mysql 功能 轻松 解析 binlog2sql binlog 闪回 借用 再现

本文主要是介绍借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

借用binlog2sql工具轻松解析MySQL的binlog文件

    • 简介
    • 依赖配置
    • 用户权限
    • 选项配置
    • 案例：误UPDATE表数据回滚
    • binlog2sql VS mysqlbinlog

看腻文章了就来听听视频演示吧：https://www.bilibili.com/video/BV1Zj411k7VW/

简介

binlog2sql是美团大众点评开源的一款用于解析binlog的工具。可用于提取操作的SQL及生成回滚SQL。

依赖配置

github项目：
https://github.com/danfengcao/binlog2sql

github打不开可去gitee下载：
https://gitee.com/damned_gentleness/binlog2sql/tree/master/

unzip binlog2sql-master.zip 
cd binlog2sql-master/
# 需要安装的Python依赖
[root@db01 binlog2sql-master]# cat requirements.txt
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13
# 指定使用阿里云的镜像（能连网的方式）
pip install -r requirements.txt -i http://mirrors.aliyun.com/pypi/simple/ --trusted-host mirrors.aliyun.com
  1. 阿里云：http://mirrors.aliyun.com/pypi/simple/
  2. 中国科技大学：https://pypi.mirrors.ustc.edu.cn/simple/
  3. 清华大学：https://pypi.tuna.tsinghua.edu.cn/simple/
  4. 中国科学技术大学：http://pypi.mirrors.ustc.edu.cn/simple/

MySQL server必须设置以下参数:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

用户权限

最小权限集合：

  • select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
  • super/replication client：两个权限都可以，需要执行’SHOW MASTER STATUS’, 获取server端的binlog列表
  • replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
-- 授权语句
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO xxoo;

由于是伪装成slave来获取主的二进制事件，故无需对binlog有可读权限。

先切进python脚步文件（binlog2sql.py）所在目录

[root@dba binlog2sql-master]# cd binlog2sql
[root@dba binlog2sql]# ll
total 36
-rwxr-xr-x 1 root root  7747 Oct 12  2018 binlog2sql.py
-rwxr-xr-x 1 root root 11581 Oct 12  2018 binlog2sql_util.py
-rw-r--r-- 1 root root    92 Oct 12  2018 __init__.py

选项配置

解析出标准SQL

[root@dba binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -proot -dmdb -t t_student --start-file='mysql-bin.000011'
USE mdb;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `t_view` AS select * from heartbeat;
USE mdb;
create table test2 (id int,name text);
USE mdb;
DROP TABLE `test2` /* generated by server */;
USE db_test;
create table tblpky(id int primary key auto_increment,name text);
USE mdb;
create table t_student(id int,name varchar(18),class int,score varchar(18));
INSERT INTO `mdb`.`t_student`(`class`, `score`, `id`, `name`) VALUES (1, '66', 1, 'a'); #start 2418 end 2638 time 2023-02-23 02:22:10
INSERT INTO `mdb`.`t_student`(`class`, `score`, `id`, `name`) VALUES (1, '58', 2, 'b'); #start 2418 end 2638 time 2023-02-23 02:22:10
INSERT INTO `mdb`.`t_student`(`class`, `score`, `id`, `name`) VALUES (2, '86', 3, 'c'); #start 2418 end 2638 time 2023-02-23 02:22:10
INSERT INTO `mdb`.`t_student`(`class`, `score`, `id`, `name`) VALUES (2, '78', 4, 'd'); #start 2418 end 2638 time 2023-02-23 02:22:10
UPDATE `mdb`.`t_student` SET `class`=2, `score`='89', `id`=3, `name`='c' WHERE `class`=2 AND `score`='86' AND `id`=3 AND `name`='c' LIMIT 1; #start 2734 end 2927 time 2023-02-23 02:28:38
DELETE FROM `mdb`.`t_student` WHERE `class`=1 AND `score`='58' AND `id`=2 AND `name`='b' LIMIT 1; #start 3023 end 3201 time 2023-02-23 02:28:55
INSERT INTO `mdb`.`t_student`(`class`, `score`, `id`, `name`) VALUES (1, '48', 5, 'e'); #start 3297 end 3475 time 2023-02-23 02:29:32

参数选项

python binlog2sql.py --help解析模式:
--stop-never 持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。默认False
-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。
--back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。解析范围控制:
--start-file 起始解析文件，只需文件名，无需全路径 。必须。
--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。对象过滤:
-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。
-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
--only-dml 只解析dml，忽略ddl。可选。默认False。
--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

案例：误UPDATE表数据回滚

忘带where条件的误UPDATE整张表

mysql> select * from t_student;
+------+------+-------+-------+
| id   | name | class | score |
+------+------+-------+-------+
|    1 | a    |     1 | 66    |
|    3 | c    |     2 | 89    |
|    4 | d    |     2 | 78    |
|    5 | e    |     1 | 48    |
+------+------+-------+-------+
4 rows in set (0.00 sec)mysql> update t_student set score='failure';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0mysql> select * from t_student;
+------+------+-------+---------+
| id   | name | class | score   |
+------+------+-------+---------+
|    1 | a    |     1 | failure |
|    3 | c    |     2 | failure |
|    4 | d    |     2 | failure |
|    5 | e    |     1 | failure |
+------+------+-------+---------+
4 rows in set (0.00 sec)

找到误操作记录的binlog文件

mysql> show master status\G
*************************** 1. row ***************************File: mysql-bin.000011Position: 3899Binlog_Do_DB: Binlog_Ignore_DB: 
Executed_Gtid_Set: 0ee6241a-f240-11ec-9388-080027be95b2:1-169719
1 row in set (0.00 sec)

根据误操作人提供的大致误操作时间过滤数据

[root@dba binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dmdb -tt_student --start-file='mysql-bin.000011' --start-datetime='2023-02-23 02:36:17' --stop-datetime='2023-02-23 02:38:17'UPDATE `mdb`.`t_student` SET `class`=1, `score`='failure', `id`=1, `name`='a' WHERE `class`=1 AND `score`='66' AND `id`=1 AND `name`='a' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27
UPDATE `mdb`.`t_student` SET `class`=2, `score`='failure', `id`=3, `name`='c' WHERE `class`=2 AND `score`='89' AND `id`=3 AND `name`='c' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27
UPDATE `mdb`.`t_student` SET `class`=2, `score`='failure', `id`=4, `name`='d' WHERE `class`=2 AND `score`='78' AND `id`=4 AND `name`='d' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27
UPDATE `mdb`.`t_student` SET `class`=1, `score`='failure', `id`=5, `name`='e' WHERE `class`=1 AND `score`='48' AND `id`=5 AND `name`='e' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27

可以知道误操作的位置点在3571-3868之间和时间点，再用flashback模式( -B )生成回滚sql，检查回滚sql是否正确

[root@dba binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' -dmdb -tt_student --start-file='mysql-bin.000011' --start-datetime='2023-02-23 02:36:17' --stop-datetime='2023-02-23 02:38:17' -B > tb_student_rb.sql[root@dba binlog2sql]# cat tb_student_rb.sql UPDATE `mdb`.`t_student` SET `class`=1, `score`='48', `id`=5, `name`='e' WHERE `class`=1 AND `score`='failure' AND `id`=5 AND `name`='e' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27
UPDATE `mdb`.`t_student` SET `class`=2, `score`='78', `id`=4, `name`='d' WHERE `class`=2 AND `score`='failure' AND `id`=4 AND `name`='d' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27
UPDATE `mdb`.`t_student` SET `class`=2, `score`='89', `id`=3, `name`='c' WHERE `class`=2 AND `score`='failure' AND `id`=3 AND `name`='c' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27
UPDATE `mdb`.`t_student` SET `class`=1, `score`='66', `id`=1, `name`='a' WHERE `class`=1 AND `score`='failure' AND `id`=1 AND `name`='a' LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27

确认回滚sql语句无误并回滚。登录mysql确认，检查数据回滚成功。

mysql> select * from t_student;
+------+------+-------+---------+
| id   | name | class | score   |
+------+------+-------+---------+
|    1 | a    |     1 | failure |
|    3 | c    |     2 | failure |
|    4 | d    |     2 | failure |
|    5 | e    |     1 | failure |
+------+------+-------+---------+
4 rows in set (0.00 sec)mysql> source /root/binlog2sql-master/binlog2sql/tb_student_rb.sql
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t_student;
+------+------+-------+-------+
| id   | name | class | score |
+------+------+-------+-------+
|    1 | a    |     1 | 66    |
|    3 | c    |     2 | 89    |
|    4 | d    |     2 | 78    |
|    5 | e    |     1 | 48    |
+------+------+-------+-------+
4 rows in set (0.00 sec)

binlog2sql VS mysqlbinlog

限制:

  • mysql server必须开启，离线模式下不能解析
    – 基于BINLOG_DUMP协议来获取binlog内容
    – 需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
  • 参数 binlog_row_image 必须为FULL，暂不支持MINIMAL
  • 解析速度不如mysqlbinlog

优点:

  • 纯Python开发，安装与使用都很简单
  • 自带flashback、no-primary-key解析模式，无需再装补丁
  • flashback模式下，更适合闪回实战
  • 解析为标准SQL，方便理解、筛选
  • 代码容易改造，可以支持更多个性化解析

参考链接：https://www.cnblogs.com/ivictor/p/6418409.html

这篇关于借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/2279。

相关文章

nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析(结合应用场景)

nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析(结合应用场景)

《nginx-t、nginx-sstop和nginx-sreload命令的详细解析(结合应用场景)》本文解析Nginx的-t、-sstop、-sreload命令,分别用于配置语法检... 以下是关于 nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析，结合实际应
阅读更多...
MyBatis中$与#的区别解析

MyBatis中$与#的区别解析

《MyBatis中$与#的区别解析》文章浏览阅读314次，点赞4次，收藏6次。MyBatis使用#{}作为参数占位符时，会创建预处理语句（PreparedStatement），并将参数值作为预处理语句... 目录一、介绍二、sql注入风险实例一、介绍#（井号）：MyBATis使用#{}作为参数占位符时，会
阅读更多...
MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

MySQL 多列 IN 查询之语法、性能与实战技巧(最新整理)

《MySQL多列IN查询之语法、性能与实战技巧(最新整理)》本文详解MySQL多列IN查询,对比传统OR写法,强调其简洁高效,适合批量匹配复合键,通过联合索引、分批次优化提升性能,兼容多种数据库... 目录一、基础语法：多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析
阅读更多...
MySQL中的LENGTH()函数用法详解与实例分析

MySQL中的LENGTH()函数用法详解与实例分析

《MySQL中的LENGTH()函数用法详解与实例分析》MySQLLENGTH()函数用于计算字符串的字节长度,区别于CHAR_LENGTH()的字符长度,适用于多字节字符集（如UTF-8）的数据验证... 目录1. LENGTH()函数的基本语法2. LENGTH()函数的返回值2.1 示例1：计算字符串
阅读更多...
浅谈mysql的not exists走不走索引

浅谈mysql的not exists走不走索引

《浅谈mysql的notexists走不走索引》在MySQL中,​NOTEXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引,下面就来介绍一下mysql的notexists走不走索... 在mysql中，​NOT EXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引。以下
阅读更多...
Python办公自动化实战之打造智能邮件发送工具

Python办公自动化实战之打造智能邮件发送工具

《Python办公自动化实战之打造智能邮件发送工具》在数字化办公场景中,邮件自动化是提升工作效率的关键技能,本文将演示如何使用Python的smtplib和email库构建一个支持图文混排,多附件,多... 目录前言一、基础配置：搭建邮件发送框架1.1 邮箱服务准备1.2 核心库导入1.3 基础发送函数二、
阅读更多...
Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys
阅读更多...
Qt使用QSqlDatabase连接MySQL实现增删改查功能

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构
阅读更多...
基于Python实现一个图片拆分工具

基于Python实现一个图片拆分工具

《基于Python实现一个图片拆分工具》这篇文章主要为大家详细介绍了如何基于Python实现一个图片拆分工具,可以根据需要的行数和列数进行拆分,感兴趣的小伙伴可以跟随小编一起学习一下... 简单介绍先自己选择输入的图片，默认是输出到项目文件夹中，可以自己选择其他的文件夹，选择需要拆分的行数和列数，可以通过
阅读更多...
Python使用pip工具实现包自动更新的多种方法

Python使用pip工具实现包自动更新的多种方法

《Python使用pip工具实现包自动更新的多种方法》本文深入探讨了使用Python的pip工具实现包自动更新的各种方法和技术,我们将从基础概念开始,逐步介绍手动更新方法、自动化脚本编写、结合CI/C... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2