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……
转自: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> 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',我们可以在日志中过滤出来。
[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 = 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.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: 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>
可以看见数据已经完全恢复,这种方法的优点是快速,方便。
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 = 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.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: 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>