本实验模拟把生产库当做测试库，对某张表做了大量DML操作，然后进行闪回的过程。

脚本文件：mysqlbinlog_flashback

此脚本为阿里DBA在mysqlbinlog基础上进行改进来的，网上可以下载到。

一、DML操作

模拟生产库的错误DML操作

mysql>showtables;+----------------+|Tables_in_test|+----------------+|kk||t1||t2||t3||t4||t5||t6||t7||t8|+----------------+9rowsinset(0.00sec)mysql>select*fromt8;+------+------+|id|name|+------+------+|1|jack||2|jack||3|jack||4|jack|+------+------+4rowsinset(0.00sec)mysql>insertintot8values(5,'steven');QueryOK,1rowaffected(0.04sec)mysql>insertintot8values(6,'steven');QueryOK,1rowaffected(0.04sec)mysql>insertintot8values(7,'steven');QueryOK,1rowaffected(0.04sec)mysql>insertintot8values(8,'steven');QueryOK,1rowaffected(0.03sec)mysql>updatet8setname='devid'wherename='steven';QueryOK,4rowsaffected(0.33sec)Rowsmatched:4Changed:4Warnings:0mysql>deletefromt8wherename='jack';QueryOK,4rowsaffected(0.04sec)

对生产库t8做了大量的DML之后，发现操作错误

二、查看binlog events

查看当前binlog和pos

mysql>showmasterstatus;+------------------+----------+--------------+------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+------------------+-------------------+|mysql-bin.000005|2195||||+------------------+----------+--------------+------------------+-------------------+1rowinset(0.00sec)

查看binlog events

mysql>showbinlogeventsin'mysql-bin.000005';+------------------+------+----------------+-----------+-------------+--------------------------------------------------+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+------------------+------+----------------+-----------+-------------+--------------------------------------------------+|mysql-bin.000005|4|Format_desc|330631|123|Serverver:5.7.23-log,Binlogver:4||mysql-bin.000005|123|Previous_gtids|330631|154|||mysql-bin.000005|154|Anonymous_Gtid|330631|219|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000005|219|Query|330631|291|BEGIN||mysql-bin.000005|291|Rows_query|330631|348|#insertintot8values(5,'steven')||mysql-bin.000005|348|Table_map|330631|396|table_id:116(test.t8)||mysql-bin.000005|396|Write_rows|330631|443|table_id:116flags:STMT_END_F||mysql-bin.000005|443|Xid|330631|474|COMMIT/*xid=29*/||mysql-bin.000005|474|Anonymous_Gtid|330631|539|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000005|539|Query|330631|611|BEGIN||mysql-bin.000005|611|Rows_query|330631|668|#insertintot8values(6,'steven')||mysql-bin.000005|668|Table_map|330631|716|table_id:116(test.t8)||mysql-bin.000005|716|Write_rows|330631|763|table_id:116flags:STMT_END_F||mysql-bin.000005|763|Xid|330631|794|COMMIT/*xid=30*/||mysql-bin.000005|794|Anonymous_Gtid|330631|859|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000005|859|Query|330631|931|BEGIN||mysql-bin.000005|931|Rows_query|330631|988|#insertintot8values(7,'steven')||mysql-bin.000005|988|Table_map|330631|1036|table_id:116(test.t8)||mysql-bin.000005|1036|Write_rows|330631|1083|table_id:116flags:STMT_END_F||mysql-bin.000005|1083|Xid|330631|1114|COMMIT/*xid=31*/||mysql-bin.000005|1114|Anonymous_Gtid|330631|1179|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000005|1179|Query|330631|1251|BEGIN||mysql-bin.000005|1251|Rows_query|330631|1308|#insertintot8values(8,'steven')||mysql-bin.000005|1308|Table_map|330631|1356|table_id:116(test.t8)||mysql-bin.000005|1356|Write_rows|330631|1403|table_id:116flags:STMT_END_F||mysql-bin.000005|1403|Xid|330631|1434|COMMIT/*xid=32*/||mysql-bin.000005|1434|Anonymous_Gtid|330631|1499|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000005|1499|Query|330631|1571|BEGIN||mysql-bin.000005|1571|Rows_query|330631|1641|#updatet8setname='devid'wherename='steven'||mysql-bin.000005|1641|Table_map|330631|1689|table_id:116(test.t8)||mysql-bin.000005|1689|Update_rows|330631|1817|table_id:116flags:STMT_END_F||mysql-bin.000005|1817|Xid|330631|1848|COMMIT/*xid=33*/||mysql-bin.000005|1848|Anonymous_Gtid|330631|1913|SET@@SESSION.GTID_NEXT='ANONYMOUS'||mysql-bin.000005|1913|Query|330631|1985|BEGIN||mysql-bin.000005|1985|Rows_query|330631|2041|#deletefromt8wherename='jack'||mysql-bin.000005|2041|Table_map|330631|2089|table_id:116(test.t8)||mysql-bin.000005|2089|Delete_rows|330631|2164|table_id:116flags:STMT_END_F||mysql-bin.000005|2164|Xid|330631|2195|COMMIT/*xid=34*/|+------------------+------+----------------+-----------+-------------+--------------------------------------------------+

可以看到，所做的操作为291-2195之间的SQL，binlog文件为mysql-bin.000005

三、查看并反向解析binlog

查看binlog

[root@node1data]#mysqlbinlog_flashback--no-defaults--base64-output=decode-rows-v-v--start-position=291--stop-position=2195mysql-bin.000005/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019SET@@session.max_insert_delayed_threads=0*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at291#18112717:38:25serverid330631end_log_pos348CRC320x1a594faaRows_query#insertintot8values(5,'steven')#at348#18112717:38:25serverid330631end_log_pos396CRC320xa2c0344bTable_map:`test`.`t8`mappedtonumber116#at396#18112717:38:25serverid330631end_log_pos443CRC320xd5578b59Write_rows:tableid116flags:STMT_END_F###INSERTINTO`test`.`t8`###SET###@1=5/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#at443#18112717:38:25serverid330631end_log_pos474CRC320x8bda2ab2Xid=29COMMIT/*!*/;#at474#18112717:38:29serverid330631end_log_pos539CRC320xb81e9c26GTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at539#18112717:38:29serverid330631end_log_pos611CRC320xa70c1ff5Querythread_id=2exec_time=0error_code=0SETTIMESTAMP=1543311509/*!*/;SET@@session.pseudo_thread_id=2/*!*/;SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;SET@@session.sql_mode=1436549152/*!*/;SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;/*!\Cutf8*//*!*/;SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET@@session.lc_time_names=0/*!*/;SET@@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;#at611#18112717:38:29serverid330631end_log_pos668CRC320xdfb1f79bRows_query#insertintot8values(6,'steven')#at668#18112717:38:29serverid330631end_log_pos716CRC320x41d9c819Table_map:`test`.`t8`mappedtonumber116#at716#18112717:38:29serverid330631end_log_pos763CRC320x3642add0Write_rows:tableid116flags:STMT_END_F###INSERTINTO`test`.`t8`###SET###@1=6/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#at763#18112717:38:29serverid330631end_log_pos794CRC320xc682eb4cXid=30COMMIT/*!*/;#at794#18112717:38:33serverid330631end_log_pos859CRC320x2c4b77a1GTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at859#18112717:38:33serverid330631end_log_pos931CRC320x9b23208eIgnorablethread_id=2exec_time=0error_code=0SETTIMESTAMP=1543311513/*!*/;BEGIN/*!*/;#at931#18112717:38:33serverid330631end_log_pos988CRC320x55fc6947Rows_query#insertintot8values(7,'steven')#at988#18112717:38:33serverid330631end_log_pos1036CRC320xfb23efb6Table_map:`test`.`t8`mappedtonumber116#at1036#18112717:38:33serverid330631end_log_pos1083CRC320xddddd8e4Write_rows:tableid116flags:STMT_END_F###INSERTINTO`test`.`t8`###SET###@1=7/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#at1083#18112717:38:33serverid330631end_log_pos1114CRC320x9521a7b4Xid=31COMMIT/*!*/;#at1114#18112717:38:37serverid330631end_log_pos1179CRC320xd43aee35GTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at1179#18112717:38:37serverid330631end_log_pos1251CRC320x3d0df308Ignorablethread_id=2exec_time=0error_code=0SETTIMESTAMP=1543311517/*!*/;BEGIN/*!*/;#at1251#18112717:38:37serverid330631end_log_pos1308CRC320x35d260fdRows_query#insertintot8values(8,'steven')#at1308#18112717:38:37serverid330631end_log_pos1356CRC320x60fbd5f4Table_map:`test`.`t8`mappedtonumber116#at1356#18112717:38:37serverid330631end_log_pos1403CRC320xf2dddee2Write_rows:tableid116flags:STMT_END_F###INSERTINTO`test`.`t8`###SET###@1=8/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#at1403#18112717:38:37serverid330631end_log_pos1434CRC320xb7d14cb6Xid=32COMMIT/*!*/;#at1434#18112717:40:07serverid330631end_log_pos1499CRC320x5300534cGTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at1499#18112717:40:07serverid330631end_log_pos1571CRC320xb4c825b9Ignorablethread_id=2exec_time=0error_code=0SETTIMESTAMP=1543311607/*!*/;BEGIN/*!*/;#at1571#18112717:40:07serverid330631end_log_pos1641CRC320xea587e3dRows_query#updatet8setname='devid'wherename='steven'#at1641#18112717:40:07serverid330631end_log_pos1689CRC320xffe4ea33Table_map:`test`.`t8`mappedtonumber116#at1689#18112717:40:07serverid330631end_log_pos1817CRC320x07fc2cecUpdate_rows:tableid116flags:STMT_END_F###UPDATE`test`.`t8`###WHERE###@1=5/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=5/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/###UPDATE`test`.`t8`###WHERE###@1=6/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=6/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/###UPDATE`test`.`t8`###WHERE###@1=7/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=7/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/###UPDATE`test`.`t8`###WHERE###@1=8/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=8/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/#at1817#18112717:40:07serverid330631end_log_pos1848CRC320xb58eb743Xid=33COMMIT/*!*/;#at1848#18112717:40:18serverid330631end_log_pos1913CRC320x6a90437eGTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#at1913#18112717:40:18serverid330631end_log_pos1985CRC320xb1f69058Ignorablethread_id=2exec_time=0error_code=0SETTIMESTAMP=1543311618/*!*/;BEGIN/*!*/;#at1985#18112717:40:18serverid330631end_log_pos2041CRC320x0c772517Rows_query#deletefromt8wherename='jack'#at2041#18112717:40:18serverid330631end_log_pos2089CRC320xb258a5d1Table_map:`test`.`t8`mappedtonumber116#at2089#18112717:40:18serverid330631end_log_pos2164CRC320x85f3157dDelete_rows:tableid116flags:STMT_END_F###DELETEFROM`test`.`t8`###WHERE###@1=1/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/###DELETEFROM`test`.`t8`###WHERE###@1=2/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/###DELETEFROM`test`.`t8`###WHERE###@1=3/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/###DELETEFROM`test`.`t8`###WHERE###@1=4/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/#at2164#18112717:40:18serverid330631end_log_pos2195CRC320x2b861950Xid=34COMMIT/*!*/;DELIMITER;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*/;DELIMITER;#EndoflogfileROLLBACK/*addedbymysqlbinlog*/;/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

反向解析binlog

[root@node1data]#mysqlbinlog_flashback--no-defaults--base64-output=decode-rows-B-v-v--start-position=291--stop-position=2195mysql-bin.000005/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019SET@@session.max_insert_delayed_threads=0*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#18112717:38:25serverid330631end_log_pos348CRC320x1a594faaRows_query#insertintot8values(5,'steven')#18112717:38:25serverid330631end_log_pos396CRC320xa2c0344bTable_map:`test`.`t8`mappedtonumber116#18112717:38:25serverid330631end_log_pos474CRC320x8bda2ab2Xid=29COMMIT/*!*/;#18112717:38:29serverid330631end_log_pos539CRC320xb81e9c26GTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#18112717:38:29serverid330631end_log_pos668CRC320xdfb1f79bRows_query#insertintot8values(6,'steven')#18112717:38:29serverid330631end_log_pos716CRC320x41d9c819Table_map:`test`.`t8`mappedtonumber116#18112717:38:29serverid330631end_log_pos794CRC320xc682eb4cXid=30COMMIT/*!*/;#18112717:38:33serverid330631end_log_pos859CRC320x2c4b77a1GTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#18112717:38:33serverid330631end_log_pos988CRC320x55fc6947Rows_query#insertintot8values(7,'steven')#18112717:38:33serverid330631end_log_pos1036CRC320xfb23efb6Table_map:`test`.`t8`mappedtonumber116#18112717:38:33serverid330631end_log_pos1114CRC320x9521a7b4Xid=31COMMIT/*!*/;#18112717:38:37serverid330631end_log_pos1179CRC320xd43aee35GTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#18112717:38:37serverid330631end_log_pos1308CRC320x35d260fdRows_query#insertintot8values(8,'steven')#18112717:38:37serverid330631end_log_pos1356CRC320x60fbd5f4Table_map:`test`.`t8`mappedtonumber116#18112717:38:37serverid330631end_log_pos1434CRC320xb7d14cb6Xid=32COMMIT/*!*/;#18112717:40:07serverid330631end_log_pos1499CRC320x5300534cGTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#18112717:40:07serverid330631end_log_pos1641CRC320xea587e3dRows_query#updatet8setname='devid'wherename='steven'#18112717:40:07serverid330631end_log_pos1689CRC320xffe4ea33Table_map:`test`.`t8`mappedtonumber116#18112717:40:07serverid330631end_log_pos1848CRC320xb58eb743Xid=33COMMIT/*!*/;#18112717:40:18serverid330631end_log_pos1913CRC320x6a90437eGTID[commit=no]SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;#18112717:40:18serverid330631end_log_pos2041CRC320x0c772517Rows_query#deletefromt8wherename='jack'#18112717:40:18serverid330631end_log_pos2089CRC320xb258a5d1Table_map:`test`.`t8`mappedtonumber116#18112717:40:18serverid330631end_log_pos2195CRC320x2b861950Xid=34COMMIT/*!*/;#18112717:40:18serverid330631end_log_pos2164CRC320x85f3157dDelete_rows:tableid116flags:STMT_END_F###INSERTINTO`test`.`t8`###SET###@1=1/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/###INSERTINTO`test`.`t8`###SET###@1=2/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/###INSERTINTO`test`.`t8`###SET###@1=3/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/###INSERTINTO`test`.`t8`###SET###@1=4/*INTmeta=0nullable=1is_null=0*/###@2='jack'/*VARSTRING(60)meta=60nullable=1is_null=0*/#18112717:40:07serverid330631end_log_pos1817CRC320x07fc2cecUpdate_rows:tableid116flags:STMT_END_F###UPDATE`test`.`t8`###WHERE###@1=5/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=5/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/###UPDATE`test`.`t8`###WHERE###@1=6/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=6/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/###UPDATE`test`.`t8`###WHERE###@1=7/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=7/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/###UPDATE`test`.`t8`###WHERE###@1=8/*INTmeta=0nullable=1is_null=0*/###@2='devid'/*VARSTRING(60)meta=60nullable=1is_null=0*/###SET###@1=8/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#18112717:38:37serverid330631end_log_pos1403CRC320xf2dddee2Write_rows:tableid116flags:STMT_END_F###DELETEFROM`test`.`t8`###WHERE###@1=8/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#18112717:38:33serverid330631end_log_pos1083CRC320xddddd8e4Write_rows:tableid116flags:STMT_END_F###DELETEFROM`test`.`t8`###WHERE###@1=7/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#18112717:38:29serverid330631end_log_pos763CRC320x3642add0Write_rows:tableid116flags:STMT_END_F###DELETEFROM`test`.`t8`###WHERE###@1=6/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/#18112717:38:25serverid330631end_log_pos443CRC320xd5578b59Write_rows:tableid116flags:STMT_END_F###DELETEFROM`test`.`t8`###WHERE###@1=5/*INTmeta=0nullable=1is_null=0*/###@2='steven'/*VARSTRING(60)meta=60nullable=1is_null=0*/DELIMITER;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#EndoflogfileROLLBACK/*addedbymysqlbinlog*/;/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

四、数据恢复

将反向解析出来的binlog添加到文本文件

mysqlbinlog_flashback--no-defaults--base64-output=decode-rows-B-v-v--start-position=291--stop-position=2195mysql-bin.000005>flash_back_t8.sql

修改flash_back_t8.sql，将最后的ROLLBACK改为COMMIT。

五、恢复数据

mysql -uroot -p test <flash_back_t8.sql