MySQL 误操作后数据恢复（update,delete忘加where条件）

最新推荐文章于 2024-08-19 14:36:26 发布
最新推荐文章于 2024-08-19 14:36:26 发布
阅读量1.1k 收藏 4
点赞数
分类专栏： mysql
原文链接：https://www.cnblogs.com/gomysql/p/3582058.html
版权

https://www.cnblogs.com/gomysql/p/3582058.html

前提mysql 的binglog格式必须是ROW

查看mysql 是否row 模式：

/etc/my.cnf 

 delete 忘加where条件误删除恢复(binglog格式必须是ROW)

1. 找到误操作 时间段的bin 文件 。

bin 文件的目录 可以造my.cnf中找到：

bin文件就在/data/mysqldata 目录下，以bin开头的文件就是bin文件

有很多bin文件：

找到误操作时间的那个。我是 这个

2. 对bin 文件 解码，然后找到误操作那块，通过sed 命令把sql 还原

现在场景是delete 操作

解码命令：

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v bin.000135 >1.sql

找到1.sql中delete 操作那块，可以看到delete from xx表

用sed 把delete 开始到commit 这个范围的数据用sed 抽取出来

命令：

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v  bin.000135 | sed -n '/### DELETE FROM/,/COMMIT/p' > /data/delete.txt

用sed 解析：

cat /data/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@14.*),/\1;/g' | sed -r 's/@.+=//g' > /data/t1.sql

解析出来不可以直接执行，所以换了一下：

cat /data/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM.*/union/g;s/WHERE/SELECT/g;' | sed -r 's/(@14.*),/\1/g' | sed -r 's/@.+=//g' |sed '1d' > /data/t1.sql

在本地数据库里面 执行，然后到处excel 文件。 

在通过excel 导入数据库表中 就可以。先把库里面的时间字段改成varchar 后再导入。

如何导入excel数据到数据库，并解决导入时间格式问题。请看blog https://blog.csdn.net/zhuchunyan_aijia/article/details/100521628

然后把表中varchar 改成时间格式，改字段类型就可以。如果报错，用update set dd=null where  dd=‘’ 就是把字段从‘’ 改成null 就可以了

 

 update 忘加where条件误操作恢复数据(binglog格式必须是ROW）

先记录一下

1. 分析二进制日志，并且在其中找到相关记录，在更新时是address='zhuhai',我们可以在日志中过滤出来。

[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 'zhuhai'
# at 1629
# at 1679
#140305 10:52:24 server id 1  end_log_pos 1679  Table_map: `db01`.`t1` mapped to number 38
#140305 10:52:24 server id 1  end_log_pos 1825  Update_rows: table id 38 flags: STMT_END_F
### UPDATE db01.t1
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

 

可以看见里面记录了每一行的变化，这也是binglog格式要一定是row才行的原因。其中@1,@2,@3,@4,分别对应表中id,name,sex,address字段。相信大家看到这里有点明白了吧，对，没错，你猜到了，我们将相关记录转换为sql语句，重新导入数据库。

6.处理分析处理的二进制日志

[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | sed -n '/# at 1679/,/COMMIT/p' > t1.txt  
[root@localhost mysql]# cat t1.txt 
# at 1679
#140305 10:52:24 server id 1  end_log_pos 1679  Table_map: `db01`.`t1` mapped to number 38
#140305 10:52:24 server id 1  end_log_pos 1825  Update_rows: table id 38 flags: STMT_END_F
### UPDATE db01.t1
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
# at 1825
#140305 10:52:24 server id 1  end_log_pos 1852  Xid = 26
COMMIT/*!*/;
[root@localhost mysql]#

这里sed有点复杂，需要童鞋们好好自己研究研究，这里我就不多说了。

[root@localhost mysql]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' t1.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > recover.sql 
root@localhost mysql]# cat recover.sql 
UPDATE db01.t1
SET
  @1=1 ,
  @2='daiiy' ,
  @3=2 ,
  @4='guangzhou' ,
WHERE
  @1=1 ;
UPDATE db01.t1
SET
  @1=2 ,
  @2='tom' ,
  @3=1 ,
  @4='shanghai' ,
WHERE
  @1=2 ;
UPDATE db01.t1
SET
  @1=3 ,
  @2='liany' ,
  @3=2 ,
  @4='beijing' ,
WHERE
  @1=3 ;
[root@localhost mysql]#

将文件中的@1,@2,@3,@4替换为t1表中id，name，sex，address字段，并删除最后字段的","号

[root@localhost mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql
[root@localhost mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql
[root@localhost mysql]# cat recover.sql 
UPDATE db01.t1
SET
  id=1 ,
  name='daiiy' ,
  sex=2 ,
  address='guangzhou' 
WHERE
  id=1 ;
UPDATE db01.t1
SET
  id=2 ,
  name='tom' ,
  sex=1 ,
  address='shanghai' 
WHERE
  id=2 ;
UPDATE db01.t1
SET
  id=3 ,
  name='liany' ,
  sex=2 ,
  address='beijing' 
WHERE
  id=3 ;
[root@localhost mysql]#

7.到这里日志就处理好了，现在导入即可（导入数据后，解锁表）；

mysql> source recover.sql;

 

我家小宝_朱朱
关注
专栏目录
mysql+修改数据恢复_MySQL 误操作后数据恢复（update,delete忘加where条件）
weixin_32446549的博客
01-18 541
在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。一. update 忘加where条件误操作恢复数据(binglog格式必须是ROW)1....
mysql update 限制值_MySQL不带where条件的UPDATE和DELETE 限制操作说明
weixin_29790897的博客
01-19 1501
本文来自数据安全是业务的基石，但是DBA 总会遇到救火情况，业务误删除全表或者误更新错全表业务数据，导致服务不可用sql_safe_updates参数可以限制不带where条件的update/delete语句执行失败，这个参数设置后，可以防止业务bug/漏洞导致把整个表都更新或者删除(线上发生过的案例)，也可以防止DBA在线误操作更新/删除整张表。If set to 1, MySQL aborts...
错误：update 忘了加 where
weixin_33911824的博客
04-10 88
职业生涯应该都犯过的错误，幸好是在开发库，生产环境的库真是要严格的进行权限管理和脚本执行流程规范。
对DB做数据更新时，忘了加where条件不要慌
ysyswywl2的博客
12-24 2260
作为程序员，我们几乎都会和数据库打交道，在对数据库做数据更新时，你们有没有过忘记加where条件的惨痛经历？如果有，这会儿想想是不是想笑一笑？哈哈！如果没有，那么希望我的这篇帖子或多或少对你有点帮助，可以让你避免或者在遇到这种情况的时候不至于感觉天要塌下来了。 对数据库的更新操作说白了就是add、update、delete。无论你是通过程序还是直接在DB客户端里敲sql命令，当遇到这种马虎错误时
MySQL数据误删或者误更新如何恢复(详细步骤，一看就会)
最新发布
落日的博客
08-19 2171
①、测试环境少量近期误删除或者误更新的数据恢复。②、测试环境少量从库数据不一致问题。备注：大量数据的恢复或者复制还是需要使用备份数据,例如使用mysqldump或者Mydumper、mysqlshell。(本篇文章对此不做讨论)警告：数据恢复为DBA专业人员负责处理的事情，本文章仅为开发人员测试环境恢复近期误操作的少量数据提供参考。请对生产环境数据心存敬畏~
MySQL恢复之update忘加where条件误操作后数据恢复（提前条件binlog为row行格式）
anzhen0429的博客
07-23 2111
在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。 误操作之前数据库是binglog格式必须是ROW状态 一. update 忘加whe
Orcale执行update时忘了加条件的回退操作
qq_41377914的博客
11-05 1353
今天写update的SQL忘了加where条件,全更新,真是悲催,还好找到一个好的方法,回退回来; 在次记录下来; 　　1.查询最近更新数据之前的数据（以便确定是不是Commit之前的数据） select * from 表名 as of timestamp to_timestamp('2018-10-22 08:00:00', 'yyyy-mm-dd hh24:mi:ss'); '20...
恢复误操作update未加where条件的mysql数据表
pyxllq的博客
04-02 2150
记录一下：因为自己的误操作，导致运营中的数据库表数据被更新掉(update未加where条件)，本文章引用来自https://blog.51cto.com/wujianwei/2294284?cid=720522 一.环境说明 MySQL 上update操作时,忘加where条件 ，使用mysqlbinlog搭配sed命令完美还原 MySQL版本号：(MySQL Community S...
MySQL数据库误操作后快速回滚的方法
09-09
本篇文章将详细讲解如何在MySQL数据库误操作后进行快速回滚，以确保数据的安全。 首先，MySQL的二进制日志（Binary Log，简称binlog）是实现数据恢复的关键。它记录了所有改变数据库状态的语句，包括INSERT、UPDATE...
MySQL防止delete命令删除数据的两种方法
09-10
安全更新模式是MySQL的一种启动选项，它强制`UPDATE`和`DELETE`命令必须包含`WHERE`子句，以限制删除或修改数据的行为。如果在执行`DELETE`命令时不提供`WHERE`条件，MySQL将拒绝执行该命令，从而防止误删所有数据。...
update 后没有加where条件解决办法
weixin_34166472的博客
09-27 7211
MySQL 误操作后数据恢复（update,delete忘加where条件） 在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。 ...
Oracle使用updateSQL语句忘记加条件，误改了其他数据的回滚方法
u010741112的博客
11-16 1194
执行完之后，就会回到这个误操作之前啦，然后在执行正确的语句，便可以完成，对数据库操作一定要仔细仔细再仔细！Oracle使用updateSQL语句忘记加条件，误改了其他数据的回滚方法。误改后不管是保存还是暂存，所有审批状态都变成了审核通过-。上面语句的table和时间要根据自己的情况进行修改。这是其他单据的审批状态。（1）第一条SQL语句。（2）第二条SQL语句。（3）第三条SQL语句。
update和delete操作忘加where条件导致全表更新的处理方法
小胡子
07-27 1595
在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。 一. update 忘加where条件误操作恢复数据(binglog格式必须是ROW) 1.创建测试用的数据表 mysql> create table t1 ( ->.
对数据库操作，竟然忘记添加where条件
AinUser的博客
06-25 1274
[html] view plain copy 记得前一阵子，对数据库进行操作的时候，竟然忘记添加where条件了，当时很是捉急 本人觉得，虽然这个问题，很小，但是非常重要，在这里就不再啰嗦，大部分搜到这篇文章的同学们，现在 应该很是捉急，直接上几个解决办法吧 ①：网上
mysql 删除恢复_MySQL之delete 忘加where条件误删除恢复
weixin_34742806的博客
01-18 206
一、mysql环境介绍：mysql数据库指定字符集位utf8,同时表的字符集也得为utf8,同时mysql要开启row模式的bin-log日志/etc/my.cnf文件字符集参数设置：[root@git-server ~]# grep character-set /etc/my.cnf[client]port = 3306socket = /tmp/mysql.sockdefault-charac...
MySQL数据库误Update找回数据的方法
乌龟的专栏
11-17 5927
MySQL数据库误Update找回数据的方法
Navicat for MySQL 误操作数据表数据 补救方法
热门推荐
Yy02123的博客
11-22 2万+
一、问题描述 用navicat 操作MySQL时，修改一行单元格后，不小心点击了另一行，这样navicat是会自动保存数据的，我就犯过这种错，小心肝微微一颤，毕竟数据是重要的~ 二、问题解决 在工具栏里面有个历史日志， 打开后可以看到你对数据表的修改日志，然后恢复一下就可以啦 ^_ ^ ...
利用binglog恢复mysql删除数据
qinshi501的博客
04-28 799
一:使用的是mysql5.6,首先看下MySQL的binlog日志是否开启 log-bin=mysql-bin binlog_format=mixed expire_logs_days = 10 保留10天日志 二: 更新biglog日志 mysq> flush logs; 会产生新的日志文件; 三:导出待恢复的sql语句 /usr/loca...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值