在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于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>复制代码5.分析二进制日志，并且在其中找到相关记录，在更新时是address='zhuhai',我们可以在日志中过滤出来。[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 'zhuhai'
复制代码
# at1629# at1679#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 38flags: STMT_END_F
###UPDATEdb01.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*/###UPDATEdb01.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*/###UPDATEdb01.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
# at1679#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 38flags: STMT_END_F
###UPDATEdb01.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*/###UPDATEdb01.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*/###UPDATEdb01.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*/# at1825#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.sqlUPDATEdb01.t1SET
@1=1,@2='daiiy',@3=2,@4='guangzhou',WHERE
@1=1;UPDATEdb01.t1SET
@1=2,@2='tom',@3=1,@4='shanghai',WHERE
@1=2;UPDATEdb01.t1SET
@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.sqlUPDATEdb01.t1SETid=1,
name='daiiy',
sex=2,
address='guangzhou'
WHEREid=1;UPDATEdb01.t1SETid=2,
name='tom',
sex=1,
address='shanghai'
WHEREid=2;UPDATEdb01.t1SETid=3,
name='liany',
sex=2,
address='beijing'
WHEREid=3;[root@localhost mysql]#
复制代码7.到这里日志就处理好了，现在导入即可(导入数据后，解锁表)；
复制代码
mysql>source recover.sql;
Query OK,1 row affected (0.12sec)
Rows matched:1 Changed: 1 Warnings: 0Query OK,1 row affected (0.00sec)
Rows matched:1 Changed: 1 Warnings: 0Query OK,1 row affected (0.01sec)
Rows matched:1 Changed: 1 Warnings: 0mysql> 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.00sec)
mysql>复制代码
可以看见数据已经完全恢复，这种方法的优点是快速，方便。
二.delete忘加where条件误删除恢复(binglog格式必须是ROW)
其实这和update忘加条件差不多，不过这处理更简单,这里就用上面那张表做测试吧1.模拟误删除数据
复制代码
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.00sec)
mysql> delete fromt1;
Query OK,4 rows affected (0.03sec)
mysql> select * fromt1;
Emptyset (0.00sec)
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 FROMdb01.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 FROMdb01.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 FROMdb01.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 FROMdb01.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*/# at2719#140305 11:41:00 server id 1 end_log_pos 2746 Xid = 78
COMMIT/*!*/;[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.sqlINSERT INTOdb01.t1SELECT
1,'daiiy',2,'guangzhou';INSERT INTOdb01.t1SELECT
2,'tom',1,'shanghai';INSERT INTOdb01.t1SELECT
3,'liany',2,'beijing';INSERT INTOdb01.t1SELECT
4,'lilu',2,'zhuhai';[root@localhost mysql]#
复制代码4.导入数据，验证数据完整性
复制代码
mysql>source t1.sql;
Query OK,1 row affected (0.00sec)
Records:1 Duplicates: 0 Warnings: 0Query OK,1 row affected (0.02sec)
Records:1 Duplicates: 0 Warnings: 0Query OK,1 row affected (0.02sec)
Records:1 Duplicates: 0 Warnings: 0Query OK,1 row affected (0.01sec)
Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt1;
ERROR1046 (3D000): No databaseselected
mysql> select * fromdb01.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.00sec)
mysql>复制代码
到这里数据就完整回来了。将binglog格式设置为row有利有弊，好处是记录了每一行的实际变化，在主从复制时也不容易出问题。但是由于记录每行的变化，会占用大量磁盘，主从复制时带宽占用会有所消耗。到底是使用row还是mixed，需要在实际工作中自己去衡量，但从整体上来说，binglog的格式设置为row，都是不二的选择。
总结：
所以在数据库操作的过程中我们需要格外小心，当然开发那边我们需要做好权限的控制，不过有一个参数可以解决我们的问题，让我们不用担心类似的问题发生：
在[mysql]段落开启这个参数：
safe-updates
这样当我们在做DML操作时忘记加where条件时，mysqld服务器是不会执行操作的：
复制代码
mysql> select * fromt1;+----+------------------+
| id | name |
+----+------------------+
| 1 | yayun |
| 2 | atlas |
| 3 | mysql |
| 6 | good yayun heheh |
+----+------------------+
4 rows in set (0.00sec)
mysql> delete fromt1;
ERROR1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY columnmysql>复制代码
作者：Atlas
出处：Atlas的博客 /gomysql
您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。如果您需要技术支持，本人亦提供有偿服务。
