前提:binlog模式为row,隔离模式为read-committed
对于update误操作,可以模拟oralce 的闪回功能,利用binlog日志,具体操作如下:
MySQL> select * from test1;
+------+---------+--------+
| dept | name | salary |
+------+---------+--------+
| it | gaopeng | 100 |
| it | yhb | 100 |
| it | dzy | 100 |
| uu | yl | 100 |
| uu | yl1 | 200 |
| uu | yl3 | 300 |
+------+---------+--------+
6 rows in set (0.05 sec)
mysql> update test1 set name='test';
Query OK, 6 rows affected (0.06 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from test1;
+------+------+--------+
| dept | name | salary |
+------+------+--------+
| it | test | 100 |
| it | test | 100 |
| it | test | 100 |
| uu | test | 100 |
| uu | test | 200 |
| uu | test | 300 |
+------+------+--------+
6 rows in set (0.00 sec)
mysql> exit
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v db-bin.000016 |grep -B 15 'test'|more
ps: grep -B 15 'test' 因为更改后的字段值为test,所以我们这里选test前15行和之后所有的数据.
# at 384
#150424 14:07:59 server id 199 end_log_pos 456 CRC32 0x7b4aabf1 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1429855679/*!*/;
BEGIN
/*!*/;
# at 456
#150424 14:07:59 server id 199 end_log_pos 510 CRC32 0x5f63d428 Table_map: `test`.`test1` mapped to number 74
# at 510
#150424 14:07:59 server id 199 end_log_pos 699 CRC32 0xf362ace6 Update_rows: table id 74 flags: STMT_END_F
### UPDATE `test`.`test1`
### WHERE
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='gaopeng' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yhb' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='dzy' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yl' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yl1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=200 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=200 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yl3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=300 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
这就是我们需要的日志,捞取这部分数据
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v -v db-bin.000016 | sed -n '/# at 510/,/COMMIT/p'>/root/1.txt
ps:sed -n '/#at 510/,/COMMIT/p' 表示从选取'# at 510'开始,到第一个commit结束的内容,然后导到1.txt文件.
捞取之后的文件如下:
[root@localhost ~]# cat 1.txt
# at 510
#150424 14:07:59 server id 199 end_log_pos 699 CRC32 0xf362ace6 Update_rows: table id 74 flags: STMT_END_F
### UPDATE `test`.`test1`
### WHERE
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='gaopeng' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yhb' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='dzy' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='it' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yl' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yl1' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=200 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=200 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `test`.`test1`
### WHERE
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='yl3' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=300 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1='uu' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3=300 /* INT meta=0 nullable=1 is_null=0 */
# at 699
#150424 14:07:59 server id 199 end_log_pos 730 CRC32 0x83588cbb Xid = 44
COMMIT/*!*/;
[root@localhost ~]#
现在开始对捞取的这部分数据进行转换,转换成能执行的sql格式.其中会用到大量的sed命令,sed本人也不精通,我会解释每个sed执行后达到的目的,具体关于sed命令参数等,请另行参考;
[root@localhost ~]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 4.txt |sed 's/###//g;s/\/\*.*/,/g' |sed -r '/WHERE/{:a;N;/@3/!ba;s/@2.*//g}' |sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' >9.sql
[root@localhost ~]#cat 9.sql
UPDATE `test`.`test1`
SET
@1='it' ,
@2='gaopeng' ,
@3=100 ,
WHERE
@1='it' ;
UPDATE `test`.`test1`
SET
@1='it' ,
@2='yhb' ,
@3=100 ,
WHERE
@1='it' ;
UPDATE `test`.`test1`
SET
@1='it' ,
@2='dzy' ,
@3=100 ,
WHERE
@1='it' ;
UPDATE `test`.`test1`
SET
@1='uu' ,
@2='yl' ,
@3=100 ,
WHERE
@1='uu' ;
UPDATE `test`.`test1`
SET
@1='uu' ,
@2='yl1' ,
@3=200 ,
WHERE
@1='uu' ;
UPDATE `test`.`test1`
SET
@1='uu' ,
@2='yl3' ,
@3=300 ,
WHERE
@1='uu' ;
[root@localhost ~]# sed -i -r 's/(@3=.*),/\1/g' 9.sql
[root@localhost ~]# sed -i 's/@1/dept/g;s/@2/name/g;s/@3/salary/g' 9.sql
对sed的各个命令逐步解释:
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 4.txt |sed 's/###//g;s/\/\*.*/,/g' |sed -r '/WHERE/{:a;N;/@3/!ba;s/@2.*//g}' |sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d'
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 4.txt
这行表示把日志里的where换成set,把set换成where.因为在bin-log日志里面,where后面的是更改前的数据,set后面是update后的数据,现在我们要回滚到update前的数据所以要对where和set进行对换.
|sed 's/###//g;s/\/\*.*/,/g'
这一个sed的目的是将binlog日志里面的###和/*...*/去掉.
|sed -r '/WHERE/{:a;N;/@3/!ba;s/@2.*//g}'
这一行是把非关键字段去掉.做为where条件,也可以只去掉只被update的字段,可以自由选择,只要保证条件查询出来的是唯一行就可以.
|sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g'
这一行表示在每条语句结尾加上';',mysql的结束符号是';',所以需要加上这个一个结束符.
| sed '/^$/d'
一行表示去除多余的,比较简单.不多解释.
sed -i -r 's/(@3=.*),/\1/g' 9.txt 将set最后一个字段(这里是@3)后面的','去掉.
sed -i 's/@1/dept/g;s/@2/name/g;s/@3/salary/g' 9.txt 将@1,@2,@3 换成表中的字段名.
[root@localhost ~]# more 9.txt
UPDATE `test`.`test1`
SET
dept='it' ,
name='gaopeng' ,
salary=100
WHERE
dept='it' ;
UPDATE `test`.`test1`
SET
dept='it' ,
name='yhb' ,
salary=100
WHERE
dept='it' ;
UPDATE `test`.`test1`
SET
dept='it' ,
name='dzy' ,
salary=100
WHERE
dept='it' ;
UPDATE `test`.`test1`
SET
dept='uu' ,
name='yl' ,
salary=100
WHERE
dept='uu' ;
UPDATE `test`.`test1`
SET
dept='uu' ,
name='yl1' ,
salary=200
WHERE
dept='uu' ;
UPDATE `test`.`test1`
SET
dept='uu' ,
name='yl3' ,
salary=300
WHERE
dept='uu' ;
到此,需要回滚的sql捞取完毕,执行下改文件即可.这里不累述.
ps:本次测试,其实是有问题的,还原数据的时候,导致数据没办法还原,这是因为我在测试的表里,没有唯一值,所以,各位测试的时候,一定要找有唯一值的表进行测试,没有唯一值,将导致还原的时候,数据无法还原到原来的模样..
名称栏目：mysql中update误操作,利用binlog日志,模拟oracle闪回功能.
链接分享：
http://shouzuofang.com/article/iisccp.html