注册 登录
MySQL社区 返回首页

贺春阳的技术专栏 http://www.mysqlpub.com/?11327 [收藏] [复制] [RSS] 《MySQL管理之道:性能调优、高可用与监控》china-pub首发!

日志

update忘加where条件误操作恢复过程演示

已有 2904 次阅读2012-12-6 11:41 | update后恢复

update、delete没有带where条件,误操作,如何恢复呢?

我现在有一张学生表,我要把小于60更新成不及格。

  1. mysql> select * from student; 
  2. +----+------+-------+-------+ 
  3. | id | name | class | score | 
  4. +----+------+-------+-------+ 
  5. |  1 | a    |     1 | 56    |  
  6. |  2 | b    |     1 | 61    |  
  7. |  3 | c    |     2 | 78    |  
  8. |  4 | d    |     2 | 45    |  
  9. |  5 | e    |     3 | 76    |  
  10. |  6 | f    |     3 | 89    |  
  11. |  7 | g    |     4 | 43    |  
  12. |  8 | h    |     4 | 90    |  
  13. +----+------+-------+-------+ 
  14. 8 rows in set (0.02 sec) 

结果,忘带where条件了,

  1. mysql> update student set score='failure'
  2. Query OK, 8 rows affected (0.11 sec) 
  3. Rows matched: 8  Changed: 8  Warnings: 0 
  4.  
  5. mysql> select * from student; 
  6. +----+------+-------+---------+ 
  7. | id | name | class | score   | 
  8. +----+------+-------+---------+ 
  9. |  1 | a    |     1 | failure |  
  10. |  2 | b    |     1 | failure |  
  11. |  3 | c    |     2 | failure |  
  12. |  4 | d    |     2 | failure |  
  13. |  5 | e    |     3 | failure |  
  14. |  6 | f    |     3 | failure |  
  15. |  7 | g    |     4 | failure |  
  16. |  8 | h    |     4 | failure |  
  17. +----+------+-------+---------+ 
  18. 8 rows in set (0.01 sec) 

把整张表的记录都给更新成不及格了。

传统的方法是:利用最近的全量备份+增量binlog备份,恢复到误操作之前的状态,那么随着表的记录增大,binlog的增多,恢复起来很费时费力。

现在通过一个简单的方法,可以恢复到误操作之前的状态。

我的binlog日志设置为binlog_format = ROW,如果是STATEMENT,这个方法是无效的。切记!!!

首先,创建一个普通权限的账号(切记不能是SUPER权限),例如:

  1. GRANT ALL PRIVILEGES ON yourDB.* TO 'admin_read_only'@'%' IDENTIFIED BY '123456'; 
  2. flush privileges; 

把read_only打开,设置数据库只读,

  1. mysql> set global read_only = 1
  2. Query OK, 0 rows affected (0.01 sec) 

把刚才创建的admin_read_only账号给运维,让运维把前端程序(PHP/JSP/.NET等)的用户名改下,然后重启前端程序(PHP/JSP/.NET等),这样再连接进来的用户对数据库的访问只能读不能写,保证恢复的一致性。

通过binlog先找到那条语句

  1. [root@M1 data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS 
  2. mysql-bin.000001 | grep -B 15 'failure'| more 
  3. /*!*/; 
  4. # at 192 
  5. #121124 23:55:15 server id 25  end_log_pos 249 CRC32 0x83a12fbc         Table_map: `test`.`student` mapped to number 76 
  6. # at 249 
  7. #121124 23:55:15 server id 25  end_log_pos 549 CRC32 0xcf7d2635         Update_rows: table id 76 flags: STMT_END_F 
  8. ### UPDATE test.student 
  9. ### WHERE 
  10. ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */ 
  11. ###   @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  12. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  13. ###   @4='56' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  14. ### SET 
  15. ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */ 
  16. ###   @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  17. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  18. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  19. ### UPDATE test.student 
  20. ### WHERE 
  21. ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
  22. ###   @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  23. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  24. ###   @4='61' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  25. ### SET 
  26. ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
  27. ###   @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  28. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  29. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  30. --More-- 

然后把那条binlog给导出来

  1. [root@M1 data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS 
  2. mysql-bin.000001 | sed -n '/# at 249/,/COMMIT/p' > /opt/1.txt   
  3. [root@M1 data]#  
  4. [root@M1 data]# more /opt/1.txt  
  5. # at 249 
  6. #121124 23:55:15 server id 25  end_log_pos 549 CRC32 0xcf7d2635         Update_rows: table id 76 flags: STMT_END_F 
  7. ### UPDATE test.student 
  8. ### WHERE 
  9. ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */ 
  10. ###   @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  11. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  12. ###   @4='56' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  13. ### SET 
  14. ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */ 
  15. ###   @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  16. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  17. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  18. ### UPDATE test.student 
  19. ### WHERE 
  20. ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
  21. ###   @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  22. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  23. ###   @4='61' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  24. ### SET 
  25. ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
  26. ###   @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  27. ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 
  28. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  29. ### UPDATE test.student 
  30. ### WHERE 
  31. ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
  32. ###   @2='c' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  33. ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */ 
  34. ###   @4='78' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  35. ### SET 
  36. ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
  37. ###   @2='c' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  38. ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */ 
  39. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  40. ### UPDATE test.student 
  41. ### WHERE 
  42. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
  43. ###   @2='d' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  44. ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */ 
  45. ###   @4='45' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  46. ### SET 
  47. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
  48. ###   @2='d' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  49. ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */ 
  50. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  51. ### UPDATE test.student 
  52. ### WHERE 
  53. ###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
  54. ###   @2='e' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  55. ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 
  56. ###   @4='76' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  57. ### SET 
  58. ###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
  59. ###   @2='e' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  60. ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 
  61. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  62. ### UPDATE test.student 
  63. ### WHERE 
  64. ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
  65. ###   @2='f' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  66. ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 
  67. ###   @4='89' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  68. ### SET 
  69. ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
  70. ###   @2='f' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  71. ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 
  72. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  73. ### UPDATE test.student 
  74. ### WHERE 
  75. ###   @1=7 /* INT meta=0 nullable=0 is_null=0 */ 
  76. ###   @2='g' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  77. ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 
  78. ###   @4='43' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  79. ### SET 
  80. ###   @1=7 /* INT meta=0 nullable=0 is_null=0 */ 
  81. ###   @2='g' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  82. ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 
  83. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  84. ### UPDATE test.student 
  85. ### WHERE 
  86. ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
  87. ###   @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  88. ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 
  89. ###   @4='90' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  90. ### SET 
  91. ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
  92. ###   @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  93. ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 
  94. ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 
  95. # at 549 
  96. #121124 23:55:15 server id 25  end_log_pos 580 CRC32 0x378c91b0         Xid = 531 
  97. COMMIT/*!*/; 
  98. [root@M1 data]# 

其中,这些是误操作之前的数据

  • ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
  • ###   @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  • ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 
  • ###   @4='90' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

这些是误操作之后的数据

  • ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
  • ###   @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 
  • ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 
  • ###   @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

这里,@1/@2/@3/@4对应的表字段是id,name,class,score

现在,就要进行最后一步的恢复操作了,只需把这些binlog转换成SQL语句,然后将其导入进去。

  1. [root@M1 opt]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 1.txt
  2. | sed -r '/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}' 
  3. | sed 's/### //g;s/\/\*.*/,/g' 
  4. | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' 
  5. | sed '/^$/d' > ./recover.sql 
  6. [root@M1 opt]#  
  7. [root@M1 opt]# cat recover.sql  
  8. UPDATE test.student 
  9. SET 
  10.   @11=1 , 
  11.   @2='a' , 
  12.   @3=1 , 
  13.   @4='56' , 
  14. WHERE 
  15.   @11=1 ; 
  16. UPDATE test.student 
  17. SET 
  18.   @1=2 , 
  19.   @2='b' , 
  20.   @3=1 , 
  21.   @4='61' , 
  22. WHERE 
  23.   @1=2 ; 
  24. UPDATE test.student 
  25. SET 
  26.   @1=3 , 
  27.   @2='c' , 
  28.   @3=2 , 
  29.   @4='78' , 
  30. WHERE 
  31.   @1=3 ; 
  32. UPDATE test.student 
  33. SET 
  34.   @1=4 , 
  35.   @2='d' , 
  36.   @3=2 , 
  37.   @4='45' , 
  38. WHERE 
  39.   @1=4 ; 
  40. UPDATE test.student 
  41. SET 
  42.   @1=5 , 
  43.   @2='e' , 
  44.   @33=3 , 
  45.   @4='76' , 
  46. WHERE 
  47.   @1=5 ; 
  48. UPDATE test.student 
  49. SET 
  50.   @1=6 , 
  51.   @2='f' , 
  52.   @33=3 , 
  53.   @4='89' , 
  54. WHERE 
  55.   @1=6 ; 
  56. UPDATE test.student 
  57. SET 
  58.   @1=7 , 
  59.   @2='g' , 
  60.   @3=4 , 
  61.   @4='43' , 
  62. WHERE 
  63.   @1=7 ; 
  64. UPDATE test.student 
  65. SET 
  66.   @1=8 , 
  67.   @2='h' , 
  68.   @3=4 , 
  69.   @4='90' , 
  70. WHERE 
  71.   @1=8 ; 
  72. [root@M1 opt]#  

再把@1/@2/@3/@4对应的表字段是id,name,class,score,替换掉

  1. [root@M1 opt]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql 
  2. [root@M1 opt]# sed -i -r 's/(score=.*),/\1/g' recover.sql
    [root@M1 opt]#
    [root@M1 opt]# cat recover.sql
    UPDATE test.student
    SET
    id=1 ,
    name='a' ,
    class=1 ,
    score='56'
    WHERE
    id=1 ;
    UPDATE test.student
    SET
    id=2 ,
    name='b' ,
    class=1 ,
    score='61'
    WHERE
    id=2 ;
    UPDATE test.student
    SET
    id=3 ,
    name='c' ,
    class=2 ,
    score='78'
    WHERE
    id=3 ;
    UPDATE test.student
    SET
    id=4 ,
    name='d' ,
    class=2 ,
    score='45'
    WHERE
    id=4 ;
    UPDATE test.student
    SET
    id=5 ,
    name='e' ,
    class=3 ,
    score='76'
    WHERE
    id=5 ;
    UPDATE test.student
    SET
    id=6 ,
    name='f' ,
    class=3 ,
    score='89'
    WHERE
    id=6 ;
    UPDATE test.student
    SET
    id=7 ,
    name='g' ,
    class=4 ,
    score='43'
    WHERE
    id=7 ;
    UPDATE test.student
    SET
    id=8 ,
    name='h' ,
    class=4 ,
    score='90'
    WHERE
    id=8 ;
    [root@M1 opt]#

OK。最激动人心的一幕到来了,我们进行恢复:

  1. mysql> select * from student; 
  2. +----+------+-------+---------+ 
  3. | id | name | class | score   | 
  4. +----+------+-------+---------+ 
  5. |  1 | a    |     1 | failure |  
  6. |  2 | b    |     1 | failure |  
  7. |  3 | c    |     2 | failure |  
  8. |  4 | d    |     2 | failure |  
  9. |  5 | e    |     3 | failure |  
  10. |  6 | f    |     3 | failure |  
  11. |  7 | g    |     4 | failure |  
  12. |  8 | h    |     4 | failure |  
  13. +----+------+-------+---------+ 
  14. 8 rows in set (0.02 sec) 
  15.  
  16. mysql> source /opt/recover.sql 
  17. Query OK, 1 row affected (0.11 sec) 
  18. Rows matched: 1  Changed: 1  Warnings: 0 
  19.  
  20. Query OK, 1 row affected (0.95 sec) 
  21. Rows matched: 1  Changed: 1  Warnings: 0 
  22.  
  23. Query OK, 1 row affected (0.16 sec) 
  24. Rows matched: 1  Changed: 1  Warnings: 0 
  25.  
  26. Query OK, 1 row affected (0.03 sec) 
  27. Rows matched: 1  Changed: 1  Warnings: 0 
  28.  
  29. Query OK, 1 row affected (0.80 sec) 
  30. Rows matched: 1  Changed: 1  Warnings: 0 
  31.  
  32. Query OK, 1 row affected (0.08 sec) 
  33. Rows matched: 1  Changed: 1  Warnings: 0 
  34.  
  35. Query OK, 1 row affected (0.09 sec) 
  36. Rows matched: 1  Changed: 1  Warnings: 0 
  37.  
  38. Query OK, 1 row affected (0.07 sec) 
  39. Rows matched: 1  Changed: 1  Warnings: 0 
  40.  
  41. mysql> select * from student;  
  42. +----+------+-------+-------+ 
  43. | id | name | class | score | 
  44. +----+------+-------+-------+ 
  45. |  1 | a    |     1 | 56    |  
  46. |  2 | b    |     1 | 61    |  
  47. |  3 | c    |     2 | 78    |  
  48. |  4 | d    |     2 | 45    |  
  49. |  5 | e    |     3 | 76    |  
  50. |  6 | f    |     3 | 89    |  
  51. |  7 | g    |     4 | 43    |  
  52. |  8 | h    |     4 | 90    |  
  53. +----+------+-------+-------+ 
  54. 8 rows in set (0.02 sec) 
  55.  
  56. mysql>  

大功告成!

你也快动手试试吧!


评论 (0 个评论)

facelist doodle 涂鸦板

您需要登录后才可以评论 登录 | 注册

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-3-28 18:48 , Processed in 0.060731 second(s), 19 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

返回顶部