前提: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' 因?yàn)楦暮蟮淖侄沃禐閠est,所以我們這里選test前15行和之后所有的數(shù)據(jù).

# 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 */


這就是我們需要的日志,撈取這部分?jǐn)?shù)據(jù)
[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'開始,到第一個(gè)commit結(jié)束的內(nèi)容,然后導(dǎo)到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 ~]#


現(xiàn)在開始對撈取的這部分?jǐn)?shù)據(jù)進(jìn)行轉(zhuǎn)換,轉(zhuǎn)換成能執(zhí)行的sql格式.其中會(huì)用到大量的sed命令,sed本人也不精通,我會(huì)解釋每個(gè)sed執(zhí)行后達(dá)到的目的,具體關(guān)于sed命令參數(shù)等,請另行參考;


[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的各個(gè)命令逐步解釋:
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.因?yàn)樵赽in-log日志里面,where后面的是更改前的數(shù)據(jù),set后面是update后的數(shù)據(jù),現(xiàn)在我們要回滾到update前的數(shù)據(jù)所以要對where和set進(jìn)行對換.
|sed 's/###//g;s/\/\*.*/,/g'
這一個(gè)sed的目的是將binlog日志里面的###和/*...*/去掉.
|sed -r '/WHERE/{:a;N;/@3/!ba;s/@2.*//g}'
這一行是把非關(guān)鍵字段去掉.做為where條件,也可以只去掉只被update的字段,可以自由選擇,只要保證條件查詢出來的是唯一行就可以.
|sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g'
這一行表示在每條語句結(jié)尾加上';',mysql的結(jié)束符號是';',所以需要加上這個(gè)一個(gè)結(jié)束符.
| sed '/^$/d'
一行表示去除多余的,比較簡單.不多解釋.


sed -i -r 's/(@3=.*),/\1/g' 9.txt   將set最后一個(gè)字段(這里是@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撈取完畢,執(zhí)行下改文件即可.這里不累述.

ps:本次測試,其實(shí)是有問題的,還原數(shù)據(jù)的時(shí)候,導(dǎo)致數(shù)據(jù)沒辦法還原,這是因?yàn)槲以跍y試的表里,沒有唯一值,所以,各位測試的時(shí)候,一定要找有唯一值的表進(jìn)行測試,沒有唯一值,將導(dǎo)致還原的時(shí)候,數(shù)據(jù)無法還原到原來的模樣..


分享題目：mysql中update誤操作,利用binlog日志,模擬oracle閃回功能.
分享URL：http://weahome.cn/article/iisccp.html