Mysqlbinlog 误删数据恢复测试-gtid模式

说明，今天测试利用binlog进行数据恢复遇到一点问题，现记录下来加深印象

1、记录当前binlog所处position--5092

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000022
         Position: 5092
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: fa27fab0-dcea-11ec-8e8d-000c293b7d4b:1-71742
1 row in set (0.00 sec)

2、创建测试表并插入测试数据

mysql> create table test(name varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test values('gg')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values('mm');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+
| name |
+------+
| gg   |
| mm   |
+------+
2 rows in set (0.00 sec)

3、记录当前position，可以通过show master status查看，也可以通过event进行

mysql> show binlog events in 'mysql-bin.000022' from 5092;
+------------------+------+------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                                                                  |
+------------------+------+------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000022 | 5092 | Gtid       |         1 |        5157 | SET @@SESSION.GTID_NEXT= 'fa27fab0-dcea-11ec-8e8d-000c293b7d4b:71743' |
| mysql-bin.000022 | 5157 | Query      |         1 |        5270 | use `sbtest`; create table test(name varchar(10))                     |
| mysql-bin.000022 | 5270 | Gtid       |         1 |        5335 | SET @@SESSION.GTID_NEXT= 'fa27fab0-dcea-11ec-8e8d-000c293b7d4b:71744' |
| mysql-bin.000022 | 5335 | Query      |         1 |        5409 | BEGIN                                                                 |
| mysql-bin.000022 | 5409 | Table_map  |         1 |        5460 | table_id: 116 (sbtest.test)                                           |
| mysql-bin.000022 | 5460 | Write_rows |         1 |        5499 | table_id: 116 flags: STMT_END_F                                       |
| mysql-bin.000022 | 5499 | Xid        |         1 |        5530 | COMMIT /* xid=145306 */                                               |
| mysql-bin.000022 | 5530 | Gtid       |         1 |        5595 | SET @@SESSION.GTID_NEXT= 'fa27fab0-dcea-11ec-8e8d-000c293b7d4b:71745' |
| mysql-bin.000022 | 5595 | Query      |         1 |        5669 | BEGIN                                                                 |
| mysql-bin.000022 | 5669 | Table_map  |         1 |        5720 | table_id: 116 (sbtest.test)                                           |
| mysql-bin.000022 | 5720 | Write_rows |         1 |        5759 | table_id: 116 flags: STMT_END_F                                       |
| mysql-bin.000022 | 5759 | Xid        |         1 |        5790 | COMMIT /* xid=145310 */                                               |
+------------------+------+------------+-----------+-------------+-----------------------------------------------------------------------+

在例子中可以看到，当前position 结束位置在5790

4、Drop table后并开始恢复

mysql> drop table test;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from test;
ERROR 1146 (42S02): Table 'sbtest.test' doesn't exist

5、开始恢复---在这里就需要注意gtid的用法了。因为会存在gtid重用的问题，mysql默认会跳过，且不会产生报错，所以若是直接恢复，因生成的sql文件中gtid值一致，mysql会跳过执行，所以此时会一脸懵逼

[root@localhost mysql]# mysqlbinlog mysql-bin.000022 --start-position=5092 --stop-position=5790  >inesrt.sql
[root@localhost mysql]# 
[root@localhost mysql]# mysql --login-path=dba <inesrt.sql 
[root@localhost mysql]# mysql --login-path=dba -e 'select * from sbtest.test;'
ERROR 1146 (42S02) at line 1: Table 'sbtest.test' doesn't exist
[root@localhost mysql]# more inesrt.sql 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220719  3:08:44 server id 1  end_log_pos 123 CRC32 0x857bd93e  Start: binlog v 4, server v 5.7.28-log created 220719  3:08:44
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
rILWYg8BAAAAdwAAAHsAAAABAAQANS43LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AT7Ze4U=
'/*!*/;
# at 5092

6、此时需要跳过gtid进行解析binlog文件，重新生成sql文件，然后再执行sql文件，即恢复成功

[root@localhost mysql]# mysqlbinlog mysql-bin.000022 --start-position=5092 --stop-position=5790 --skip-gtids >insert_new.sql
[root@localhost mysql]# 
[root@localhost mysql]# 
[root@localhost mysql]# mysql --login-path=dba <insert_new.sql 
[root@localhost mysql]# mysql --login-path=dba -e 'select * from sbtest.test;'
+------+
| name |
+------+
| gg   |
| mm   |
+------+