Mysql通过binlog日志恢复误更新数据

爪洼h

已于 2024-06-11 20:38:37 修改

阅读量376

点赞数 4

文章标签： mysql 数据库

于 2024-06-11 20:24:43 首次发布

本文链接：https://blog.csdn.net/m0_48480302/article/details/139606616

版权

今天在测试环境的时候不小心更新了整表的数据：

sql语句为： update erp_out_order set order_status="0" where 5021

一时眼花漏写"id="，点击运行手快，关键这个sql执行成功了，一下子将表中2万多条测试数据全部更新了，后悔莫及呀😫，

下面是我通过logbin恢复数据的过程

条件：1、mysql服务开启了binlog，且模式为row；

2、需要进行数据恢复,mysql服务肯定是不能停止的了;

第一步：先查看当前binlog文件是那个文件，并且记录下来，同时为了方便更好的定位问题，及时让binlog文件写入到新的一个文件中，使用flush生成新的文件

show master logs;

flush logs;

第二步：先讲当前记录的binlog日志备份一份（防止我们在恢复过程中不规范操作损坏源文件）

第三步：查找无更新操作sql的位置

show binlog events in "mysql-bin.000381";

两个起始pos点和结束pos点中间的所有Update_rows都是对表记录的修改。

第四步：通过mysqlbinlog工具对binlog日志进行解释并保存到parseA.txt文件中

/usr/bin/mysqlbinlog --base64-output=decode-rows -vv --start-position=164503 --stop-position=2147161 mysql-bin.000381 > parseA.txt

这是未过滤的parseA文件的其中一条记录：

### UPDATE `erp_cc`.`erp_out_order`
### WHERE
###   @1=7967 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='D240607985000029' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @3='P240515D4910000063' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @5=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @6=39800 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @7=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @8='18829479963' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @9=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @10=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @11=1380097 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @12=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
###   @13='sgs' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @14=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @15=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @16='茶香里128罐装SS英红九号250g*2+满香园红茶（2114）【众春茗】250g*2（赠品：古树红茶（英红九号）10g*1）' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @17=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @18=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @19=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @20=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @21=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @22=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @23=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @24=NULL /* TINYINT meta=0 nullable=1 is_null=1 */
###   @25=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @26='sgs' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @27='18829479963' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @28='2024-01-12 18:22:38' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @29=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @30=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @31=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @32=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @33='山西省-运城市-万荣县-汉fsdfgsdfgsd' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @34=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @35=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @36=NULL /* VARSTRING(255) meta=255 nullable=1 is_null=1 */
###   @37=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @38=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @39=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @40=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @41=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @42=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @43=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @44='1' /* STRING(2) meta=65026 nullable=1 is_null=0 */
###   @45=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @46=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @47=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @48=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @49=1 /* INT meta=0 nullable=1 is_null=0 */
###   @50=0 /* INT meta=0 nullable=1 is_null=0 */
###   @51='创彩公司管理员' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @52='2024-06-07 20:12:28' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @53='cc' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @54='2024-06-07 20:16:46' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @55=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @56=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @57=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @58=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @59=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @60=6550 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @61='创彩公司管理员' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @62=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @63=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @64=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @65=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @66=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @67=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @68=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @69=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @70=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### SET
###   @1=7967 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='D240607985000029' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @3='P240515D4910000063' /* VARSTRING(765) meta=765 nullable=0 is_null=0 */
###   @4='0' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @5=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @6=39800 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @7=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @8='18829479963' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @9=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @10=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @11=1380097 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @12=NULL /* VARSTRING(150) meta=150 nullable=1 is_null=1 */
###   @13='sgs' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @14=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @15=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @16='茶香里128罐装SS英红九号250g*2+满香园红茶（2114）【众春茗】250g*2（赠品：古树红茶（英红九号）10g*1）' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @17=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @18=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @19=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @20=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @21=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @22=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @23=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @24=NULL /* TINYINT meta=0 nullable=1 is_null=1 */
###   @25=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @26='sgs' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @27='18829479963' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @28='2024-01-12 18:22:38' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @29=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @30=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @31=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @32=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @33='山西省-运城市-万荣县-汉fsdfgsdfgsd' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @34=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @35=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @36=NULL /* VARSTRING(255) meta=255 nullable=1 is_null=1 */
###   @37=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @38=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @39=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @40=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @41=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @42=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @43=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @44='1' /* STRING(2) meta=65026 nullable=1 is_null=0 */
###   @45=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @46=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @47=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @48=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @49=1 /* INT meta=0 nullable=1 is_null=0 */
###   @50=0 /* INT meta=0 nullable=1 is_null=0 */
###   @51='创彩公司管理员' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @52='2024-06-07 20:12:28' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @53='cc' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @54='2024-06-07 20:16:46' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @55=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @56=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
###   @57=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @58=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @59=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @60=6550 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @61='创彩公司管理员' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @62=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @63=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @64=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @65=NULL /* VARSTRING(1020) meta=1020 nullable=1 is_null=1 */
###   @66=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @67=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @68=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
###   @69=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @70=0 /* TINYINT meta=0 nullable=1 is_null=0 */

第五步：使用linux的sed命令配合正则表达式对parseA文件进行处理成可以直接执行的sql语句（此处借鉴了网上和AI生成的正则表达式，对于linux正则过滤文本不太擅长又着急恢复数据的同学建议直接 ==> 抄+AI生成满足条件的正则）

sed -n '/### /'p parseA.txt  >b.txt

sed 's/### //g' b.txt  >c.txt

sed '/^UPDATE/{x;p;x}' c.txt >d.txt

sed 's#WHERE#set#g' d.txt >e.txt

sed 's#SET#where#g' e.txt >ii.txt

#因为误操作字段为order_status字段，所以正则匹配留下这个set字段
sed -i '/set/{:a;N;/where/!ba;s#@4=#order_status=#g}' ii.txt
#更新条件为id
sed -i '/where/{:a;N;/\n/!ba;s#@1=#id=#g}' ii.txt

#最后给每个sql语句末尾加上;表示分隔，这里根据你的sql语句的情况来，我这个sql是id=xxx结尾的
sed -i 's/\(id=[0-9]*\)/\1;/' ii.txt

经过sed命令过滤后的文件（抽取的2条记录展示，依靠强大的流式文本编辑器sed命令）：

UPDATE `erp_cc`.`erp_out_order`
set
   order_status='0' 
where
   id=7964;

UPDATE `erp_cc`.`erp_out_order`
set
   order_status='1' 
where
   id=7965;

接着由于我mysql是安装在docker上，所以将docker的文件cp到服务器中

docker cp <容器名或容器ID>:<容器内路径> <本地目标路径>

docker cp 102:/var/lib/mysql/f.txt /home/data/fanke/mysql/data

最后我们在本地服务器查看 tail -n200 ./ii.txt

可以看到所有因为误更新影响的记录都在里面了，然后我们可以通过第三方工具（navicat）直接将文本文件中的所有sql语句执行一遍，就将2万多条数据全部恢复。全程边百度查阅资料边恢复花费了40分钟。特此记录😭，DML操作需谨慎啊！！

爪洼h

关注

4
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mysql通过binlog日志恢复误更新数据

binlog数据恢复
复制链接

扫一扫

Mysql通过binlog日志恢复误更新数据

“相关推荐”对你有帮助么？