MySQL 误删数据、误更新数据（update,delete忘加where条件）

转自：https://www.cnblogs.com/gomysql/p/3582058.html

在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。

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

1.创建测试用的数据表

1.  mysql> create table t1 (

2.  -> id int  unsigned  not  null auto_increment,

3.  -> name char(20)  not  null,

4.  -> sex enum('f','m')  not  null  default  'm',

5.  -> address varchar(30)  not  null,

6.  -> primary key(id)

7.  ->  );

8.  Query OK,  0 rows affected (0.31 sec)

9.  mysql>

2.插入测试数据

1.  mysql> insert into t1 (name,sex,address)values('daiiy','m','guangzhou');

2.  Query OK,  1 row affected (0.01 sec)

4.  mysql> insert into t1 (name,sex,address)values('tom','f','shanghai');

5.  Query OK,  1 row affected (0.00 sec)

7.  mysql> insert into t1 (name,sex,address)values('liany','m','beijing');

8.  Query OK,  1 row affected (0.00 sec)

10.  mysql> insert into t1 (name,sex,address)values('lilu','m','zhuhai');

11.  Query OK,  1 row affected (0.05 sec)

13.  mysql>

3.现在需要将id等于2的用户的地址改为zhuhai，update时没有添加where条件

1.  mysql>  select  *  from t1;

2.  +----+-------+-----+-----------+

3.  | id | name | sex | address |

4.  +----+-------+-----+-----------+

5.  |  1  | daiiy | m | guangzhou |

6.  |  2  | tom | f | shanghai |

7.  |  3  | liany | m | beijing |

8.  |  4  | lilu | m | zhuhai |

9.  +----+-------+-----+-----------+

10.  4 rows in  set  (0.01 sec)

12.  mysql> update t1 set address='zhuhai';

13.  Query OK,  3 rows affected (0.09 sec)

14.  Rows matched:  4  Changed:  3  Warnings:  0

16.  mysql>  select  *  from t1;

17.  +----+-------+-----+---------+

18.  | id | name | sex | address |

19.  +----+-------+-----+---------+

20.  |  1  | daiiy | m | zhuhai |

21.  |  2  | tom | f | zhuhai |

22.  |  3  | liany | m | zhuhai |

23.  |  4  | lilu | m | zhuhai |

24.  +----+-------+-----+---------+

25.  4 rows in  set  (0.00 sec)

27.  mysql>

4.开始恢复，在线上的话，应该比较复杂，要先进行锁表，以免数据再次被污染。（锁表，查看正在写哪个二进制日志）

1.  mysql>  lock tables t1 read ;

2.  Query OK,  0 rows affected (0.00 sec)

4.  mysql> show master status;

5.  +------------------+----------+--------------+------------------+

6.  |  File  |  Position  |  Binlog_Do_DB  |  Binlog_Ignore_DB  |

7.  +------------------+----------+--------------+------------------+

8.  | mysql-bin.000024  |  1852  |  |  |

9.  +------------------+----------+--------------+------------------+

10.  1 row in  set  (0.00 sec)

12.  mysql>

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

1.  [root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024  | grep -B 15  'zhuhai'    --表名

1.  # at 1629
2.  **# at 1679**

3.  #140305 10:52:24 server id 1  end_log_pos 1679  Table_map: `db01`.`t1` mapped to number 38

4.  #140305 10:52:24 server id 1  end_log_pos 1825  Update_rows: table id 38 flags: STMT_END_F

5.  ### UPDATE db01.t1

6.  ### WHERE

7.  ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

8.  ###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */

9.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

10.  ###   @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

11.  ### SET

12.  ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

13.  ###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */

14.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

15.  ###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

16.  ### UPDATE db01.t1

17.  ### WHERE

18.  ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

19.  ###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */

20.  ###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

21.  ###   @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

22.  ### SET

23.  ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

24.  ###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */

25.  ###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

26.  ###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

27.  ### UPDATE db01.t1

28.  ### WHERE

29.  ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

30.  ###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */

31.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

32.  ###   @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

33.  ### SET

34.  ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

35.  ###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */

36.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

37.  ###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

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

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

1.  [root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024  | sed -n '/# at 1679/,/COMMIT/p'  > t1.txt

2.  [root@localhost mysql]# cat t1.txt

3.  **# at** **1679**

4.  #140305 10:52:24 server id 1  end_log_pos 1679  Table_map: `db01`.`t1` mapped to number 38

5.  #140305 10:52:24 server id 1  end_log_pos 1825  Update_rows: table id 38 flags: STMT_END_F

6.  ### UPDATE db01.t1

7.  ### WHERE

8.  ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

9.  ###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */

10.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

11.  ###   @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

12.  ### SET

13.  ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

14.  ###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */

15.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

16.  ###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

17.  ### UPDATE db01.t1

18.  ### WHERE

19.  ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

20.  ###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */

21.  ###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

22.  ###   @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

23.  ### SET

24.  ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

25.  ###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */

26.  ###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

27.  ###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

28.  ### UPDATE db01.t1

29.  ### WHERE

30.  ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

31.  ###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */

32.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

33.  ###   @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

34.  ### SET

35.  ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

36.  ###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */

37.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

38.  ###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

39.  # at 1825

40.  #140305 10:52:24 server id 1  end_log_pos 1852  Xid = 26

41.  COMMIT/*!*/;

42.  [root@localhost mysql]#

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

1.  [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

1.  [root@localhost mysql]# cat recover.sql

2.  UPDATE db01.t1

3.  SET

4.  @1=1  ,

5.  @2='daiiy'  ,

6.  @3=2  ,

7.  @4='guangzhou'  ,

8.  WHERE

9.  @1=1  ;

10.  UPDATE db01.t1

11.  SET

12.  @1=2  ,

13.  @2='tom'  ,

14.  @3=1  ,

15.  @4='shanghai'  ,

16.  WHERE

17.  @1=2  ;

18.  UPDATE db01.t1

19.  SET

20.  @1=3  ,

21.  @2='liany'  ,

22.  @3=2  ,

23.  @4='beijing'  ,

24.  WHERE

25.  @1=3  ;

26.  [root@localhost mysql]#

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

1.  [root@localhost mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql

2.  [root@localhost mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql

3.  [root@localhost mysql]# cat recover.sql

4.  UPDATE db01.t1

5.  SET

6.  id=1  ,

7.  name='daiiy'  ,

8.  sex=2  ,

9.  address='guangzhou'

10.  WHERE

11.  id=1  ;

12.  UPDATE db01.t1

13.  SET

14.  id=2  ,

15.  name='tom'  ,

16.  sex=1  ,

17.  address='shanghai'

18.  WHERE

19.  id=2  ;

20.  UPDATE db01.t1

21.  SET

22.  id=3  ,

23.  name='liany'  ,

24.  sex=2  ,

25.  address='beijing'

26.  WHERE

27.  id=3  ;

28.  [root@localhost mysql]#

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

1.  mysql> source recover.sql;

2.  Query OK,  1 row affected (0.12 sec)

3.  Rows matched:  1  Changed:  1  Warnings:  0

5.  Query OK,  1 row affected (0.00 sec)

6.  Rows matched:  1  Changed:  1  Warnings:  0

8.  Query OK,  1 row affected (0.01 sec)

9.  Rows matched:  1  Changed:  1  Warnings:  0

11.  mysql>  select  *  from t1;

12.  +----+-------+-----+-----------+

13.  | id | name | sex | address |

14.  +----+-------+-----+-----------+

15.  |  1  | daiiy | m | guangzhou |

16.  |  2  | tom | f | shanghai |

17.  |  3  | liany | m | beijing |

18.  |  4  | lilu | m | zhuhai |

19.  +----+-------+-----+-----------+

20.  4 rows in  set  (0.00 sec)

22.  mysql>

可以看见数据已经完全恢复，这种方法的优点是快速，方便。

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

其实这和update忘加条件差不多，不过这处理更简单,这里就用上面那张表做测试吧

1.模拟误删除数据

1.  mysql>  select  *  from t1;

2.  +----+-------+-----+-----------+

3.  | id | name | sex | address |

4.  +----+-------+-----+-----------+

5.  |  1  | daiiy | m | guangzhou |

6.  |  2  | tom | f | shanghai |

7.  |  3  | liany | m | beijing |

8.  |  4  | lilu | m | zhuhai |

9.  +----+-------+-----+-----------+

10.  4 rows in  set  (0.00 sec)

12.  mysql>  delete  from t1;

13.  Query OK,  4 rows affected (0.03 sec)

15.  mysql>  select  *  from t1;

16.  Empty  set  (0.00 sec)

18.  mysql>

2.在binglog中去查找相关记录

1.  [root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024  | sed -n '/### DELETE FROM db01.t1/,/COMMIT/p'  >  delete.txt

2.  [root@localhost mysql]# cat delete.txt

3.  ### DELETE FROM db01.t1

4.  ### WHERE

5.  ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

6.  ###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */

7.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

8.  ###   @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

9.  ### DELETE FROM db01.t1

10.  ### WHERE

11.  ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

12.  ###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */

13.  ###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

14.  ###   @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

15.  ### DELETE FROM db01.t1

16.  ### WHERE

17.  ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

18.  ###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */

19.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

20.  ###   @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

21.  ### DELETE FROM db01.t1

22.  ### WHERE

23.  ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */

24.  ###   @2='lilu' /* STRING(60) meta=65084 nullable=0 is_null=0 */

25.  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

26.  ###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

27.  # at 2719

28.  #140305 11:41:00 server id 1  end_log_pos 2746  Xid = 78

29.  COMMIT/*!*/;

30.  [root@localhost mysql]#

3.将记录转换为SQL语句

1.  [root@localhost mysql]# cat delete.txt | sed -n '/###/p'  | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;'  | sed -r 's/(@4.*),/\1;/g'  | sed 's/@[1-9]=//g'  > t1.sql

2.  [root@localhost mysql]# cat t1.sql

3.  INSERT INTO db01.t1

4.  SELECT

5.  1  ,

6.  'daiiy'  ,

7.  2  ,

8.  'guangzhou'  ;

9.  INSERT INTO db01.t1

10.  SELECT

11.  2  ,

12.  'tom'  ,

13.  1  ,

14.  'shanghai'  ;

15.  INSERT INTO db01.t1

16.  SELECT

17.  3  ,

18.  'liany'  ,

19.  2  ,

20.  'beijing'  ;

21.  INSERT INTO db01.t1

22.  SELECT

23.  4  ,

24.  'lilu'  ,

25.  2  ,

26.  'zhuhai'  ;

27.  [root@localhost mysql]#

4.导入数据，验证数据完整性

1.  mysql> source t1.sql;

2.  Query OK,  1 row affected (0.00 sec)

3.  Records:  1  Duplicates:  0  Warnings:  0

5.  Query OK,  1 row affected (0.02 sec)

6.  Records:  1  Duplicates:  0  Warnings:  0

8.  Query OK,  1 row affected (0.02 sec)

9.  Records:  1  Duplicates:  0  Warnings:  0

11.  Query OK,  1 row affected (0.01 sec)

12.  Records:  1  Duplicates:  0  Warnings:  0

14.  mysql>  select  *  from t1;

15.  ERROR 1046  (3D000):  No database selected

16.  mysql>  select  *  from db01.t1;

17.  +----+-------+-----+-----------+

18.  | id | name | sex | address |

19.  +----+-------+-----+-----------+

20.  |  1  | daiiy | m | guangzhou |

21.  |  2  | tom | f | shanghai |

22.  |  3  | liany | m | beijing |

23.  |  4  | lilu | m | zhuhai |

24.  +----+-------+-----+-----------+

25.  4 rows in  set  (0.00 sec)

27.  mysql>

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

总结：

所以在数据库操作的过程中我们需要格外小心，当然开发那边我们需要做好权限的控制，不过有一个参数可以解决我们的问题，让我们不用担心类似的问题发生：

在[mysql]段落开启这个参数：

1.  safe-updates

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

1.  mysql>  select  *  from t1;

2.  +----+------------------+

3.  | id | name |

4.  +----+------------------+

5.  |  1  | yayun |

6.  |  2  | atlas |

7.  |  3  | mysql |

8.  |  6  | good yayun heheh |

9.  +----+------------------+

10.  4 rows in  set  (0.00 sec)

12.  mysql>  delete  from t1;

13.  ERROR 1175  (HY000):  You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

14.  mysql>

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,651评论 6赞 501
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,468评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,931评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,218评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,234评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,198评论 1赞 299
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,084评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,926评论 0赞 274
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,341评论 1赞 311
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,563评论 2赞 333
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,731评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,430评论 5赞 343
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,036评论 3赞 326
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,676评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,829评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,743评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,629评论 2赞 354

MySQL 误删数据、误更新数据（update,delete忘加where条件）

推荐阅读更多精彩内容