文档章节

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

 一清二白三疯
发布于 2017/09/08 23:48
字数 1546
阅读 241
收藏 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
博文 7
码字总数 7116
作品 0
深圳
MySQL SQL_ERROR 1032解决办法

MySQLSQL_ERROR 1032解决办法 一、缘由:   在主主同步的测试环境,由于业务侧没有遵循同一时间只写一个点的原则,造成A库上删除了一条数据,B库上在同时更新这条数据。 由于异步和网络延时...

xiaocao13140 ⋅ 05/30 ⋅ 0

binlog日志恢复数据操作记录

Mysql之binlog日志说明及利用binlog日志恢复数据操作记录 众所周知,binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定...

xiaocao13140 ⋅ 05/29 ⋅ 0

DBA 必知的 MYSQL 备份与还原方法

一mysqldump 备份结合 binlog 日志恢复 说明:MySQL 备份一般采取全库备份加日志备份的方式,例如每天执行一次全备份,每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份和日...

xiaocao13140 ⋅ 05/21 ⋅ 0

sed工具与正则表达式的使用(shell第四天)

sed工具 【流式编辑器】 —— 非交互,基于模式匹配过滤及修改文本 —— 逐行处理,并将结果输出到屏幕 ——可实现对文本的输出,删除,替换,复制,剪切,导入,导出等各种操作 命令格式: ...

Morning晨丿 ⋅ 04/30 ⋅ 0

9.4/9.5 sed工具(上、下)

9.4-9.5 sed命令 sed是一种流编辑器,它是文本处理中非常中的工具,能够完美的配合正则表达式使用,功能不同凡响。处理时,把当前处理的行存储在临时缓冲区中,称为“模式空间”(pattern s...

Champin ⋅ 2017/11/21 ⋅ 0

mysql六:数据备份、pymysql模块

一、IDE工具介绍 生产环境还是推荐使用mysql命令行,但为了方便我们测试,可以使用IDE工具 下载链接:https://pan.baidu.com/s/1bpo5mqj 掌握: 1. 测试+链接数据库 2. 新建库 3. 新建表,新...

西鼠 ⋅ 05/03 ⋅ 0

Mysql使用 binlog 二进制日志来恢复数据

开启二进制日志 [mysqld] log-bin=mysql-bin 重启数据库服务 mysql的二进制日志解析工具:mysqlbinlog 使用 start-datatime 和 stop-datetime 来限定恢复的范围 使用 start-position 和 stop...

big_cat ⋅ 2016/10/20 ⋅ 0

9.1-9.7 正则三剑客:grep、sed、awk

grep工具 grep是什么 grep命令是一种强大的文本搜索工具,它能使用正则表达式搜索文本,并把匹 配的行打印出来。grep全称是Global Regular Expression Print,表示全局正则表达式版本,它的使...

熊猫头先生 ⋅ 前天 ⋅ 0

MySQL基础【MySQL运维实践】

5.1-MySQL日志系统 什么是日志 日志(log)是一种顺序记录事件流水的文件 记录计算机程序运行过程中发生了什么 多种多样的用途 帮助分析程序问题 分析服务请求的特征、流量等 判断工作是否成功...

yanfeilai528 ⋅ 04/14 ⋅ 0

Mysql的binlog日志详解

Binlog的开启 mysql5.7默认是不开启binlog日志的,具体的开启方式在开启的笔记中查看。 Binlog 配置的查看 binlog开启成功之后,binlog文件的位置可以在my.inf配置文件中查看。也可以在mysql...

细节探索者 ⋅ 05/10 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

6.1 压缩打包介绍 6.2 gzip压缩工具 6.3 bzip2压缩工具 6.4 xz压缩工具

压缩打包介绍 使用压缩工具的好处: 使用压缩文件,不仅可以节省磁盘空间,而且在传输时还能节省网络宽带。 我们通常讲的家用宽带和机房宽带100M是有区别的: 机房宽带的上行和下行都是100M,...

Linux_老吴 ⋅ 33分钟前 ⋅ 0

SpringBoot热部署加持

概述 进行SpringBoot的Web开发过程中,我们很多时候经常需要重启Web服务器才能保证修改的 源代码文件、或者一些诸如xml的配置文件、以及一些静态文件生效,这样耗时又低效。所谓的热部署指的...

CodeSheep ⋅ 40分钟前 ⋅ 0

OSChina 周六乱弹 —— 假如你被熊困到树上

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @小小编辑:推荐歌曲《如果写不出好的和弦就该在洒满阳光的钢琴前一起吃布丁》 《如果写不出好的和弦就该在洒满阳光的钢琴前一起吃布丁》- 谢...

小小编辑 ⋅ 今天 ⋅ 5

vbs 取文件大小 字节

dim namedim fs, s'name = Inputbox("姓名")'msgbox(name)set fs = wscript.createobject("scripting.filesystemobject") 'fs为FSO实例if (fs.folderexists("c:\temp"))......

vga ⋅ 今天 ⋅ 1

高并发之Nginx的限流

首先Nginx的版本号有要求,最低为1.11.5 如果低于这个版本,在Nginx的配置中 upstream web_app { server 到达Ip1:端口 max_conns=10; server 到达Ip2:端口 max_conns=10; } server { listen ...

算法之名 ⋅ 今天 ⋅ 0

Spring | IOC AOP 注解 简单使用

写在前面的话 很久没更新笔记了,有人会抱怨:小冯啊,你是不是在偷懒啊,没有学习了。老哥,真的冤枉:我觉得我自己很菜,还在努力学习呢,正在学习Vue.js做管理系统呢。即便这样,我还是不...

Wenyi_Feng ⋅ 今天 ⋅ 0

博客迁移到 https://www.jianshu.com/u/aa501451a235

博客迁移到 https://www.jianshu.com/u/aa501451a235 本博客不再更新

为为02 ⋅ 今天 ⋅ 0

win10怎么彻底关闭自动更新

win10自带的更新每天都很多,每一次下载都要占用大量网络,而且安装要等得时间也蛮久的。 工具/原料 Win10 方法/步骤 单击左下角开始菜单点击设置图标进入设置界面 在设置窗口中输入“服务”...

阿K1225 ⋅ 今天 ⋅ 0

Elasticsearch 6.3.0 SQL功能使用案例分享

The best elasticsearch highlevel java rest api-----bboss Elasticsearch 6.3.0 官方新推出的SQL检索插件非常不错,本文一个实际案例来介绍其使用方法。 1.代码中的sql检索 @Testpu...

bboss ⋅ 今天 ⋅ 0

informix数据库在linux中的安装以及用java/c/c++访问

一、安装前准备 安装JDK(略) 到IBM官网上下载informix软件:iif.12.10.FC9DE.linux-x86_64.tar放在某个大家都可以访问的目录比如:/mypkg,并解压到该目录下。 我也放到了百度云和天翼云上...

wangxuwei ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部