文档章节

MySQL执行update 语句忘加where条件后使用mysqlbinlog搭配sed命令完美还原

 一清二白三疯
发布于 2017/09/08 23:48
字数 1546
阅读 290
收藏 0

话说吃一堑长一智,之前因为自己一个不小心,在update执行时忘加where 条件,导致全表更新,当时很是着急,花钱找了别人修复。事后自己研究一二,其实也没那么复杂,自己尝试着也可以成功恢复数据,遂记录一下自己成长。

一、首先要确认你的数据库日志格式是row,root用户登陆数据库后,执行命令:

  >   show variables like '%binlog_format%'; 

输入图片说明

mysql数据库日志格式有三种;Statement,Mixed,Row,这里我不做过多介绍,这里需要通过日志恢复数据的日志格式必须是Row。

二、查看是否开启了binlog

> show variables like '%log_bin%';

输入图片说明

我们可以看到log_bin的值为ON,开启状态,OK,我们数据库可以还原。

三、查看log文件

> show  master logs; 

输入图片说明

看到末尾数值最大那个文件就是了,OK,我们先来找到它,如果你不知道文件位置,简单粗暴点:

> find  / -name 'mysqld-bin.000057' ;

找到这个文件,我们单独可以把他拷贝到tmp目录下,然返回到mysqllogbin这个文件路径下,再次之前需要确认一下你误操作的大概时间,因为我们要通过时间范围来搜索日志,执行命令如下:

   > mysqlbinlog  --base64-output=decode-rows  --start-date='2017-09-05 11:05:00' --stop-date='2017-09-05 11:07:00'  /tmp/mysqld-bin.000057 | grep -B 15 '此处替换成要搜索的关键字' 

    -A 15 : 输出符合查询条件的日志后的15行

    -B 15 : 输出符合查询条件的日志前的15行

找到我们误操作的update 语句,记录下sql上面 # at 开头后面的数字(这个标记应该是事务的行号吧),OK,继续执行命令

> mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS  /tmp/mysqld-bin.000057 | sed -n '/# at 124179692/,/COMMIT/p' > /tmp/update.sql

我们将这串事务从# at开始到COMMIT之间的行全部提取出来到update.sql里。

四、至此,我们已经拿到了需要还原的sql语句,根据导出的sql语句进行sed命令替换,还原到修改之前sql语句,命令如下:

> sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' update.sql | sed 's/### //g;s/\/\*.*/,/g' | sed  /@3/s/,//g | sed '/WHERE/{:a;N;/@3/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d'  >  rollback.sql

输入图片说明

这里sed命令乍看起来比较复杂,我们可以将他分成块来看,因为sed命令是按顺序来执行的,上述命令一共由五条sed命令组成,通过管道分隔,下面我们来细说一下这些命令都做了什么

1.分段解析:

第一个sed 命令:

   > sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' update.sql

功能:将where 和set未知对调

命令剖析:

  >    /WHERE/  #包含WHERE

    :a;      #创建一个labela;

    N;       #追加下一个输入行到读取行的末尾,读入到模式空间

    /SET/!ba;   # 如果不是/SET/,返回a,也就是重复读,一直读到/SET/之前(buffer的内容是WHERE\n.......\nSET)
    
  s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/   这块可以分三部分来读

        第1步: 
            s  #替换命令,例如s/a/b  将a替换为b

        第2步:
            \([^\n]*\)\n\(.*\)\n\(.*\) 

            \        #转义字符

            [^\n]* == buffer中的where

            (.*\)    #单符号(.)匹配除换行符以外的单个字符,*同上;

             [^\n]*\  #代表非换行符(回车)开头,*表示匹配零或多个字符

            \n       #换行

        第3步
            \3\n\2\n\1   

            \3  == 内存中的set,第三个括号中的内容

            \2  == 内存中原来where与set之间的内容,第二个括号中的内容

            \1  == 内存中的where,第一个括号中的内容

输入图片说明

_第二个sed 命令: _

 > sed 's/### //g;s/\/\*.*/,/g'

功能:这句做了两个事情1.把字符串### 替换成 空格 2.把/*往后的内容 替换成,

 >    s/### //g        #将### 替换成空串,

    \                #转义字符

    \/\*.*           #匹配/*之后出换行符外所有内容

_第三个sed 命令: _

    > sed  /@3/s/,//g

功能:这句把字符串包含@3的行中的全部(,)换成空格

   >    /@3/     #匹配包含@3的行

    s/,//    #将,替换为空串

    g        #全部替换

_第四个sed 命令: _

   > sed '/WHERE/{:a;N;/@3/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g'

功能:这句做了三件事 1.就是把WHERE 至@3之间的所有逗号,替换成分号 AND 2.#.* 就是把#在的行替换为空格 3.就是把匹配到的COMMIT, 替换为空格

 >    /@1/!ba;s/,/;/g       #将@1及之前的行尾的(,)替换为(;)

        s/#.*//g                    #将#号开头的整行字符替换为空串。

        s/COMMIT,//g          #将(COMMIT,)替换为空行;

第五个sed 命令:

> sed '/^$/d' > rollback.sql

功能:删除缓存中所有的空行。

 >    /^$/      #查找缓存内容中所有的空行

    d         #删除

 >  rollback.sql   #输出缓存中的内容到rollback.sql

对于第3个sed语句中的@3可以替换成你当前表的最大列数@max。

五、将rollback.sql中的@1,@2,@3替换成对应的列名

   > sed  -i -r  '/WHERE/{:a;N;/@3/!ba;s/(@3=.*)/\1\;/g}' rollback.sql

这句是在where语句后加(;),如果后执行这句请将@3换成对应的列名即可。

    > sed -i 's/@1/列1/g;s/@2/列2/g;s/@3/列3/g' rollback.sql

将@1,@2,@3列转换为对应的列名。

输入图片说明

六、数据格式化

至此,我们的sql语句已经成功还原,美中不足的一条sql语句写成很多行,看起来不顺眼,来我们再继续优化下,执行语句:

> cat rollback.sql | tr  "\n" " " > new_rollback.sql

将所有的换行替换成空格,此处用tr命令,因我的数据量比较大,tr执行效率相对较高,也可以用sed命令sed -i ':label;N;s/\n/ /;b label' rollback.sql,效果都是一样的。

  > sed -i 's/\;/ LIMIT 1\;\n/g' new_rollback.sql

在每一个;前面加上 LIMIT 1,后面加上换行符。

> cat  new_rollback.sql

mysql还原

至此,终于完成了理想的效果,导入数据库即可恢复。

作者:三疯

出处:https://my.oschina.net/u/3496194/blog/1532177 您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,转载请注明出处。

© 著作权归作者所有

共有 人打赏支持
粉丝 0
博文 7
码字总数 7116
作品 0
深圳
MySQL binlog的地雷

上周在研究MySQL binlog格式的时候,发现了一些MySQL binlog的风险点,记录一下。 5.1版本后,MySQL引入了基于ROW方式的binlog格式,不同于Statement方式的是,ROW方式记录了变更的内容,而不...

大数据之路
2012/09/03
0
1
MySQL mysqlbinlog 解析出的 SQL 语句被注释是怎么回事

原文出处:潇湘隐者 一网友反馈使用mysqlbinlog解析出的二进制日志中的内容中,有些SQL语句有#注释的情况,这个是怎么回事呢?我们通过实验来了解一下具体细节情况,如下所示,实验环境为5.6...

潇湘隐者
07/27
0
0
mysqlbinlog: unknown variable 'default-character-

今天试用mysqlbinlog竟然报了这个错,无法继续执行,而且mysql也不认为是一个bug,晕! 原因: [client]下的选项会被所有客户端程序读取(但不包括mysqld),但是mysqlbinlog却不能识别default...

有事没事
2015/07/27
0
0
转义字符导致sqlite迁移至mysql少了15w数据

Sqlite迁移至mysql 一、 准备工作 相关sqlite语句 进入sqlite数据库,如数据库名为mobi.db [root@localhost ~]# sqlite3 mobi.db SQLite version 3.6.23.1 Enter ".help" for instructions ......

qwjhq
06/26
0
0
【MySQL】mysqlbinlog_flashback工具使用

简介: mysqlbinlog_back.py 是在线读取row格式的mysqld的binlog,然后生成反向的sql语句的工具。 一般用于数据恢复的目的。 所谓反向的sql语句就是如果是insert,则反向的sql为delete。 如果...

wangkunj
06/30
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

配置Spring的注解支持

声明:本栏目所使用的素材都是凯哥学堂VIP学员所写,学员有权匿名,对文章有最终解释权;凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。 配置Spring的注解支持 以上也提到了使用注解来配...

凯哥学堂
41分钟前
0
0
关于Spring Aop存在的一点问题的思考

在本人前面的文章Spring Aop原理之切点表达式解析中讲解了Spring是如何解析切点表达式的,在分析源码的时候,出现了如下将要讲述的问题,我认为是不合理的,后来本人单纯使用aspectj进行试验...

爱宝贝丶
43分钟前
0
0
JavaScript 概述

JavaScript是面向Web的编程语言。绝大多数现代网站都使用了JavaScript,并且所有的现代Web浏览器——基于桌面系统、游戏机、平板电脑和智能手机的浏览器——均包含了JavaScript解释器。这使得...

Mr_ET
今天
0
0
Java Run-Time Data Areas(Java运行时数据区/内存分配)

Java运行时数据区(内存分配) 本文转载官网 更多相关内容可查看官网 中文翻译可参考 2.5. Run-Time Data Areas The Java Virtual Machine defines various run-time data areas that are use...

lichuangnk
今天
0
0
docker learn :services docker-compose.yml

docker-compose.yml定义了服务的运行参数 version: "3" services: web: # replace username/repo:tag with your name and image details image: hub.c.163.com/dog948453219/friendlyhello d......

writeademo
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部