本实验模拟把生产库当做测试库,对某张表做了大量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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------+
可以看到,所做的操作为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
;
;
;
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
### @2='steven'
# 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;
;
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
### @2='steven'
# 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
### @2='steven'
# 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
### @2='steven'
# 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
### @2='steven'
### SET
### @1=5
### @2='devid'
### UPDATE `test`.`t8`
### WHERE
### @1=6
### @2='steven'
### SET
### @1=6
### @2='devid'
### UPDATE `test`.`t8`
### WHERE
### @1=7
### @2='steven'
### SET
### @1=7
### @2='devid'
### UPDATE `test`.`t8`
### WHERE
### @1=8
### @2='steven'
### SET
### @1=8
### @2='devid'
# 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
### @2='jack'
### DELETE FROM `test`.`t8`
### WHERE
### @1=2
### @2='jack'
### DELETE FROM `test`.`t8`
### WHERE
### @1=3
### @2='jack'
### DELETE FROM `test`.`t8`
### WHERE
### @1=4
### @2='jack'
# at 2164
#181127 17:40:18 server id 330631 end_log_pos 2195 CRC32 0x2b861950 Xid = 34
COMMIT;
DELIMITER ;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' ;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;
反向解析binlog
[root@node1 data]# mysqlbinlog_flashback --no-defaults --base64-output=decode-rows -B -v -v --start-position=291 --stop-position=2195 mysql-bin.000005
;
;
;
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
### @2='jack'
### INSERT INTO `test`.`t8`
### SET
### @1=2
### @2='jack'
### INSERT INTO `test`.`t8`
### SET
### @1=3
### @2='jack'
### INSERT INTO `test`.`t8`
### SET
### @1=4
### @2='jack'
#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
### @2='devid'
### SET
### @1=5
### @2='steven'
### UPDATE `test`.`t8`
### WHERE
### @1=6
### @2='devid'
### SET
### @1=6
### @2='steven'
### UPDATE `test`.`t8`
### WHERE
### @1=7
### @2='devid'
### SET
### @1=7
### @2='steven'
### UPDATE `test`.`t8`
### WHERE
### @1=8
### @2='devid'
### SET
### @1=8
### @2='steven'
#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
### @2='steven'
#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
### @2='steven'
#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
### @2='steven'
#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
### @2='steven'
DELIMITER ;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' ;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;
四、数据恢复
将反向解析出来的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