在数据库日常维护中,开发人员是最让人头痛的,很多时候都会由于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: 0 
 
mysql> 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',我们可以在日志中过滤出来。

[[email protected] 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 */

可以看见里面记录了每一行的变化,[email protected],@2,@3,@4,分别对应表中id,name,sex,address字段。相信大家看到这里有点明白了吧,对,没错,你猜到了,我们将相关记录转换为sql语句,重新导入数据库。

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

[[email protected] mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | sed -n '/# at 1679/,/COMMIT/p' > t1.txt   
[[email protected] 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 = 26 
COMMIT/*!*/; 
[[email protected] mysql]# 

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

[[email protected] mysql]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' t1.txt | sed -r '/WHERE/{:a;N;[email protected]/!ba;s/###   @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;[email protected]/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > recover.sql 
[[email protected] 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 ; 
[[email protected] mysql]# 

[email protected],@2,@3,@4替换为t1表中id,name,sex,address字段,并删除最后字段的","号

[[email protected] mysql]# sed -i '[email protected]/id/g;[email protected]/name/g;[email protected]/sex/g;[email protected]/address/g' recover.sql 
[[email protected] mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql 
[[email protected] 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 ; 
[[email protected] mysql]# 

7.到这里日志就处理好了,现在导入即可(导入数据后,解锁表);

mysql> source recover.sql; 
Query OK, 1 row affected (0.12 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.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
 
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 忘加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中去查找相关记录

[[email protected] mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETE FROM db01.t1/,/COMMIT/p' > delete.txt 
[[email protected] 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 = 78 
COMMIT/*!*/; 
[[email protected] mysql]# 

3.将记录转换为SQL语句

[[email protected] 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 
[[email protected] 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' ; 
[[email protected] mysql]# 

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

mysql> source t1.sql; 
Query OK, 1 row affected (0.00 sec) 
Records: 1  Duplicates: 0  Warnings: 0 
 
Query OK, 1 row affected (0.02 sec) 
Records: 1  Duplicates: 0  Warnings: 0 
 
Query OK, 1 row affected (0.02 sec) 
Records: 1  Duplicates: 0  Warnings: 0 
 
Query OK, 1 row affected (0.01 sec) 
Records: 1  Duplicates: 0  Warnings: 0 
 
mysql> 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 column 
mysql> 

 

作者:Atlas

出处:Atlas的博客 http://www.cnblogs.com/gomysql

您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。如果您需要技术支持,本人亦提供有偿服务。

 
 
发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

利用nginx实现负载均衡详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。