mysql+修改数据恢复_MySQL 误操作后数据恢复（update,delete忘加where条件）

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

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

1.创建测试用的数据表

mysql> create tablet1 (-> id int unsigned not nullauto_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.31sec)

mysql>

2.插入测试数据

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

Query OK,1 row affected (0.01sec)

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

Query OK,1 row affected (0.00sec)

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

Query OK,1 row affected (0.00sec)

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

Query OK,1 row affected (0.05sec)

mysql>

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

mysql> select * fromt1;+----+-------+-----+-----------+

| 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.01sec)

mysql> update t1 set address='zhuhai';

Query OK,3 rows affected (0.09sec)

Rows matched:4 Changed: 3 Warnings: 0mysql> select * fromt1;+----+-------+-----+---------+

| 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.00sec)

mysql>

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

mysql> lock tables t1 read;

Query OK,0 rows affected (0.00sec)

mysql>show master status;+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000024 | 1852 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

mysql>