当前位置： 首页 > news >正文

南宁网站推广手段,口碑好的做网站公司,好设计购物网站,精准客源推广引流借用binlog2sql工具轻松解析MySQL的binlog文件 简介依赖配置用户权限选项配置案例#xff1a;误UPDATE表数据回滚binlog2sql VS mysqlbinlog 看腻文章了就来听听视频演示吧#xff1a;https://www.bilibili.com/video/BV1Zj411k7VW/ 简介 binlog2sql是美团大众点评开源的一… 借用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依赖

[rootdb01 binlog2sql-master]# cat requirements.txt PyMySQL0.7.11 wheel0.29.0 mysql-replication0.13

指定使用阿里云的镜像能连网的方式

pip install -r requirements.txt -i http://mirrors.aliyun.com/pypi/simple/ –trusted-host mirrors.aliyun.com阿里云http://mirrors.aliyun.com/pypi/simple/中国科技大学https://pypi.mirrors.ustc.edu.cn/simple/清华大学https://pypi.tuna.tsinghua.edu.cn/simple/中国科学技术大学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所在目录 [rootdba binlog2sql-master]# cd binlog2sql [rootdba 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 [rootdba binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -proot -dmdb -t t_student –start-filemysql-bin.000011 USE mdb; CREATE ALGORITHMUNDEFINED DEFINERroot% 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 class2, score89, id3, namec WHERE class2 AND score86 AND id3 AND namec LIMIT 1; #start 2734 end 2927 time 2023-02-23 02:28:38 DELETE FROM mdb.t_student WHERE class1 AND score58 AND id2 AND nameb 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 scorefailure; 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)根据误操作人提供的大致误操作时间过滤数据 [rootdba binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -proot -dmdb -tt_student –start-filemysql-bin.000011 –start-datetime2023-02-23 02:36:17 –stop-datetime2023-02-23 02:38:17UPDATE mdb.t_student SET class1, scorefailure, id1, namea WHERE class1 AND score66 AND id1 AND namea LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27 UPDATE mdb.t_student SET class2, scorefailure, id3, namec WHERE class2 AND score89 AND id3 AND namec LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27 UPDATE mdb.t_student SET class2, scorefailure, id4, named WHERE class2 AND score78 AND id4 AND named LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27 UPDATE mdb.t_student SET class1, scorefailure, id5, namee WHERE class1 AND score48 AND id5 AND namee LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27可以知道误操作的位置点在3571-3868之间和时间点再用flashback模式( -B )生成回滚sql检查回滚sql是否正确 [rootdba binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -proot -dmdb -tt_student –start-filemysql-bin.000011 –start-datetime2023-02-23 02:36:17 –stop-datetime2023-02-23 02:38:17 -B tb_student_rb.sql[rootdba binlog2sql]# cat tb_student_rb.sql UPDATE mdb.t_student SET class1, score48, id5, namee WHERE class1 AND scorefailure AND id5 AND namee LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27 UPDATE mdb.t_student SET class2, score78, id4, named WHERE class2 AND scorefailure AND id4 AND named LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27 UPDATE mdb.t_student SET class2, score89, id3, namec WHERE class2 AND scorefailure AND id3 AND namec LIMIT 1; #start 3571 end 3868 time 2023-02-23 02:36:27 UPDATE mdb.t_student SET class1, score66, id1, namea WHERE class1 AND scorefailure AND id1 AND namea 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