mysql数据库数据的删除与恢复

Part 1 删除数据, truncate 和 delete 的区别

转自：http://blog.is36.com/mysql_difference_of_truncate_and_delete/

truncate是整体删除（速度较快）， delete是逐条删除（速度较慢）。
truncate不写服务器log，delete写服务器log，也就是truncate效率比delete高的原因；(delete 不带where 的语句不写日志)
truncate不激活trigger(触发器)，但是会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。而delete删除以后，Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
如果只需删除表中的部分记录，只能使用DELETE语句配合where条件。 DELETE FROM wp_comments WHERE……

Part 2 msql下对没有where语句的delete和update做限制

转自：http://blog.csdn.net/shgh_2004/article/details/53150276

如果在生产环境中使用UPDATE，DELETE语句操作数据，此时如果忘记携带本应该添加的WHERE条件，后果可能不堪设想。在正常的业务情况，也不会更新或者删除所有的记录

1. 阻止update设置（sql_safe_updates）

sql_safe_updates这个MySQL自带的参数就可以完美的解决我们的问题，并且该参数是可以在线变更的哦~当该参数开启的情况下，你必须要在UPDATE语句后携带WHERE条件，否则就会报出ERROR。。

# sql_safe_updates=0,即未开启
root@127.0.0.1 : test 07:58:34> set sql_safe_updates=0;
Query OK, 0 rows affected (0.00 sec)root@127.0.0.1 : test 07:58:43> show variables like 'sql_safe_updates'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | sql_safe_updates | OFF | +------------------+-------+ 1 row in set (0.00 sec)  # sql_safe_updates=1,即开启 root@127.0.0.1 : test 08:00:00> set sql_safe_updates=1; Query OK, 0 rows affected (0.00 sec) root@127.0.0.1 : test 08:00:11> show variables like 'sql_safe_updates'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | sql_safe_updates | ON | +------------------+-------+ 1 row in set (0.00 sec)  root@127.0.0.1 : test 08:00:27> update t set aaa='aaa'; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

如上属的例子所示，当参数sql_safe_updates开启的时候，UPDATE语句不携带WHERE条件将会爆出一个错误。

2. 阻止delete设置

直接限制mysql删除
启动MySQL的时候加上参数 -U
-U, –safe-updates Only allow UPDATE and DELETE that uses keys.
他的作用是防止执行delete的时候没带有条件语句，如果没加上where则语句不执行。

Part 3 MySQL 误操作后数据恢复（update,delete忘加where条件）

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

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

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

1.创建测试用的数据表

mysql数据库数据的删除与恢复-编程知识网

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>

mysql数据库数据的删除与恢复-编程知识网

2.插入测试数据

mysql数据库数据的删除与恢复-编程知识网

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>

mysql数据库数据的删除与恢复-编程知识网

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

mysql数据库数据的删除与恢复-编程知识网

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>

mysql数据库数据的删除与恢复-编程知识网

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

mysql数据库数据的删除与恢复-编程知识网

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>

mysql数据库数据的删除与恢复-编程知识网

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

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

mysql数据库数据的删除与恢复-编程知识网

# 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 */

mysql数据库数据的删除与恢复-编程知识网

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

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

mysql数据库数据的删除与恢复-编程知识网

[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 = 26
COMMIT/*!*/;
[root@localhost mysql]#

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

mysql数据库数据的删除与恢复-编程知识网

[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]#

mysql数据库数据的删除与恢复-编程知识网

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

mysql数据库数据的删除与恢复-编程知识网

[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
SETid=1 ,name='daiiy' ,sex=2 ,address='guangzhou' 
WHEREid=1 ;
UPDATE db01.t1
SETid=2 ,name='tom' ,sex=1 ,address='shanghai' 
WHEREid=2 ;
UPDATE db01.t1
SETid=3 ,name='liany' ,sex=2 ,address='beijing' 
WHEREid=3 ;
[root@localhost mysql]#

mysql数据库数据的删除与恢复-编程知识网

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

mysql数据库数据的删除与恢复-编程知识网

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>

mysql数据库数据的删除与恢复-编程知识网

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

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

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

1.模拟误删除数据

mysql数据库数据的删除与恢复-编程知识网

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>

mysql数据库数据的删除与恢复-编程知识网

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

mysql数据库数据的删除与恢复-编程知识网

[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 = 78
COMMIT/*!*/;
[root@localhost mysql]#

mysql数据库数据的删除与恢复-编程知识网

3.将记录转换为SQL语句

mysql数据库数据的删除与恢复-编程知识网

[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
SELECT1 ,'daiiy' ,2 ,'guangzhou' ;
INSERT INTO db01.t1
SELECT2 ,'tom' ,1 ,'shanghai' ;
INSERT INTO db01.t1
SELECT3 ,'liany' ,2 ,'beijing' ;
INSERT INTO db01.t1
SELECT4 ,'lilu' ,2 ,'zhuhai' ;
[root@localhost mysql]#

mysql数据库数据的删除与恢复-编程知识网

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

mysql数据库数据的删除与恢复-编程知识网

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>

mysql数据库数据的删除与恢复-编程知识网

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

总结：

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

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

safe-updates

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

mysql数据库数据的删除与恢复-编程知识网

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>