mysqlbinlog结合sed命令恢复update时未加where条件之前的数据
一.环境说明
腾讯云机器上自建MySQL 上update操作时,忘加where条件 ,使用mysqlbinlog搭配sed命令完美还原
MySQL版本号:5.6.39;
mysql必须开启binlog,并且mysql的binlog最好是Row模式;
mysql数据库指定字符集位utf8,同时表的字符集也得为utf8,否则在mysqlbinlog 解析出来的sql文件对于中文汉字的会出现乱码,导致最后恢复数据到线上的表中报错。
满足以上条件这样可以极大的保证数据恢复的几率。
当然把控好数据库的权限问题,禁止采用不加where条件的delete 和update语句,以及禁止采用drop,truncate才是从根源保证数据安全行之有效的办法。
前面的几篇博文都有介绍采用第三方的工具binlog-rollback.pl,binlog2sql来还原和恢复数据,其实原理和思路都是一致的。同时在测试使用中发现,这2个工具要求必须是本地数据库服务器安装此2个工具,本地的数据库开启binlog,binlog格式为Row模式并且都是针对本地数据库数据进行恢复的。如果在其他机器上安装binlog-rollback.pl或binlog2sql,并且其把要恢复数据的binlog文件拿到已经安装好binlog-rollback.pl或binlog2sql工具的机器上来恢复数据是会报错的,导致数据恢复失败。
二.采用mysqlbinlog结合sed命令依据binlog日志文件恢复数据
今天咱们介绍update执行时忘加where 条件,导致全表更新,采用mysqlbinlog结合sed命令依据binlog日志文件如何来恢复数据。采用此种方式恢复数据对mysql的服务和binlog文件所在的具体服务设备是没有任何限制的,此种方式恢复数据时更灵活
2.1确定binlog格式以及是否开启binlog
查看mysql的binlog格式:
show variables like ‘%binlog_format%’;
查看是否开启了binlog
show variables like ‘%log_bin%’;
我们可以看到log_bin的值为ON,开启状态,OK, 确保了update误操作前,我的数据库 是开启了binog并且binlog格式是row格式,我的数据库数据是可以还原的。
查看log文件:
show master logs;
2.2创建测试表
给测试表插入数据:
select * from test.zx_scores ;
update更新时忘记加where条件限制:
mysql> update zx_scores set titles=‘班长’;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 12 Changed: 11 Warnings: 0
mysql> select * from zx_scores ;
当前的binlog文件为如下:
提示:把此binlog mysql-bin.000020文件移动到其他的机器上也是可以恢复的
三.腾讯云机器上采用mysqlbinog+sed命令来恢复
采用find查找到此文件在服务器上的位置:
找到这个文件,我们单独可以把他拷贝到tmp目录下,然返回到mysqllogbin这个文件路径下,再次之前需要确认一下你误操作的大概时间,因为我们要通过时间范围来搜索日志,执行命令如下:
找到我们误操作的update 语句,记录下sql上面 # at 开头后面的数字14739(这个标记应该是事务的行号吧),OK,继续执行命令
我们将这串事务从# at 14739开始到COMMIT之间的行全部提取出来到update.sql里。
到此处,我们已经拿到了需要还原的sql语句,根据导出的sql语句进行sed命令替换,还原到修改之前sql语句,命令如下:
** 这里sed命令乍一看起来比较复杂,我们将它分成块来进行分析,因为sed命令是按顺序来执行的,上述命令一共由五条sed命令组成,通过管道分隔,下面我们来细说一下这些命令都做了什么:**
3.1.第一个sed命令作用:
功能:将where 和set位置对调
命令剖析:
/WHERE/ #包含WHERE
s/([^\n])\n(.)\n(.*)/\3\n\2\n\1/ 这块可以分三部分来读
将where 和set位置对调,update.sql文件中其他内容不变
3.2.第二个sed 命令作用:
功能:这句做了两个事情1.把字符串### 替换成 空格 2.把/*往后的内容 替换成,
内容如下:
3.3.第三个sed 命令作用:
功能:这句把字符串包含@7的行中的全部(,)换成空格
替换前文件内容:
替换后的文件内容:
3.4.第四个sed 命令作用:
功能:这句做了三件事 1.就是把WHERE 至@7之间的所有逗号,替换成AND 2.#.* 就是把#在的行替换为空格 3.就是把匹配到的COMMIT, 替换为空格
替换后的文件的内容:
3.5.第五个sed 命令:
提示:对于第3个sed语句中的@7和第四个sed语句中@7可以替换成你当前表zx_score的最大列数@max。
3.6.第六个sed 命令:
这句是在where语句后@7最后一个字段加(;),如果后执行这句请将@7换成对应的列名即可。
将sed6-update.sql中的@1,@2,@3,@4,@5,@6,@7替换成对应的列名
此处采用的是先把@1,@2…@6,@7替换为对应的zx_scores表的列名。
换换后的内容如下:
然后将@7转换为对应的列名day
转换后的内容如下:
到此处数据还原已经完成,直接把sed6-update.sql 数据导入到mysql中即可
四.数据格式化
至此,我们的sql语句已经成功还原,美中不足的一条sql语句写成很多行,看起来不顺眼,来我们再继续优化下,执行语句:
在每一个;前面加上 LIMIT 1,后面加上换行符:
恢复到MySQL
还原成功:
到此处mysqlbinlog结合sed命令恢复数据库数据介绍完毕,欢迎留言一起探讨交流学习