MySQL执行update 语句忘加where条件后使用mysqlbinlog搭配sed命令完美还原
MySQL执行update 语句忘加where条件后使用mysqlbinlog搭配sed命令完美还原
一清二白三疯 发表于1个月前
MySQL执行update 语句忘加where条件后使用mysqlbinlog搭配sed命令完美还原
  • 发表于 1个月前
  • 阅读 67
  • 收藏 0
  • 点赞 0
  • 评论 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
博文 5
码字总数 4926
×
一清二白三疯
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: