转自：https://www.cnblogs.com/gomysql/p/3582058.html
在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。
一. update 忘加where条件误操作恢复数据(binglog格式必须是ROW)
1.创建测试用的数据表
1. mysql> create table t1 (
2. -> id int unsigned not null auto_increment,
3. -> name char(20) not null,
4. -> sex enum('f','m') not null default 'm',
5. -> address varchar(30) not null,
6. -> primary key(id)
7. -> );
8. Query OK, 0 rows affected (0.31 sec)
9. mysql>
2.插入测试数据
1. mysql> insert into t1 (name,sex,address)values('daiiy','m','guangzhou');
2. Query OK, 1 row affected (0.01 sec)
4. mysql> insert into t1 (name,sex,address)values('tom','f','shanghai');
5. Query OK, 1 row affected (0.00 sec)
7. mysql> insert into t1 (name,sex,address)values('liany','m','beijing');
8. Query OK, 1 row affected (0.00 sec)
10. mysql> insert into t1 (name,sex,address)values('lilu','m','zhuhai');
11. Query OK, 1 row affected (0.05 sec)
13. mysql>
3.现在需要将id等于2的用户的地址改为zhuhai，update时没有添加where条件
1. mysql> select * from t1;
2. +----+-------+-----+-----------+
3. | id | name | sex | address |
4. +----+-------+-----+-----------+
5. | 1 | daiiy | m | guangzhou |
6. | 2 | tom | f | shanghai |
7. | 3 | liany | m | beijing |
8. | 4 | lilu | m | zhuhai |
9. +----+-------+-----+-----------+
10. 4 rows in set (0.01 sec)
12. mysql> update t1 set address='zhuhai';
13. Query OK, 3 rows affected (0.09 sec)
14. Rows matched: 4 Changed: 3 Warnings: 0
16. mysql> select * from t1;
17. +----+-------+-----+---------+
18. | id | name | sex | address |
19. +----+-------+-----+---------+
20. | 1 | daiiy | m | zhuhai |
21. | 2 | tom | f | zhuhai |
22. | 3 | liany | m | zhuhai |
23. | 4 | lilu | m | zhuhai |
24. +----+-------+-----+---------+
25. 4 rows in set (0.00 sec)
27. mysql>
4.开始恢复，在线上的话，应该比较复杂，要先进行锁表，以免数据再次被污染。（锁表，查看正在写哪个二进制日志）
1. mysql> lock tables t1 read ;
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> show master status;
5. +------------------+----------+--------------+------------------+
6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
7. +------------------+----------+--------------+------------------+
8. | mysql-bin.000024 | 1852 | | |
9. +------------------+----------+--------------+------------------+
10. 1 row in set (0.00 sec)
12. mysql>
5.分析二进制日志，并且在其中找到相关记录，在更新时是address='zhuhai',我们可以在日志中过滤出来。
1. [root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 'zhuhai' --表名
1. # at 1629
2. **# at 1679**
3. #140305 10:52:24 server id 1 end_log_pos 1679 Table_map: `db01`.`t1` mapped to number 38
4. #140305 10:52:24 server id 1 end_log_pos 1825 Update_rows: table id 38 flags: STMT_END_F
5. ### UPDATE db01.t1
6. ### WHERE
7. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
8. ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
9. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
10. ### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
11. ### SET
12. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
13. ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
14. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
15. ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
16. ### UPDATE db01.t1
17. ### WHERE
18. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
19. ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
20. ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
21. ### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
22. ### SET
23. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
24. ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
25. ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
26. ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
27. ### UPDATE db01.t1
28. ### WHERE
29. ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
30. ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
31. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
32. ### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
33. ### SET
34. ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
35. ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
36. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
37. ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
可以看见里面记录了每一行的变化，这也是binglog格式要一定是row才行的原因。其中@1,@2,@3,@4,分别对应表中id,name,sex,address字段。相信大家看到这里有点明白了吧，对，没错，你猜到了，我们将相关记录转换为sql语句，重新导入数据库。
6.处理分析处理的二进制日志
1. [root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | sed -n '/# at 1679/,/COMMIT/p' > t1.txt
2. [root@localhost mysql]# cat t1.txt
3. **# at** **1679**
4. #140305 10:52:24 server id 1 end_log_pos 1679 Table_map: `db01`.`t1` mapped to number 38
5. #140305 10:52:24 server id 1 end_log_pos 1825 Update_rows: table id 38 flags: STMT_END_F
6. ### UPDATE db01.t1
7. ### WHERE
8. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
9. ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
10. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
11. ### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
12. ### SET
13. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
14. ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
15. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
16. ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
17. ### UPDATE db01.t1
18. ### WHERE
19. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
20. ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
21. ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
22. ### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
23. ### SET
24. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
25. ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
26. ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
27. ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
28. ### UPDATE db01.t1
29. ### WHERE
30. ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
31. ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
32. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
33. ### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
34. ### SET
35. ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
36. ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
37. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
38. ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
39. # at 1825
40. #140305 10:52:24 server id 1 end_log_pos 1852 Xid = 26
41. COMMIT/*!*/;
42. [root@localhost mysql]#
这里sed有点复杂，需要童鞋们好好自己研究研究，这里我就不多说了。
1. [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
1. [root@localhost mysql]# cat recover.sql
2. UPDATE db01.t1
3. SET
4. @1=1 ,
5. @2='daiiy' ,
6. @3=2 ,
7. @4='guangzhou' ,
8. WHERE
9. @1=1 ;
10. UPDATE db01.t1
11. SET
12. @1=2 ,
13. @2='tom' ,
14. @3=1 ,
15. @4='shanghai' ,
16. WHERE
17. @1=2 ;
18. UPDATE db01.t1
19. SET
20. @1=3 ,
21. @2='liany' ,
22. @3=2 ,
23. @4='beijing' ,
24. WHERE
25. @1=3 ;
26. [root@localhost mysql]#
将文件中的@1,@2,@3,@4替换为t1表中id，name，sex，address字段，并删除最后字段的","号
1. [root@localhost mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql
2. [root@localhost mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql
3. [root@localhost mysql]# cat recover.sql
4. UPDATE db01.t1
5. SET
6. id=1 ,
7. name='daiiy' ,
8. sex=2 ,
9. address='guangzhou'
10. WHERE
11. id=1 ;
12. UPDATE db01.t1
13. SET
14. id=2 ,
15. name='tom' ,
16. sex=1 ,
17. address='shanghai'
18. WHERE
19. id=2 ;
20. UPDATE db01.t1
21. SET
22. id=3 ,
23. name='liany' ,
24. sex=2 ,
25. address='beijing'
26. WHERE
27. id=3 ;
28. [root@localhost mysql]#
7.到这里日志就处理好了，现在导入即可（导入数据后，解锁表）；
1. mysql> source recover.sql;
2. Query OK, 1 row affected (0.12 sec)
3. Rows matched: 1 Changed: 1 Warnings: 0
5. Query OK, 1 row affected (0.00 sec)
6. Rows matched: 1 Changed: 1 Warnings: 0
8. Query OK, 1 row affected (0.01 sec)
9. Rows matched: 1 Changed: 1 Warnings: 0
11. mysql> select * from t1;
12. +----+-------+-----+-----------+
13. | id | name | sex | address |
14. +----+-------+-----+-----------+
15. | 1 | daiiy | m | guangzhou |
16. | 2 | tom | f | shanghai |
17. | 3 | liany | m | beijing |
18. | 4 | lilu | m | zhuhai |
19. +----+-------+-----+-----------+
20. 4 rows in set (0.00 sec)
22. mysql>
可以看见数据已经完全恢复，这种方法的优点是快速，方便。
二. delete 忘加where条件误删除恢复(binglog格式必须是ROW)
其实这和update忘加条件差不多，不过这处理更简单,这里就用上面那张表做测试吧
1.模拟误删除数据
1. mysql> select * from t1;
2. +----+-------+-----+-----------+
3. | id | name | sex | address |
4. +----+-------+-----+-----------+
5. | 1 | daiiy | m | guangzhou |
6. | 2 | tom | f | shanghai |
7. | 3 | liany | m | beijing |
8. | 4 | lilu | m | zhuhai |
9. +----+-------+-----+-----------+
10. 4 rows in set (0.00 sec)
12. mysql> delete from t1;
13. Query OK, 4 rows affected (0.03 sec)
15. mysql> select * from t1;
16. Empty set (0.00 sec)
18. mysql>
2.在binglog中去查找相关记录
1. [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
2. [root@localhost mysql]# cat delete.txt
3. ### DELETE FROM db01.t1
4. ### WHERE
5. ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
6. ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
7. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
8. ### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
9. ### DELETE FROM db01.t1
10. ### WHERE
11. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
12. ### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
13. ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
14. ### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
15. ### DELETE FROM db01.t1
16. ### WHERE
17. ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
18. ### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
19. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
20. ### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
21. ### DELETE FROM db01.t1
22. ### WHERE
23. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
24. ### @2='lilu' /* STRING(60) meta=65084 nullable=0 is_null=0 */
25. ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
26. ### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
27. # at 2719
28. #140305 11:41:00 server id 1 end_log_pos 2746 Xid = 78
29. COMMIT/*!*/;
30. [root@localhost mysql]#
3.将记录转换为SQL语句
1. [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
2. [root@localhost mysql]# cat t1.sql
3. INSERT INTO db01.t1
4. SELECT
5. 1 ,
6. 'daiiy' ,
7. 2 ,
8. 'guangzhou' ;
9. INSERT INTO db01.t1
10. SELECT
11. 2 ,
12. 'tom' ,
13. 1 ,
14. 'shanghai' ;
15. INSERT INTO db01.t1
16. SELECT
17. 3 ,
18. 'liany' ,
19. 2 ,
20. 'beijing' ;
21. INSERT INTO db01.t1
22. SELECT
23. 4 ,
24. 'lilu' ,
25. 2 ,
26. 'zhuhai' ;
27. [root@localhost mysql]#
4.导入数据，验证数据完整性
1. mysql> source t1.sql;
2. Query OK, 1 row affected (0.00 sec)
3. Records: 1 Duplicates: 0 Warnings: 0
5. Query OK, 1 row affected (0.02 sec)
6. Records: 1 Duplicates: 0 Warnings: 0
8. Query OK, 1 row affected (0.02 sec)
9. Records: 1 Duplicates: 0 Warnings: 0
11. Query OK, 1 row affected (0.01 sec)
12. Records: 1 Duplicates: 0 Warnings: 0
14. mysql> select * from t1;
15. ERROR 1046 (3D000): No database selected
16. mysql> select * from db01.t1;
17. +----+-------+-----+-----------+
18. | id | name | sex | address |
19. +----+-------+-----+-----------+
20. | 1 | daiiy | m | guangzhou |
21. | 2 | tom | f | shanghai |
22. | 3 | liany | m | beijing |
23. | 4 | lilu | m | zhuhai |
24. +----+-------+-----+-----------+
25. 4 rows in set (0.00 sec)
27. mysql>
到这里数据就完整回来了。将binglog格式设置为row有利有弊，好处是记录了每一行的实际变化，在主从复制时也不容易出问题。但是由于记录每行的变化，会占用大量磁盘，主从复制时带宽占用会有所消耗。到底是使用row还是mixed，需要在实际工作中自己去衡量，但从整体上来说，binglog的格式设置为row，都是不二的选择。
总结：
所以在数据库操作的过程中我们需要格外小心，当然开发那边我们需要做好权限的控制，不过有一个参数可以解决我们的问题，让我们不用担心类似的问题发生：
在[mysql]段落开启这个参数：
1. safe-updates
这样当我们在做DML操作时忘记加where条件时，mysqld服务器是不会执行操作的：
1. mysql> select * from t1;
2. +----+------------------+
3. | id | name |
4. +----+------------------+
5. | 1 | yayun |
6. | 2 | atlas |
7. | 3 | mysql |
8. | 6 | good yayun heheh |
9. +----+------------------+
10. 4 rows in set (0.00 sec)
12. mysql> delete from t1;
13. ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
14. mysql>
网友评论