适用场景
- 适用于希望从mysql的binlog恢复数据。
- 并且希望快速抽取出自己关注的部分数据表,以及特定数据的SQL操作脚本。可以进行精准恢复。
使用须知
- Mysql开启了binlog日志功能,并确实记录了想要查找的SQL语句
- 本次使用到了 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
解释:
- 查找 符合正则 -E “(@2=182759|@2=581940|@2=582021)”
@2=182759或@2=182759|或@2=182759 的所在行。 - -B 4:命中所在行, 并查找命中行向上数的4行一起作为结果返回
- -A 4:命中所在行 ,并输出向下数的4行 内容一起作为结果返回
- mysql-bin.000118 :被查找的文件。(示例由于命令是在跟这个文件所在文件夹下打的命令,如果不是同文件夹,需要打全路径或相对路径)
/opt/binlog_000118.txt :把查找结果覆盖输出到 /opt/binlog_000118.txt 文件里(不需要提前生成该文件。)
Tips:
- 如果只是想先在控制台看看输出结果确认是否符合筛选预期,不想把结果写入文件。可以去掉
> /opt/binlog_000118.txt
即可- 如果不想覆盖结果,而是基于文件现有内容继续追加 使用
>>
替换文件前的符号>
实际执行效果
- 语句1观察要点:由于原文本含有 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
- 先根据 UPDATE 锁定指定的数据库表并获取其下 10行数据 (对应本示例是到内容
### @9=0
这一行)
### UPDATE `testdb`.`test_table`
# 注意!需要对字符 ` 进行转义
# 这里 -E 可以去掉,因为 里面压根没有正则。。。懒得删除了,不碍事的
grep -A 10 -E "### UPDATE \`testdb\`.\`test_table\`" 1.txt >step_1.txt
- 再筛选出 @2=182759 所在行,以及上1行和下7行的内容
# 注意!需要对字符 ` 进行转义
# 这里 -E 可以去掉,因为 里面压根没有正则。。。懒得删除了,不碍事的
grep -A 7 -B 1 -E "@2=182759" step_1.txt >step_2.txt
- 使用管道符完善脚本,管道符
|
,可以把管道符左边的数据内容作为右边的数据输入源头。
grep -A 10 -E "### UPDATE \`testdb\`.\`test_table\`" 1.txt | grep -A 7 -B 1 -E "@2=182759" >step_2.txt
- 根据需要,使用文本编辑器和EXCEL处理出最终想要的结果内容。
使用文本编辑器批量替换点无用的 字符 比如###
;如果想分割结果集,可以借助---
替换为想要的内容。
-The End-