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

    适用场景

    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_%';

    • 1

    关注 字段： 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

    • 1
    • 2
    • 3
    • 4

    节选了一个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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    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
    • 2
    • 3
    • 4
    • 5
    • 6

    解释：

    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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    详细版可以百度搜索去学习，这里附带一个推荐讲解链接供学习
    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    1. 先根据 UPDATE 锁定指定的数据库表并获取其下 10行数据 （对应本示例是到内容 ### @9=0 这一行）
    ### UPDATE `testdb`.`test_table`

    • 1
    # 注意！需要对字符  ` 进行转义  
# 这里 -E 可以去掉，因为 里面压根没有正则。。。懒得删除了，不碍事的
grep -A 10  -E  "### UPDATE \`testdb\`.\`test_table\`" 1.txt >step_1.txt

    • 1
    • 2
    • 3
    1. 再筛选出 @2=182759 所在行，以及上1行和下7行的内容
    # 注意！需要对字符  ` 进行转义  
# 这里 -E 可以去掉，因为 里面压根没有正则。。。懒得删除了，不碍事的
grep -A 7 -B 1  -E  "@2=182759" step_1.txt >step_2.txt

    • 1
    • 2
    • 3
    1. 使用管道符完善脚本,管道符 | ,可以把管道符左边的数据内容作为右边的数据输入源头。
    grep -A 10  -E  "### UPDATE \`testdb\`.\`test_table\`" 1.txt  | grep -A 7 -B 1  -E  "@2=182759"  >step_2.txt

    • 1
    1. 根据需要，使用文本编辑器和EXCEL处理出最终想要的结果内容。
      使用文本编辑器批量替换点无用的 字符 比如### ；如果想分割结果集，可以借助---替换为想要的内容。

    -The End-

  • 相关阅读:
    Django（3）模型
    【redis】Stream、String 超详细介绍
    黎曼几何与切空间之间的投影
    Linux初始化（下）：从_start到第一个进程
    react native 毛玻璃效果
    能够注入Bean的XXXUtil工具类
    【1++的数据结构】之map与set（二）
    Java的编程之旅44——学生信息管理系统
    9.jenkins安装
    windows PCL1.12+VTK9.1+Qt5.15.2 VS2019 编译 环境搭建
  • 原文地址：https://blog.csdn.net/baixf/article/details/128001558