从 Mysql binlog 过滤抽取指定条件的SQL语句

最新推荐文章于 2023-03-24 21:37:08 发布
最新推荐文章于 2023-03-24 21:37:08 发布
阅读量3.2k 收藏 2
点赞数
分类专栏： MySql 文章标签： mysql sql grep linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/baixf/article/details/128001558
版权

适用场景

  1. 适用于希望从mysql的binlog恢复数据。
  2. 并且希望快速抽取出自己关注的部分数据表，以及特定数据的SQL操作脚本。可以进行精准恢复。

使用须知

  1. Mysql开启了binlog日志功能，并确实记录了想要查找的SQL语句
  2. 本次使用到了 Linux系统的 grep命令 用于过滤并抽取目标sql脚本到新的文件（效率好高，近2百兆的日志文件瞬间完成过滤写入新文件）。windows 可以尝试安装 grep命令试试，本人没测试过。

开始

获取Mysql的binlog文件

根据mysql的配置找到binlog文件

查找 binlog 文件位置，可以执行语句

SHOW VARIABLES LIKE 'log_%';

关注 字段： log_bin_basename 。后面的value是路径+最后的是binlog文件名，所以访问路径要去掉最后路径分隔符的那个字符串。
例如：D:\mysql-5.6.20-winx64\mysql-5.6.20-winx64\data\mysql-bin
实际binlog文件路径：D:\mysql-5.6.20-winx64\mysql-5.6.20-winx64\data
结尾的myslq-bin 是binlog日志生成的文件名前缀。

解析binlog文件，获取可读的SQL文件

使用命令 mysqlbinlog。只要windows或者Linux上安装过Mysql 命令行可识别此命令就可以用。

# 参数介绍
inlog --base64-output=decode-rows -v /全路径（如果执行命令时不跟binlog文件在一个目录下）/要解析的binlog文件名 --result-file=结果sql脚本文件
# 例子:
inlog --base64-output=decode-rows -v /opt/data/mysql-bin.000118 --result-file=000118.sql

节选了一个update（相对其他操作内容更复杂）语句内容如下。
忽略每行开头的### 符号和用@列的顺序号替代了列名,就是一个标准的 SQL语句，

### UPDATE `testdb`.`test_table`
### WHERE
###   @1=614305
###   @2=182759
###   @3=182749
###   @4=6668
###   @5=1876
###   @6=NULL
###   @7=1668128408
###   @8=1
###   @9=0
### SET
###   @1=614305
###   @2=182759
###   @3=182749
###   @4=6668
###   @5=1876
###   @6=NULL
###   @7=1668136095
###   @8=2
###   @9=0

grep命令简介

本次使用的 grep命令是在Linux环境上，所以把解析好的sql文件我上传到了Linux服务器进行后续操作

windows 安装 grep工具是否完全支持相同语法，以及性能如何我没测试过，有兴趣的小伙伴可以试试，评论解惑下
windows 有类似 grep 的 findstr 命令，但是语法不一样，我也没试过。

grep 命令可以对一堆文本文件或管道符流转来的数据进行筛选（支持正则表达式），并可以将结果输出到控制台或者写入文件里。
加粗的3个地方是本次我们要用到的grep的地方

# 注意参数的大小写！
# 注意参数的大小写！
# 注意参数的大小写！
grep [-abcEFGhHilLnqrsvVwxy][-A<显示命中行之后的行数>][-B<显示命中行之前的行数>][-E<正则表达式>] [要被查找的文件] [>输出到的文件（没有路径就是当前命令所在目录） 
# 样例
grep -B 4 -A 4   -E "(@2=182759|@2=581940|@2=582021)"  mysql-bin.000118 > /opt/binlog_000118.txt

解释：

  1. 查找 符合正则 -E “(@2=182759|@2=581940|@2=582021)”
    @2=182759或@2=182759|或@2=182759 的所在行。
  2. -B 4：命中所在行， 并查找命中行向上数的4行一起作为结果返回
  3. -A 4：命中所在行 ，并输出向下数的4行 内容一起作为结果返回
  4. mysql-bin.000118 ：被查找的文件。（示例由于命令是在跟这个文件所在文件夹下打的命令，如果不是同文件夹，需要打全路径或相对路径）

  5. /opt/binlog_000118.txt :把查找结果覆盖输出到 /opt/binlog_000118.txt 文件里（不需要提前生成该文件。）

Tips：

  1. 如果只是想先在控制台看看输出结果确认是否符合筛选预期，不想把结果写入文件。可以去掉 > /opt/binlog_000118.txt 即可
  2. 如果不想覆盖结果，而是基于文件现有内容继续追加 使用 >> 替换文件前的符号>

实际执行效果

  1. 语句1观察要点：由于原文本含有 2行命中内容，命中了2次。2次命中结果中间用单独的一行---进行了分割
  2. 语句2观察要点：-B 4变为 -B 5。结果却变为一条了！至于原理。。。菜狗我也不懂，只是把现象跟大家说明下。如果相邻的命中结果 -A -B (或者-C 数字,命中行的前后多取 数字 行数，-C 4 等效于 -A 4 -B 4），前后相接了，比如一段文字共20行，-A -B 命中第一次命中包含了 1-4行，第二次命中只需要 命中5-任一行grep会自动合并结果为1条！而不是2条。至于如何强拆为2条？我不会！ 欢迎读者评论赐教谢谢！
# 1.txt 内容是之前截取的update的SQL语句
# 语句1
[root@Master home]# grep  -B 4 -A 4 -E "(@2=182759|@2=581940|@2=582021)" 1.txt
### UPDATE `testdb`.`test_table`
### WHERE
###   @1=614305
###   @2=182759
###   @3=182749
###   @4=6668
###   @5=1876
###   @6=NULL
--
###   @8=1
###   @9=0
### SET
###   @1=614305
###   @2=182759
###   @3=182749
###   @4=6668
###   @5=1876
###   @6=NULL

# 语句2：跟语句1的区别 -B后面参数由4改为5了
[root@Master home]# grep -B 5 -A 4   -E "(@2=182759|@2=581940|@2=582021)" 1.txt
### UPDATE `testdb`.`test_table`
### WHERE
###   @1=614305
###   @2=182759
###   @3=182749
###   @4=6668
###   @5=1876
###   @6=NULL
###   @7=1668128408
###   @8=1
###   @9=0
### SET
###   @1=614305
###   @2=182759
###   @3=182749
###   @4=6668
###   @5=1876
###   @6=NULL

详细版可以百度搜索去学习，这里附带一个推荐讲解链接供学习
linux grep命令使用详解

实操从grep抽取想要的内容到新的文件

已上面模拟的 Update 的sql脚本举例。

场景： 抽取恢复数据表 testdb.test_table，@2=182759相关的UPDATE SQL脚本，恢复Update之前的数据。

思路梳理，首先确定最终目标是抽取以下内容( ### where 所在行之下，### SET 所在行之上部分），之后再去Excel或者其他文本工具处理就好说了。

###   @1=614305
###   @2=182759
###   @3=182749
###   @4=6668
###   @5=1876
###   @6=NULL
###   @7=1668128408
###   @8=1
###   @9=0
  1. 先根据 UPDATE 锁定指定的数据库表并获取其下 10行数据 （对应本示例是到内容 ### @9=0 这一行）
### UPDATE `testdb`.`test_table`

# 注意！需要对字符  ` 进行转义  
# 这里 -E 可以去掉，因为 里面压根没有正则。。。懒得删除了，不碍事的
grep -A 10  -E  "### UPDATE \`testdb\`.\`test_table\`" 1.txt >step_1.txt
  1. 再筛选出 @2=182759 所在行，以及上1行和下7行的内容
# 注意！需要对字符  ` 进行转义  
# 这里 -E 可以去掉，因为 里面压根没有正则。。。懒得删除了，不碍事的
grep -A 7 -B 1  -E  "@2=182759" step_1.txt >step_2.txt
  1. 使用管道符完善脚本,管道符 | ,可以把管道符左边的数据内容作为右边的数据输入源头。
grep -A 10  -E  "### UPDATE \`testdb\`.\`test_table\`" 1.txt  | grep -A 7 -B 1  -E  "@2=182759"  >step_2.txt
  1. 根据需要，使用文本编辑器和EXCEL处理出最终想要的结果内容。
    使用文本编辑器批量替换点无用的 字符 比如### ；如果想分割结果集，可以借助---替换为想要的内容。

-The End-

智鼎在线IT团队
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql binlog 过滤表_mysql日志恢复指定表【从binlog日志过滤恢复指定数据表】
weixin_35953155的博客
01-19 2550
很多时候，MYSQL数据库由于一些误操作，需要恢复指定表，或某个表指定时间点的数据，这个时候需要根据MYSQL的binlog日志做时间点的恢复，下面将介绍这个方案的具体步骤：一、登录数据库刷新binlog1.1)查看当前的binlogshow master status;+---------------------+----------+--------------+---------------...
MySQL_Binlog_Table_Filter:因为mysqlbinlog不能过滤表而创建
06-04
MySQL_Binlog_Table_Filter ####Created because mysqlbinlog can not filter table ###示例 / Example ./myfiler.pl --tables hello,hi --enable-drop --enable-truncate --src src.sql ###参数 --tables 筛选要导出的表名，使用英文逗号","分隔多个表名 --enable-drop 允许DROP语句，可选，默认不允许 --enable-truncate 允许TRUNCATE语句，可选，默认不允许 --src 从指定文件读取 ###操作指南 用mysqlbinlog将binlog文件导出成sql文件，使用-d操作符指定数据库。 使用myfilter.pl处理导
mysql binlog 的配置与使用
xu990128638的专栏
03-24 2190
修改 my.cnf 文件，一般来说都位于 /etc/my.cnf 这边。不指定名称默认就是查看的是 mysqlbinlog.000001的信息。sql_log_bin : 当前会话 是否要记录 binlog。也可以通过mysqlbinlog 工具进行查看。也可以通过时间查看binlog。也可以输出到某个文件中。
处理mysql_binlog日志 过滤掉无用数据
LingMax的博客
09-04 2233
替换文本的正则换行符我为了方便就不用\r\n了 ^#.+ |^[a-zA-Z]+\s*/\*.+\*/;$ |^/\*!\*/;$ |^SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/\*!\*/; |^SET .+;$ |^DELIMITER ;$ |^BEGIN$ |^COMMIT |^/\*.+\*/;$ 处理之前binglog日志 处理后 ...
MySQL Binlog Digger 4.28 + Mysql Binlog分析 + 数据库
08-19
当发生误删、误增、误改时，它可以帮助我们从binlog中快速定位到误操作的重做语句(redo sql)，同时推理出回滚语句(undo sql)。此外，它还可以结合[mysqld]的init-connect参数做mysql 8.0的数据库审计。 ​一. 对dml...
my2sql:解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息以及大事务分析信息
05-13
my2sqlgo版MySQL binlog解析...分析主库执行SQL语句除了支持常规数据类型，对大部分工具不支持的数据类型做了支持，比如json、blob、text、emoji等数据类型sql生成产品性能对比binlog2sql当前是业界使用最广泛MySQL回
MySQL Binlog Digger 4.8.0
12-14
MySQL Binlog Digger是一个基于图形界面的MySQL Binlog挖掘分析工具，可以为数据恢复提供undo sql回滚语句，它免安装，能对在线binlog与离线binlog进行分析，在选定在线binlog(甚至分析到最新日志)或离线binlog日志...
通过binlog得到恢复数据的sql语句.md
11-23
通过binlog得到恢复数据的sql语句.md
mysql数据库恢复 binlog2sql数据库恢复
08-25
mysql服务设置； mysql数据库登录；使用binlog2sql工具进行数据bin查询， 数据恢复、操作撤销等；
show binlog events 命令查看某个binlog日志内容
Johnson_Liang的博客
12-15 980
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 选项解析： IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件) FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算) LIMIT [offset,] 偏移量(不指定就是0) row_count 查询总条数(不指定就是所有行) ...
mysql binlog events_MySQL之show binlog events介绍
weixin_29479863的博客
01-19 184
show binlog events;只查看第一个binlog文件的内容:mysql> show binlog events;+------------------+-----+-------------+------------+-------------+-------------------------------------------------------------------...
使用mysqlbinlog提取出指定表指定时间的操作
weixin_33692284的博客
12-01 3486
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL：从库binlog 使用mysqlbinlog stop-datetime过滤问题
cri5768的博客
07-24 2055
更多主从同步相关可以参考我的《深入理解MySQL主从原理》专栏： 本文是一个朋友问我问题。从库使用mysqlbinlog...
MySQL在ROW模式下通过binlog提取SQ…
__Yoon
01-29 1188
Linux 基于row模式的binlog，生成DML(insert/update/delete)的rollback语句 通过mysqlbinlog -v 解析binlog生成可读的sql文件 提取需要处理的有效sql   "### "开头的行.如果输入的start-position位于某个event group中间，则会导致"无法识别event"错误 将INSERT/U
开启MySQL的binlog日志
热门推荐
happyfly的博客
07-08 12万+
binlog 就是binary log，二进制日志文件，这个文件记录了mysql所有的dml操作。通过binlog日志我们可以做数据恢复，做主住复制和主从复制等等。对于开发者可能对binlog并不怎么关注，但是对于运维或者架构人员来讲是非常重要的。 如何开启mysql的binlog日志呢？ 在my.inf主配置文件中直接添加三行 log_bin=ON log_bin_basenam
读取binlog的delete记录
jc_benben的专栏
12-20 5458
如果不小心删除了一批记录，并提交了，在oracle中可以使用闪回查询，闪回版本查询获取丢失的数据，在mysql中没有很好的办法，不过有一种方式是读取二进制文件 前提条件： 1，开启二进制    查看并开启show variables like '%log_bin%';    如果没有开启，设置加入参数log-bin=mysql-binlog 2，日志格式为ROW    binlog_f
mysql binlog解析出原始sql
最新发布
10-11
MySQL的binlog（二进制日志）是MySQL数据库的一种重要的日志文件，用于记录对数据库的所有修改操作。binlog记录了数据库中每一条修改语句的详细信息，包括对哪个表进行操作、操作类型（INSERT、UPDATE、DELETE等）、修改前后的数据等。 要将binlog解析出原始SQL，可以使用MySQL提供的一些工具和方法。 一种常见的方法是使用MySQL自带的工具"mysqlbinlog"。该工具可以读取和解析MySQL的binlog文件，并将其中的SQL语句转换成原始的SQL文本。使用该工具的命令行语法如下： mysqlbinlog [options] [log_file ...] 其中，[log_file]是要解析的binlog日志文件，[options]是一些可选的参数，用于控制解析的行为。解析后的原始SQL语句会输出到标准输出或指定的输出文件中。 另一种方法是使用第三方工具，例如"pydql"库。pydql是一个Python库，专门用于解析MySQL binlog，并将其转换成原始的SQL语句。通过调用相应的API和函数，可以将binlog文件解析成原始的SQL语句。使用pydql的方式如下： import pydql # 创建一个Binlog解析器对象 binlog_parser = pydql.BinlogParser() # 解析binlog文件 binlog_parser.parse_binlog_file(log_file) # 获取解析后的原始SQL语句 sql_statements = binlog_parser.get_sql_statements() 通过上述方法，可以将MySQL的binlog文件解析成原始的SQL语句，以便进行进一步的分析和处理。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值