MySQL常见错误 -- update忘加where条件误操作恢复（模拟Oracle闪回功能）

开启binlog，且binlog-format=row

新建一个表

[sql]CREATE TABLE student (id int(10) unsigned NOT NULL AUTO_INCREMENT,name varchar(10) NOT NULL DEFAULT '', class int(10), score varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (id));[/sql]

插入测试数据：

insert into student(name,class,score) values('a',1,56),('b',1,61),('c',2,78),('d',2,45),('e',3,76),('f',3,89),('g',4,43),('h',4,90);

模拟update忘加where条件

update student set score='failure';

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | grep -B 15 'failure'

/*!*/;
# at 8513042
#160323  9:50:53 server id 1  end_log_pos 8513098 CRC32 0xb96214ed     Table_map: `abc`.`student` mapped to number 151
# at 8513098
#160323  9:50:53 server id 1  end_log_pos 8513398 CRC32 0xf3c6e6c0     Update_rows: table id 151 flags: STMT_END_F
### UPDATE `abc`.`student`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='56' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### UPDATE `abc`.`student`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='61' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### UPDATE `abc`.`student`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='78' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### UPDATE `abc`.`student`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='45' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### UPDATE `abc`.`student`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='e' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
###   @4='76' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='e' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### UPDATE `abc`.`student`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='f' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
###   @4='89' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='f' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### UPDATE `abc`.`student`
### WHERE
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='g' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='43' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='g' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### UPDATE `abc`.`student`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='90' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | sed -n '/# at 8513098/,/COMMIT/p' > ~/1.txt

sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' ~/1.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' | sed 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' | sed -r 's/(score=.*),/\1/g' > ~/recovery.sql

sed解释

sed -n '/# at 8513098/,/COMMIT/p'
#打印# at 8513098到COMMIT之间的行

sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}'
#匹配WHERE，如果匹配成功执行{}的命令
#\([^\n]*\)\n\(.*\)\n\(.*\)/
#|————————| #第一个回车之前的，就是WHERE
#@@@@@@@@@@ #\n
#@@@@@@@@@@@@|—————| #.*最大匹配，一直到最后一个\n之前，也就是\nSET之前的
#@@@@@@@@@@@@@@@@@@@ #\n
#@@@@@@@@@@@@@@@@@@@@@—————— #最后一行是SET

#\3\n\2\n\1  1和3调换

sed -r '/WHERE/{:a;N;/@4/!ba;s/@2.*//g}'
#将@2到@4的内容删除

sed 's/### //g;s/\/\*.*\*\//,/g'
#删除### 
#删除/*...*/


sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT.*//g'
#在WHERE\n  @1=1 ,后面加;
#删除COMMIT所在行

sed '/^$/d' #删除空行

sed 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g'
#替换以下字段
#@1 =&gt; id
#@2 =&gt; name 
#@3 =&gt; class
#@4 =&gt; score

sed -r 's/(score=.*),/\1/g' #删除score那行后面的,

cat ~/recovery.sql

UPDATE `abc`.`student`
SET
  id=1 ,
  name='a' ,
  class=1 ,
  score='56' 
WHERE
  id=1 ;
   
UPDATE `abc`.`student`
SET
  id=2 ,
  name='b' ,
  class=1 ,
  score='61' 
WHERE
  id=2 ;
   
UPDATE `abc`.`student`
SET
  id=3 ,
  name='c' ,
  class=2 ,
  score='78' 
WHERE
  id=3 ;
   
UPDATE `abc`.`student`
SET
  id=4 ,
  name='d' ,
  class=2 ,
  score='45' 
WHERE
  id=4 ;
   
UPDATE `abc`.`student`
SET
  id=5 ,
  name='e' ,
  class=3 ,
  score='76' 
WHERE
  id=5 ;
   
UPDATE `abc`.`student`
SET
  id=6 ,
  name='f' ,
  class=3 ,
  score='89' 
WHERE
  id=6 ;
   
UPDATE `abc`.`student`
SET
  id=7 ,
  name='g' ,
  class=4 ,
  score='43' 
WHERE
  id=7 ;
   
UPDATE `abc`.`student`
SET
  id=8 ,
  name='h' ,
  class=4 ,
  score='90' 
WHERE
  id=8 ;

mysql> select * from student;
+----+------+-------+---------+
| id | name | class | score   |
+----+------+-------+---------+
|  1 | a    |     1 | failure |
|  2 | b    |     1 | failure |
|  3 | c    |     2 | failure |
|  4 | d    |     2 | failure |
|  5 | e    |     3 | failure |
|  6 | f    |     3 | failure |
|  7 | g    |     4 | failure |
|  8 | h    |     4 | failure |
+----+------+-------+---------+
8 rows in set (0.00 sec)
 
mysql&gt; source ~/recovery.sql
Query OK, 1 row affected (0.01 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.00 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.00 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
 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>
mysql> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
|  1 | a    |     1 | 56    |
|  2 | b    |     1 | 61    |
|  3 | c    |     2 | 78    |
|  4 | d    |     2 | 45    |
|  5 | e    |     3 | 76    |
|  6 | f    |     3 | 89    |
|  7 | g    |     4 | 43    |
|  8 | h    |     4 | 90    |
+----+------+-------+-------+
8 rows in set (0.00 sec)

MySQL常见错误 -- update忘加where条件误操作恢复（模拟Oracle闪回功能）

新建一个表

插入测试数据：

模拟update忘加where条件

sed解释

添加新评论

最新文章

最近回复

分类

归档

其它