首页 > 代码库 > delete、update忘加where条件误操作恢复过程演示

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

2024-10-31 02:30:02   209人阅读

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

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

 1 mysql> select * from student;  2  3 +----+------+-------+-------+  4  5 | id | name | class | score |  6  7 +----+------+-------+-------+  8  9 |  1 | a    |     1 | 56    |  10 11 |  2 | b    |     1 | 61    |  12 13 |  3 | c    |     2 | 78    |  14 15 |  4 | d    |     2 | 45    |  16 17 |  5 | e    |     3 | 76    |  18 19 |  6 | f    |     3 | 89    |  20 21 |  7 | g    |     4 | 43    |  22 23 |  8 | h    |     4 | 90    |  24 25 +----+------+-------+-------+ 26 27 8 rows in set (0.02 sec)

 

结果，忘带where条件了，

 1 mysql> update student set score=‘failure‘;  2  3 Query OK, 8 rows affected (0.11 sec)  4  5 Rows matched: 8  Changed: 8  Warnings: 0  6  7   8  9 mysql> select * from student; 10 11 +----+------+-------+---------+ 12 13 | id | name | class | score   | 14 15 +----+------+-------+---------+ 16 17 |  1 | a    |     1 | failure |  18 19 |  2 | b    |     1 | failure |  20 21 |  3 | c    |     2 | failure |  22 23 |  4 | d    |     2 | failure |  24 25 |  5 | e    |     3 | failure |  26 27 |  6 | f    |     3 | failure |  28 29 |  7 | g    |     4 | failure |  30 31 |  8 | h    |     4 | failure |  32 33 +----+------+-------+---------+ 34 35 8 rows in set (0.01 sec)

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

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

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

我的binlog日志设置为binlog_format = ROW，

首先，创建一个普通权限的账号（切记不能是SUPER权限），例如：

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

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

1 mysql> set global read_only = 1; 2 3 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  3 mysql-bin.000001 | grep -B 15 ‘failure‘| more  4  5 /*!*/;  6  7 # at 192  8  9 #121124 23:55:15 server id 25  end_log_pos 249 CRC32 0x83a12fbc         Table_map: `test`.`student` mapped to number 76 10 11 # at 249 12 13 #121124 23:55:15 server id 25  end_log_pos 549 CRC32 0xcf7d2635         Update_rows: table id 76 flags: STMT_END_F 14 15 ### UPDATE test.student 16 17 ### WHERE 18 19 ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */ 20 21 ###   @2=‘a‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 22 23 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 24 25 ###   @4=‘56‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 26 27 ### SET 28 29 ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */ 30 31 ###   @2=‘a‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 32 33 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 34 35 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 36 37 ### UPDATE test.student 38 39 ### WHERE 40 41 ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 42 43 ###   @2=‘b‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 44 45 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 46 47 ###   @4=‘61‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 48 49 ### SET 50 51 ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 52 53 ###   @2=‘b‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 54 55 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */ 56 57 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 58 59 --More--

然后把那条binlog给导出来

  1 [root@M1 data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS   2   3 mysql-bin.000001 | sed -n ‘/# at 249/,/COMMIT/p‘ > /opt/1.txt     4   5 [root@M1 data]#    6   7 [root@M1 data]# more /opt/1.txt    8   9 # at 249  10  11 #121124 23:55:15 server id 25  end_log_pos 549 CRC32 0xcf7d2635         Update_rows: table id 76 flags: STMT_END_F  12  13 ### UPDATE test.student  14  15 ### WHERE  16  17 ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */  18  19 ###   @2=‘a‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  20  21 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */  22  23 ###   @4=‘56‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */  24  25 ### SET  26  27 ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */  28  29 ###   @2=‘a‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  30  31 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */  32  33 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */  34  35 ### UPDATE test.student  36  37 ### WHERE  38  39 ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */  40  41 ###   @2=‘b‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  42  43 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */  44  45 ###   @4=‘61‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */  46  47 ### SET  48  49 ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */  50  51 ###   @2=‘b‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  52  53 ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */  54  55 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */  56  57 ### UPDATE test.student  58  59 ### WHERE  60  61 ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */  62  63 ###   @2=‘c‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  64  65 ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */  66  67 ###   @4=‘78‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */  68  69 ### SET  70  71 ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */  72  73 ###   @2=‘c‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  74  75 ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */  76  77 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */  78  79 ### UPDATE test.student  80  81 ### WHERE  82  83 ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */  84  85 ###   @2=‘d‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  86  87 ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */  88  89 ###   @4=‘45‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */  90  91 ### SET  92  93 ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */  94  95 ###   @2=‘d‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */  96  97 ###   @3=2 /* INT meta=0 nullable=1 is_null=0 */  98  99 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 100 101 ### UPDATE test.student 102 103 ### WHERE 104 105 ###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 106 107 ###   @2=‘e‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 108 109 ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 110 111 ###   @4=‘76‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 112 113 ### SET 114 115 ###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 116 117 ###   @2=‘e‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 118 119 ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 120 121 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 122 123 ### UPDATE test.student 124 125 ### WHERE 126 127 ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 128 129 ###   @2=‘f‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 130 131 ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 132 133 ###   @4=‘89‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 134 135 ### SET 136 137 ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 138 139 ###   @2=‘f‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 140 141 ###   @33=3 /* INT meta=0 nullable=1 is_null=0 */ 142 143 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 144 145 ### UPDATE test.student 146 147 ### WHERE 148 149 ###   @1=7 /* INT meta=0 nullable=0 is_null=0 */ 150 151 ###   @2=‘g‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 152 153 ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 154 155 ###   @4=‘43‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 156 157 ### SET 158 159 ###   @1=7 /* INT meta=0 nullable=0 is_null=0 */ 160 161 ###   @2=‘g‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 162 163 ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 164 165 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 166 167 ### UPDATE test.student 168 169 ### WHERE 170 171 ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 172 173 ###   @2=‘h‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 174 175 ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 176 177 ###   @4=‘90‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 178 179 ### SET 180 181 ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 182 183 ###   @2=‘h‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 184 185 ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 186 187 ###   @4=‘failure‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 188 189 # at 549 190 191 #121124 23:55:15 server id 25  end_log_pos 580 CRC32 0x378c91b0         Xid = 531 192 193 COMMIT/*!*/; 194 195 [root@M1 data]#

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

1 ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 2 3 ###   @2=‘h‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 4 5 ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 6 7 ###   @4=‘90‘ /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

这些是误操作之后的数据

1 ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 2 3 ###   @2=‘h‘ /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 4 5 ###   @3=4 /* INT meta=0 nullable=1 is_null=0 */ 6 7 ###   @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   3 | sed -r ‘/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}‘   4   5 | sed ‘s/### //g;s/\/\*.*/,/g‘   6   7 | sed ‘/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g‘   8   9 | sed ‘/^$/d‘ > ./recover.sql  10  11 [root@M1 opt]#   12  13 [root@M1 opt]# cat recover.sql   14  15 UPDATE test.student  16  17 SET  18  19   @11=1 ,  20  21   @2=‘a‘ ,  22  23   @3=1 ,  24  25   @4=‘56‘ ,  26  27 WHERE  28  29   @11=1 ;  30  31 UPDATE test.student  32  33 SET  34  35   @1=2 ,  36  37   @2=‘b‘ ,  38  39   @3=1 ,  40  41   @4=‘61‘ ,  42  43 WHERE  44  45   @1=2 ;  46  47 UPDATE test.student  48  49 SET  50  51   @1=3 ,  52  53   @2=‘c‘ ,  54  55   @3=2 ,  56  57   @4=‘78‘ ,  58  59 WHERE  60  61   @1=3 ;  62  63 UPDATE test.student  64  65 SET  66  67   @1=4 ,  68  69   @2=‘d‘ ,  70  71   @3=2 ,  72  73   @4=‘45‘ ,  74  75 WHERE  76  77   @1=4 ;  78  79 UPDATE test.student  80  81 SET  82  83   @1=5 ,  84  85   @2=‘e‘ ,  86  87   @33=3 ,  88  89   @4=‘76‘ ,  90  91 WHERE  92  93   @1=5 ;  94  95 UPDATE test.student  96  97 SET  98  99   @1=6 , 100 101   @2=‘f‘ , 102 103   @33=3 , 104 105   @4=‘89‘ , 106 107 WHERE 108 109   @1=6 ; 110 111 UPDATE test.student 112 113 SET 114 115   @1=7 , 116 117   @2=‘g‘ , 118 119   @3=4 , 120 121   @4=‘43‘ , 122 123 WHERE 124 125   @1=7 ; 126 127 UPDATE test.student 128 129 SET 130 131   @1=8 , 132 133   @2=‘h‘ , 134 135   @3=4 , 136 137   @4=‘90‘ , 138 139 WHERE 140 141   @1=8 ; 142 143 [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  3 [root@M1 opt]# sed -i -r ‘s/(score=.*),/\1/g‘ recover.sql   4 [root@M1 opt]#  5 [root@M1 opt]# cat recover.sql  6 UPDATE test.student 7 SET 8   id=1 , 9   name=‘a‘ ,10   class=1 ,11   score=‘56‘ 12 WHERE13   id=1 ;14 UPDATE test.student15 SET16   id=2 ,17   name=‘b‘ ,18   class=1 ,19   score=‘61‘ 20 WHERE21   id=2 ;22 UPDATE test.student23 SET24   id=3 ,25   name=‘c‘ ,26   class=2 ,27   score=‘78‘ 28 WHERE29   id=3 ;30 UPDATE test.student31 SET32   id=4 ,33   name=‘d‘ ,34   class=2 ,35   score=‘45‘ 36 WHERE37   id=4 ;38 UPDATE test.student39 SET40   id=5 ,41   name=‘e‘ ,42   class=3 ,43   score=‘76‘ 44 WHERE45   id=5 ;46 UPDATE test.student47 SET48   id=6 ,49   name=‘f‘ ,50   class=3 ,51   score=‘89‘ 52 WHERE53   id=6 ;54 UPDATE test.student55 SET56   id=7 ,57   name=‘g‘ ,58   class=4 ,59   score=‘43‘ 60 WHERE61   id=7 ;62 UPDATE test.student63 SET64   id=8 ,65   name=‘h‘ ,66   class=4 ,67   score=‘90‘ 68 WHERE69   id=8 ;70 [root@M1 opt]#

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

  1 mysql> select * from student;   2   3 +----+------+-------+---------+   4   5 | id | name | class | score   |   6   7 +----+------+-------+---------+   8   9 |  1 | a    |     1 | failure |   10  11 |  2 | b    |     1 | failure |   12  13 |  3 | c    |     2 | failure |   14  15 |  4 | d    |     2 | failure |   16  17 |  5 | e    |     3 | failure |   18  19 |  6 | f    |     3 | failure |   20  21 |  7 | g    |     4 | failure |   22  23 |  8 | h    |     4 | failure |   24  25 +----+------+-------+---------+  26  27 8 rows in set (0.02 sec)  28  29   30  31 mysql> source /opt/recover.sql  32  33 Query OK, 1 row affected (0.11 sec)  34  35 Rows matched: 1  Changed: 1  Warnings: 0  36  37   38  39 Query OK, 1 row affected (0.95 sec)  40  41 Rows matched: 1  Changed: 1  Warnings: 0  42  43   44  45 Query OK, 1 row affected (0.16 sec)  46  47 Rows matched: 1  Changed: 1  Warnings: 0  48  49   50  51 Query OK, 1 row affected (0.03 sec)  52  53 Rows matched: 1  Changed: 1  Warnings: 0  54  55   56  57 Query OK, 1 row affected (0.80 sec)  58  59 Rows matched: 1  Changed: 1  Warnings: 0  60  61   62  63 Query OK, 1 row affected (0.08 sec)  64  65 Rows matched: 1  Changed: 1  Warnings: 0  66  67   68  69 Query OK, 1 row affected (0.09 sec)  70  71 Rows matched: 1  Changed: 1  Warnings: 0  72  73   74  75 Query OK, 1 row affected (0.07 sec)  76  77 Rows matched: 1  Changed: 1  Warnings: 0  78  79   80  81 mysql> select * from student;   82  83 +----+------+-------+-------+  84  85 | id | name | class | score |  86  87 +----+------+-------+-------+  88  89 |  1 | a    |     1 | 56    |   90  91 |  2 | b    |     1 | 61    |   92  93 |  3 | c    |     2 | 78    |   94  95 |  4 | d    |     2 | 45    |   96  97 |  5 | e    |     3 | 76    |   98  99 |  6 | f    |     3 | 89    |  100 101 |  7 | g    |     4 | 43    |  102 103 |  8 | h    |     4 | 90    |  104 105 +----+------+-------+-------+ 106 107 8 rows in set (0.02 sec) 
1 mysql>

出处http://hcymysql.blog.51cto.com/5223301/1070148

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


联系
我们