溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本實驗模擬把生產庫當做測試庫，對某張表做了大量DML操作，然后進行閃回的過程。
腳本文件：mysqlbinlog_flashback
此腳本為阿里DBA在mysqlbinlog基礎上進行改進來的，網上可以下載到。
一、DML操作
模擬生產庫的錯誤DML操作
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | kk | | t1 | | t2 | | t3 | | t4 | | t5 | | t6 | | t7 | | t8 | +----------------+ 9 rows in set (0.00 sec) mysql> select * from t8; +------+------+ | id | name | +------+------+ | 1 | jack | | 2 | jack | | 3 | jack | | 4 | jack | +------+------+ 4 rows in set (0.00 sec) mysql> insert into t8 values(5,'steven'); Query OK, 1 row affected (0.04 sec) mysql> insert into t8 values(6,'steven'); Query OK, 1 row affected (0.04 sec) mysql> insert into t8 values(7,'steven'); Query OK, 1 row affected (0.04 sec) mysql> insert into t8 values(8,'steven'); Query OK, 1 row affected (0.03 sec) mysql> update t8 set name='devid' where name='steven'; Query OK, 4 rows affected (0.33 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> delete from t8 where name='jack'; Query OK, 4 rows affected (0.04 sec)
對生產庫t8做了大量的DML之后，發現操作錯誤
二、查看binlog events
查看當前binlog和pos
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 2195 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
查看binlog events
mysql> show binlog events in 'mysql-bin.000005'; +------------------+------+----------------+-----------+-------------+--------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+--------------------------------------------------+ | mysql-bin.000005 | 4 | Format_desc | 330631 | 123 | Server ver: 5.7.23-log, Binlog ver: 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 | # insert into t8 values(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: 116 flags: 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 | # insert into t8 values(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: 116 flags: 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 | # insert into t8 values(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: 116 flags: 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 | # insert into t8 values(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: 116 flags: 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 | # update t8 set name='devid' where name='steven' | | mysql-bin.000005 | 1641 | Table_map | 330631 | 1689 | table_id: 116 (test.t8) | | mysql-bin.000005 | 1689 | Update_rows | 330631 | 1817 | table_id: 116 flags: 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 | # delete from t8 where name='jack' | | mysql-bin.000005 | 2041 | Table_map | 330631 | 2089 | table_id: 116 (test.t8) | | mysql-bin.000005 | 2089 | Delete_rows | 330631 | 2164 | table_id: 116 flags: STMT_END_F | | mysql-bin.000005 | 2164 | Xid | 330631 | 2195 | COMMIT /* xid=34 */ | +------------------+------+----------------+-----------+-------------+--------------------------------------------------+
可以看到，所做的操作為291-2195之間的SQL，binlog文件為mysql-bin.000005
三、查看并反向解析binlog
查看binlog
[root@node1 data]# mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -v -v --start-position=291 --stop-position=2195 mysql-bin.000005 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 291 #181127 17:38:25 server id 330631 end_log_pos 348 CRC32 0x1a594faa Rows_query # insert into t8 values(5,'steven') # at 348 #181127 17:38:25 server id 330631 end_log_pos 396 CRC32 0xa2c0344b Table_map: `test`.`t8` mapped to number 116 # at 396 #181127 17:38:25 server id 330631 end_log_pos 443 CRC32 0xd5578b59 Write_rows: table id 116 flags: STMT_END_F ### INSERT INTO `test`.`t8` ### SET ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ # at 443 #181127 17:38:25 server id 330631 end_log_pos 474 CRC32 0x8bda2ab2 Xid = 29 COMMIT/*!*/; # at 474 #181127 17:38:29 server id 330631 end_log_pos 539 CRC32 0xb81e9c26 GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 539 #181127 17:38:29 server id 330631 end_log_pos 611 CRC32 0xa70c1ff5 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=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/*!*/; /*!\C utf8 *//*!*/; 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 /*!*/; # at 611 #181127 17:38:29 server id 330631 end_log_pos 668 CRC32 0xdfb1f79b Rows_query # insert into t8 values(6,'steven') # at 668 #181127 17:38:29 server id 330631 end_log_pos 716 CRC32 0x41d9c819 Table_map: `test`.`t8` mapped to number 116 # at 716 #181127 17:38:29 server id 330631 end_log_pos 763 CRC32 0x3642add0 Write_rows: table id 116 flags: STMT_END_F ### INSERT INTO `test`.`t8` ### SET ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ # at 763 #181127 17:38:29 server id 330631 end_log_pos 794 CRC32 0xc682eb4c Xid = 30 COMMIT/*!*/; # at 794 #181127 17:38:33 server id 330631 end_log_pos 859 CRC32 0x2c4b77a1 GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 859 #181127 17:38:33 server id 330631 end_log_pos 931 CRC32 0x9b23208e Ignorable thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1543311513/*!*/; BEGIN /*!*/; # at 931 #181127 17:38:33 server id 330631 end_log_pos 988 CRC32 0x55fc6947 Rows_query # insert into t8 values(7,'steven') # at 988 #181127 17:38:33 server id 330631 end_log_pos 1036 CRC32 0xfb23efb6 Table_map: `test`.`t8` mapped to number 116 # at 1036 #181127 17:38:33 server id 330631 end_log_pos 1083 CRC32 0xddddd8e4 Write_rows: table id 116 flags: STMT_END_F ### INSERT INTO `test`.`t8` ### SET ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ # at 1083 #181127 17:38:33 server id 330631 end_log_pos 1114 CRC32 0x9521a7b4 Xid = 31 COMMIT/*!*/; # at 1114 #181127 17:38:37 server id 330631 end_log_pos 1179 CRC32 0xd43aee35 GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1179 #181127 17:38:37 server id 330631 end_log_pos 1251 CRC32 0x3d0df308 Ignorable thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1543311517/*!*/; BEGIN /*!*/; # at 1251 #181127 17:38:37 server id 330631 end_log_pos 1308 CRC32 0x35d260fd Rows_query # insert into t8 values(8,'steven') # at 1308 #181127 17:38:37 server id 330631 end_log_pos 1356 CRC32 0x60fbd5f4 Table_map: `test`.`t8` mapped to number 116 # at 1356 #181127 17:38:37 server id 330631 end_log_pos 1403 CRC32 0xf2dddee2 Write_rows: table id 116 flags: STMT_END_F ### INSERT INTO `test`.`t8` ### SET ### @1=8 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ # at 1403 #181127 17:38:37 server id 330631 end_log_pos 1434 CRC32 0xb7d14cb6 Xid = 32 COMMIT/*!*/; # at 1434 #181127 17:40:07 server id 330631 end_log_pos 1499 CRC32 0x5300534c GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1499 #181127 17:40:07 server id 330631 end_log_pos 1571 CRC32 0xb4c825b9 Ignorable thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1543311607/*!*/; BEGIN /*!*/; # at 1571 #181127 17:40:07 server id 330631 end_log_pos 1641 CRC32 0xea587e3d Rows_query # update t8 set name='devid' where name='steven' # at 1641 #181127 17:40:07 server id 330631 end_log_pos 1689 CRC32 0xffe4ea33 Table_map: `test`.`t8` mapped to number 116 # at 1689 #181127 17:40:07 server id 330631 end_log_pos 1817 CRC32 0x07fc2cec Update_rows: table id 116 flags: STMT_END_F ### UPDATE `test`.`t8` ### WHERE ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### UPDATE `test`.`t8` ### WHERE ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### UPDATE `test`.`t8` ### WHERE ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### UPDATE `test`.`t8` ### WHERE ### @1=8 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=8 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ # at 1817 #181127 17:40:07 server id 330631 end_log_pos 1848 CRC32 0xb58eb743 Xid = 33 COMMIT/*!*/; # at 1848 #181127 17:40:18 server id 330631 end_log_pos 1913 CRC32 0x6a90437e GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1913 #181127 17:40:18 server id 330631 end_log_pos 1985 CRC32 0xb1f69058 Ignorable thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1543311618/*!*/; BEGIN /*!*/; # at 1985 #181127 17:40:18 server id 330631 end_log_pos 2041 CRC32 0x0c772517 Rows_query # delete from t8 where name='jack' # at 2041 #181127 17:40:18 server id 330631 end_log_pos 2089 CRC32 0xb258a5d1 Table_map: `test`.`t8` mapped to number 116 # at 2089 #181127 17:40:18 server id 330631 end_log_pos 2164 CRC32 0x85f3157d Delete_rows: table id 116 flags: STMT_END_F ### DELETE FROM `test`.`t8` ### WHERE ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### DELETE FROM `test`.`t8` ### WHERE ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### DELETE FROM `test`.`t8` ### WHERE ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### DELETE FROM `test`.`t8` ### WHERE ### @1=4 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ # at 2164 #181127 17:40:18 server id 330631 end_log_pos 2195 CRC32 0x2b861950 Xid = 34 COMMIT/*!*/; DELIMITER ; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
反向解析binlog
[root@node1 data]# mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -B -v -v --start-position=291 --stop-position=2195 mysql-bin.000005 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; #181127 17:38:25 server id 330631 end_log_pos 348 CRC32 0x1a594faa Rows_query # insert into t8 values(5,'steven') #181127 17:38:25 server id 330631 end_log_pos 396 CRC32 0xa2c0344b Table_map: `test`.`t8` mapped to number 116 #181127 17:38:25 server id 330631 end_log_pos 474 CRC32 0x8bda2ab2 Xid = 29 COMMIT/*!*/; #181127 17:38:29 server id 330631 end_log_pos 539 CRC32 0xb81e9c26 GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; #181127 17:38:29 server id 330631 end_log_pos 668 CRC32 0xdfb1f79b Rows_query # insert into t8 values(6,'steven') #181127 17:38:29 server id 330631 end_log_pos 716 CRC32 0x41d9c819 Table_map: `test`.`t8` mapped to number 116 #181127 17:38:29 server id 330631 end_log_pos 794 CRC32 0xc682eb4c Xid = 30 COMMIT/*!*/; #181127 17:38:33 server id 330631 end_log_pos 859 CRC32 0x2c4b77a1 GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; #181127 17:38:33 server id 330631 end_log_pos 988 CRC32 0x55fc6947 Rows_query # insert into t8 values(7,'steven') #181127 17:38:33 server id 330631 end_log_pos 1036 CRC32 0xfb23efb6 Table_map: `test`.`t8` mapped to number 116 #181127 17:38:33 server id 330631 end_log_pos 1114 CRC32 0x9521a7b4 Xid = 31 COMMIT/*!*/; #181127 17:38:37 server id 330631 end_log_pos 1179 CRC32 0xd43aee35 GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; #181127 17:38:37 server id 330631 end_log_pos 1308 CRC32 0x35d260fd Rows_query # insert into t8 values(8,'steven') #181127 17:38:37 server id 330631 end_log_pos 1356 CRC32 0x60fbd5f4 Table_map: `test`.`t8` mapped to number 116 #181127 17:38:37 server id 330631 end_log_pos 1434 CRC32 0xb7d14cb6 Xid = 32 COMMIT/*!*/; #181127 17:40:07 server id 330631 end_log_pos 1499 CRC32 0x5300534c GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; #181127 17:40:07 server id 330631 end_log_pos 1641 CRC32 0xea587e3d Rows_query # update t8 set name='devid' where name='steven' #181127 17:40:07 server id 330631 end_log_pos 1689 CRC32 0xffe4ea33 Table_map: `test`.`t8` mapped to number 116 #181127 17:40:07 server id 330631 end_log_pos 1848 CRC32 0xb58eb743 Xid = 33 COMMIT/*!*/; #181127 17:40:18 server id 330631 end_log_pos 1913 CRC32 0x6a90437e GTID [commit=no] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; #181127 17:40:18 server id 330631 end_log_pos 2041 CRC32 0x0c772517 Rows_query # delete from t8 where name='jack' #181127 17:40:18 server id 330631 end_log_pos 2089 CRC32 0xb258a5d1 Table_map: `test`.`t8` mapped to number 116 #181127 17:40:18 server id 330631 end_log_pos 2195 CRC32 0x2b861950 Xid = 34 COMMIT/*!*/; #181127 17:40:18 server id 330631 end_log_pos 2164 CRC32 0x85f3157d Delete_rows: table id 116 flags: STMT_END_F ### INSERT INTO `test`.`t8` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### INSERT INTO `test`.`t8` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### INSERT INTO `test`.`t8` ### SET ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### INSERT INTO `test`.`t8` ### SET ### @1=4 /* INT meta=0 nullable=1 is_null=0 */ ### @2='jack' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ #181127 17:40:07 server id 330631 end_log_pos 1817 CRC32 0x07fc2cec Update_rows: table id 116 flags: STMT_END_F ### UPDATE `test`.`t8` ### WHERE ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### UPDATE `test`.`t8` ### WHERE ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### UPDATE `test`.`t8` ### WHERE ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### UPDATE `test`.`t8` ### WHERE ### @1=8 /* INT meta=0 nullable=1 is_null=0 */ ### @2='devid' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=8 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ #181127 17:38:37 server id 330631 end_log_pos 1403 CRC32 0xf2dddee2 Write_rows: table id 116 flags: STMT_END_F ### DELETE FROM `test`.`t8` ### WHERE ### @1=8 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ #181127 17:38:33 server id 330631 end_log_pos 1083 CRC32 0xddddd8e4 Write_rows: table id 116 flags: STMT_END_F ### DELETE FROM `test`.`t8` ### WHERE ### @1=7 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ #181127 17:38:29 server id 330631 end_log_pos 763 CRC32 0x3642add0 Write_rows: table id 116 flags: STMT_END_F ### DELETE FROM `test`.`t8` ### WHERE ### @1=6 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ #181127 17:38:25 server id 330631 end_log_pos 443 CRC32 0xd5578b59 Write_rows: table id 116 flags: STMT_END_F ### DELETE FROM `test`.`t8` ### WHERE ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ ### @2='steven' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ DELIMITER ; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
四、數據恢復
將反向解析出來的binlog添加到文本文件
mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -B -v -v --start-position=291 --stop-position=2195 mysql-bin.000005 > flash_back_t8.sql
修改flash_back_t8.sql，將最后的ROLLBACK改為COMMIT。
五、恢復數據
mysql -uroot -p test < flash_back_t8.sql
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
