海同科技，欢迎您！
就业
培训
资料
职坐标
IT知识库

数据库

站
热门城市 全国站>
    其他省市
      搜索
      快速购买 快速购买  >
      icon 400-636-0069

      热门课程

      IT学院

      职坐标首页 IT知识库 数据库 MySQL
      选择在海同培训： 30 天 1327 名， 今日申请 38 人 IT培训咨询 >>
      MySQL如何恢复删除数据
      沉沙 2018-05-17 来源 ： 阅读 702 评论 0

      摘要：在数据库日常维护中，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。希望本文对大家学习MySQL有所帮助。

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

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

      1.创建测试用的数据表


      mysql> create table t1 (
    -> id int unsigned not null auto_increment,
    -> name char(20) not null,
    -> sex enum('f','m') not null default 'm',
    -> address varchar(30) not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.31 sec)mysql>


      2.插入测试数据


      mysql> insert into t1 (name,sex,address)values('daiiy','m','guangzhou');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t1 (name,sex,address)values('tom','f','shanghai');  
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1 (name,sex,address)values('liany','m','beijing');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1 (name,sex,address)values('lilu','m','zhuhai');  
Query OK, 1 row affected (0.05 sec)
 
mysql>


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


      mysql> select * from t1;+----+-------+-----+-----------+| id | name  | sex | address   |+----+-------+-----+-----------+|  1 | daiiy | m   | guangzhou ||  2 | tom   | f   | shanghai  ||  3 | liany | m   | beijing   ||  4 | lilu  | m   | zhuhai    |+----+-------+-----+-----------+4 rows in set (0.01 sec)
 
mysql> update t1 set address='zhuhai';
Query OK, 3 rows affected (0.09 sec)
Rows matched: 4  Changed: 3  Warnings: 0mysql> select * from t1;              +----+-------+-----+---------+| id | name  | sex | address |+----+-------+-----+---------+|  1 | daiiy | m   | zhuhai  ||  2 | tom   | f   | zhuhai  ||  3 | liany | m   | zhuhai  ||  4 | lilu  | m   | zhuhai  |+----+-------+-----+---------+4 rows in set (0.00 sec)
 
mysql>


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


      mysql> lock tables t1 read ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000024 |     1852 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)
 
mysql>

       


      5.分析二进制日志，并且在其中找到相关记录，在更新时是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 = 26COMMIT/*!*/;
[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;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t1;+----+-------+-----+-----------+| id | name  | sex | address   |+----+-------+-----+-----------+|  1 | daiiy | m   | guangzhou ||  2 | tom   | f   | shanghai  ||  3 | liany | m   | beijing   ||  4 | lilu  | m   | zhuhai    |+----+-------+-----+-----------+4 rows in set (0.00 sec)
 
mysql>


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

       

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

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

      1.模拟误删除数据


      mysql> select * from t1;+----+-------+-----+-----------+| id | name  | sex | address   |+----+-------+-----+-----------+|  1 | daiiy | m   | guangzhou ||  2 | tom   | f   | shanghai  ||  3 | liany | m   | beijing   ||  4 | lilu  | m   | zhuhai    |+----+-------+-----+-----------+4 rows in set (0.00 sec)
 
mysql> delete from t1;
Query OK, 4 rows affected (0.03 sec)
 
mysql> select * from t1;
Empty set (0.00 sec)
 
mysql>


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


      [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
[root@localhost mysql]# cat delete.txt
### DELETE FROM 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 */### DELETE FROM 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 */### DELETE FROM 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 */### DELETE FROM db01.t1
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */###   @2='lilu' /* 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 2719#140305 11:41:00 server id 1  end_log_pos 2746  Xid = 78COMMIT/*!*/;
[root@localhost mysql]#


      3.将记录转换为SQL语句


      [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
[root@localhost mysql]# cat t1.sql
INSERT INTO db01.t1
SELECT
  1 ,
  'daiiy' ,
  2 ,
  'guangzhou' ;
INSERT INTO db01.t1
SELECT
  2 ,
  'tom' ,
  1 ,
  'shanghai' ;
INSERT INTO db01.t1
SELECT
  3 ,
  'liany' ,
  2 ,
  'beijing' ;
INSERT INTO db01.t1
SELECT
  4 ,
  'lilu' ,
  2 ,
  'zhuhai' ;
[root@localhost mysql]#


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


      mysql> source t1.sql;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> select * from t1;
ERROR 1046 (3D000): No database selected
mysql> select * from db01.t1;+----+-------+-----+-----------+| id | name  | sex | address   |+----+-------+-----+-----------+|  1 | daiiy | m   | guangzhou ||  2 | tom   | f   | shanghai  ||  3 | liany | m   | beijing   ||  4 | lilu  | m   | zhuhai    |+----+-------+-----+-----------+4 rows in set (0.00 sec)
 
mysql>


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


      总结：

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

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

      safe-updates

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


      mysql> select *  from t1;+----+------------------+| id | name             |+----+------------------+|  1 | yayun            ||  2 | atlas            ||  3 | mysql            ||  6 | good yayun heheh |+----+------------------+4 rows in set (0.00 sec)
 
mysql> delete from t1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY columnmysql>

       

      本文由职坐标整理发布，更多相关知识，请关注职坐标数据库MySQL频道！


      数据库设计过程 数据库开发技术 数据仓库解决方案
      本文由 @沉沙 发布于职坐标。未经许可，禁止转载。
      喜欢 | 0 不喜欢 | 0
      看完这篇文章有何感觉？已经有0人表态，0%的人喜欢 快给朋友分享吧~
      评论（0）
      后参与评论
      加入IT交流圈
      JAVA工程师交流群 +加入
      大数据架构师交流群 +加入
      人工智能Python交流群 +加入
      WEB/H5前端交流群 +加入
      职坐标公众号
      数据库 直通车
      • 索取资料 索取资料 索取资料
      • 答疑解惑 答疑解惑 答疑解惑
      • 技术交流 技术交流 技术交流
      • 职业测评 职业测评 职业测评
      • 面试技巧 面试技巧 面试技巧
      • 高薪秘笈 高薪秘笈 高薪秘笈
      海同师资推荐 更多>>
      黄骈
      黄骈 联系TA
      认证System Analyst，System Architect
      程光淼
      程光淼 联系TA
      精通C、C++等语言、智能芯片开发
      热门就业培训班 更多>>
      【零基础】人工智能+Python全栈开发课程
      【零基础】人工智能+Python全栈开发课程
      人工智能+Python技术风口浪潮
      【零基础】JavaEE高级开发工程师课程
      【零基础】JavaEE高级开发工程师课程
      0基础入学Java课程
      【零基础】大数据与分布式架构师课程
      【零基础】大数据与分布式架构师课程
      培训大数据分布式架构
      MySQL30天热搜词 更多>>
      mysql数据库入门pdf mysql数据库设计案例 连接mysql mysql date_format mysql数据库程序设计 mysql命令 mysql怎么用 搭建mysql数据库结构 mysql数据库设计文档 mysql数据库表设计 mysql索引尽量不适用 php+mysql开发实战 mysql数据库开发面试题 mysql数据库简介 mysql从入门到精通 mysql数据库参考文献 mysql数据库开发环境 mysql入门很简单 mysql数据库debug mysql数据库设计的书籍

      您输入的评论内容中包含违禁敏感词

      我知道了

      助您圆梦职场 匹配合适岗位
      验证码手机号，获得海同独家IT培训资料
      选择就业方向：
      人工智能物联网
      大数据开发/分析
      人工智能Python
      Java全栈开发
      WEB前端+H5

      请输入正确的手机号码

      请输入正确的验证码

      获取验证码

      您今天的短信下发次数太多了，明天再试试吧！

      提交

      我们会在第一时间安排职业规划师联系您！

      您也可以联系我们的职业规划师咨询：

      小职老师的微信号：z_zhizuobiao
      小职老师的微信号：z_zhizuobiao

      版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
      上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com，All Rights Reserved.
       沪公网安备 31011502005948号    

      关于我们 │ 法律声明 │ 联系我们

      ©2015 www.zhizuobiao.com All Rights Reserved

      208小时内训课程
      在线客服