文档章节

UPDATE或者DELETE忘加WHERE条件的恢复

IT--小哥
 IT--小哥
发布于 2017/07/17 18:27
字数 1990
阅读 22
收藏 0

1、建立测试表

mysql> create table test( 
    -> id int not null auto_increment, 
    -> name char(20) not null, 
    -> sex char(4) not null, 
    -> score varchar(10) not null, 
    -> primary key(id) 
    -> ); 
Query OK, 0 rows affected (0.11 sec)

2、插入数据

mysql> insert into test(name,sex,score) values('张三','男',86); 
Query OK, 1 row affected (0.01 sec) 
…… 
mysql> select * from test; 
+----+--------+-----+-------+ 
| id | name   | sex | score | 
+----+--------+-----+-------+ 
|  1 | 张三   | 男  | 86    | 
|  2 | 李四   | 男  | 88    | 
|  3 | 王五   | 男  | 90    | 
|  4 | 麻六   | 男  | 92    | 
|  5 | 小芳   | 女  | 94    | 
|  6 | 小红   | 女  | 100   | 
+----+--------+-----+-------+ 
6 rows in set (0.00 sec)

mysql> update test set sex='tom'; 
Query OK, 6 rows affected (0.00 sec) 
Rows matched: 6  Changed: 6  Warnings: 0 
   
mysql> select * from test; 
+----+--------+-----+-------+ 
| id | name   | sex | score | 
+----+--------+-----+-------+ 
|  1 | 张三   | tom | 86    | 
|  2 | 李四   | tom | 88    | 
|  3 | 王五   | tom | 90    | 
|  4 | 麻六   | tom | 92    | 
|  5 | 小芳   | tom | 94    | 
|  6 | 小红   | tom | 100   | 
+----+--------+-----+-------+ 
6 rows in set (0.00 sec)

3、开始恢复,生产环境锁表,避免数据被再次污染

mysql> lock tables test read; 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into test(name,sex,score) values('小芳','女',94); 
ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated 
mysql> show master status; 
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000001 |     1816 |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

4、分析二进制日志

[root@xiaoya data]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000001 |grep -B 15 '中' 
BEGIN 
/*!*/; 
# at 1513 
# at 1569 
#160713 13:52:06 server id 1  end_log_pos 1569   Table_map: `students`.`test` mapped to number 33 
#160713 13:52:06 server id 1  end_log_pos 1789   Update_rows: table id 33 flags: STMT_END_F 
### UPDATE `students`.`test` 
### WHERE 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='96' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 

Binlog记录了每一行的变化情况,这里binlog格式必须是row,我们要做的就是把binlog转化成sql重新插入。

6、分析、处理二进制日志

[root@xiaoya data]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000001 |sed -n '/# at 1569/,/# at 1789/p' >mysql-test.txt 
[root@xiaoya data]# cat mysql-test.txt 
# at 1569 
#160713 13:52:06 server id 1  end_log_pos 1569   Table_map: `students`.`test` mapped to number 33 
#160713 13:52:06 server id 1  end_log_pos 1789   Update_rows: table id 33 flags: STMT_END_F 
### UPDATE `students`.`test` 
### WHERE 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='96' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='96' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
# at 1789

下面有一个神奇的sed

[root@xiaoya data]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' mysql-test.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' 
UPDATE `students`.`test` 
SET 
  @1=1 , 
  @2='张三' , 
  @3='男' , 
  @4='86' , 
WHERE 
  @1=1 ; 
UPDATE `students`.`test` 
SET 
  @1=2 , 
  @2='李四' , 
  @3='男' , 
  @4='88' , 
WHERE 
  @1=2 ; 
UPDATE `students`.`test` 
SET 
  @1=3 , 
  @2='王五' , 
  @3='男' , 
  @4='90' , 
WHERE 
  @1=3 ; 
UPDATE `students`.`test` 
SET 
  @1=4 , 
  @2='小红' , 
  @3='女' , 
  @4='92' , 
WHERE 
  @1=4 ; 
UPDATE `students`.`test` 
SET 
  @1=5 , 
  @2='小芳' , 
  @3='女' , 
  @4='96' , 
WHERE 
  @1=5 ;

下面这个就相对简单了

[root@xiaoya data]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/score/g' recover.sql 
[root@xiaoya data]# sed -i -r 's/(score.*),/\1/g' recover.sql 
[root@xiaoya data]# cat recover.sql 
UPDATE `students`.`test` 
SET 
  id=1 , 
  name='张三' , 
  sex='男' , 
  score='86'
WHERE 
  id=1 ; 
UPDATE `students`.`test` 
SET 
  id=2 , 
  name='李四' , 
  sex='男' , 
  score='88'
WHERE 
  id=2 ; 
UPDATE `students`.`test` 
SET 
  id=3 , 
  name='王五' , 
  sex='男' , 
  score='90'
WHERE 
  id=3 ; 
UPDATE `students`.`test` 
SET 
  id=4 , 
  name='小红' , 
  sex='女' , 
  score='92'
WHERE 
  id=4 ; 
UPDATE `students`.`test` 
SET 
  id=5 , 
  name='小芳' , 
  sex='女' , 
  score='96'
WHERE 
  id=5 ;

6、日志处理到此ok,导入数据看一下

mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 
   
mysql> source /usr/local/mysql/data/recover.sql 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
   
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
   
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
   
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
   
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
   
mysql> select * from test; 
+----+--------+-----+-------+ 
| id | name   | sex | score | 
+----+--------+-----+-------+ 
|  1 | 张三   | 男  | 86    | 
|  2 | 李四   | 男  | 88    | 
|  3 | 王五   | 男  | 90    | 
|  4 | 小红   | 女  | 92    | 
|  5 | 小芳   | 女  | 96    | 
+----+--------+-----+-------+ 
5 rows in set (0.00 sec)

到这里数据就完整回来了。将binglog格式设置为row有利有弊,好处是记录了每一行的实际变化,在主从复制时也不容易出问题。但是由于记录每行的变化,会占用大量磁盘,主从复制时带宽占用会有所消耗。到底是使用row还是mixed,需要在实际工作中自己去衡量,但从整体上来说,binglog的格式设置为row,都是不二的选择。

 

 

总结:

1、delete误删除和这个原理是一样的(把binlog在转换成sql重新插入)

2、解决问题的最好方式是预防问题的发生

方案一:定义别名

[root@xiaoya ~]# alias mysql='mysql -U'

[root@xiaoya ~]# echo "alias mysql='mysql -U'" >>/etc/profile
[root@xiaoya ~]# source /etc/profile

指定登陆-U 后,在mysql里,执行update和delete操作,如果没有指定where或limit,则程序拒绝执行。

方案二:在[mysql]段落开启这个参数:

safe-updates

这样当我们在做DML操作时忘记加where条件时,mysqld服务器是不会执行操作的

 

参考:http://www.cnblogs.com/gomysql/p/3582058.html 

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧

© 著作权归作者所有

IT--小哥
粉丝 47
博文 147
码字总数 150342
作品 0
东城
数据库管理员
私信 提问
MySQL之delete 忘加where条件误删除恢复方法二

和昨天介绍的MySQL之delete 忘加where条件误删除恢复的mysql的环境条件是一样的: mysql数据库指定字符集位utf8,同时表的字符集也得为utf8,同时mysql要开启row模式的bin-log日志 创建一张测试...

wjw555
2018/07/04
0
0
binlog-rollback.pl 在线恢复update 和delete不加条件误操作sql

一、binlog-rollback.pl工具介绍 是perl开发的脚本工具,此工具主要是生成反向的DML sql语句: #注意: 二、MySQL server必须设置以下参数: MySQL的版本是mysql5.6.36 三、此工具使用需要创建一...

wjw555
2018/07/06
0
0
MySQL数据恢复的九把瑞士军刀

作者介绍 做DBA的朋友可能都遇到过MySQL数据损坏或丢失的问题,比如忘加where条件的update、delete语句,或者MySQL服务器异常宕机导致数据文件损坏等。本文针对在日常运维中由于误操作、数据...

李辉
2017/04/26
0
0
简单整理一下mysql的隔离级别;

READ UNCOMMITTED SELECT语句以非锁定方式被执行,但是一个可能更早期版本的记录会被用到。因此,使用这个隔离级别,比如,读是不连贯的。这也被称为“脏读”( dirty read)。另外,这个隔离...

世界和平维护者
2016/10/06
33
0
mysql增删改查备份基本操作

删除A表中的ID 中的开头以B* 的数据库。 复制代码 代码如下: delete FROM tablename WHERE id like 'B%' 单独删除 A 表中的ID B 复制代码 代码如下: delete FROM tablename WHERE id = 'B' ......

月黑风高杀人夜
2015/07/23
43
0

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周六乱弹 —— 早上儿子问我他是怎么来的

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @凉小生 :#今日歌曲推荐# 少点戾气,愿你和这个世界温柔以待。中岛美嘉的单曲《僕が死のうと思ったのは (曾经我也想过一了百了)》 《僕が死の...

小小编辑
今天
801
11
Excption与Error包结构,OOM 你遇到过哪些情况,SOF 你遇到过哪些情况

Throwable 是 Java 中所有错误与异常的超类,Throwable 包含两个子类,Error 与 Exception 。用于指示发生了异常情况。 Java 抛出的 Throwable 可以分成三种类型。 被检查异常(checked Exc...

Garphy
今天
15
0
计算机实现原理专题--二进制减法器(二)

在计算机实现原理专题--二进制减法器(一)中说明了基本原理,现准备说明如何来实现。 首先第一步255-b运算相当于对b进行按位取反,因此可将8个非门组成如下图的形式: 由于每次做减法时,我...

FAT_mt
昨天
6
0
好程序员大数据学习路线分享函数+map映射+元祖

好程序员大数据学习路线分享函数+map映射+元祖,大数据各个平台上的语言实现 hadoop 由java实现,2003年至今,三大块:数据处理,数据存储,数据计算 存储: hbase --> 数据成表 处理: hive --> 数...

好程序员官方
昨天
7
0
tabel 中含有复选框的列 数据理解

1、el-ui中实现某一列为复选框 实现多选非常简单: 手动添加一个el-table-column,设type属性为selction即可; 2、@selection-change事件:选项发生勾选状态变化时触发该事件 <el-table @sel...

everthing
昨天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部