开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了,那如果想恢复时怎么办?
1、创建新一个测试库
db01 [(none)]>create database gtid charset utf8;
Query OK, 1 row affected (0.05 sec)
db01 [(none)]>
db01 [(none)]>select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON |
+-------------+
1 row in set (0.00 sec)
db01 [(none)]>use gtid
Database changed
db01 [gtid]>
2、创建一个新表T1,并分3次插入4行数据
db01 [gtid]>create table t1(id int);
Query OK, 0 rows affected (0.02 sec)
db01 [gtid]>show binlog events in 'mysql-bin.000050';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000050 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| mysql-bin.000050 | 123 | Previous_gtids | 1 | 194 | dda1ed88-9655-11ea-af56-000c29c4165e:1-77 |
| mysql-bin.000050 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:78' |
| mysql-bin.000050 | 259 | Query | 1 | 366 | create database gtid charset utf8 |
| mysql-bin.000050 | 366 | Gtid | 1 | 431 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:79' |
| mysql-bin.000050 | 431 | Query | 1 | 528 | use `gtid`; create table t1(id int) |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
6 rows in set (0.00 sec)
db01 [gtid]>insert into t1 values (10);
Query OK, 1 row affected (0.00 sec)
db01 [gtid]>commit;
Query OK, 0 rows affected (0.00 sec)
db01 [gtid]>show binlog events in 'mysql-bin.000050';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000050 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| mysql-bin.000050 | 123 | Previous_gtids | 1 | 194 | dda1ed88-9655-11ea-af56-000c29c4165e:1-77 |
| mysql-bin.000050 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:78' |
| mysql-bin.000050 | 259 | Query | 1 | 366 | create database gtid charset utf8 |
| mysql-bin.000050 | 366 | Gtid | 1 | 431 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:79' |
| mysql-bin.000050 | 431 | Query | 1 | 528 | use `gtid`; create table t1(id int) |
| mysql-bin.000050 | 528 | Gtid | 1 | 593 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:80' |
| mysql-bin.000050 | 593 | Query | 1 | 665 | BEGIN |
| mysql-bin.000050 | 665 | Table_map | 1 | 710 | table_id: 232 (gtid.t1) |
| mysql-bin.000050 | 710 | Write_rows | 1 | 750 | table_id: 232 flags: STMT_END_F |
| mysql-bin.000050 | 750 | Xid | 1 | 781 | COMMIT /* xid=15 */ |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
11 rows in set (0.00 sec)
db01 [gtid]>insert into t1 values (11);
Query OK, 1 row affected (0.00 sec)
db01 [gtid]>commit;
Query OK, 0 rows affected (0.00 sec)
db01 [gtid]>show binlog events in 'mysql-bin.000050';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000050 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| mysql-bin.000050 | 123 | Previous_gtids | 1 | 194 | dda1ed88-9655-11ea-af56-000c29c4165e:1-77 |
| mysql-bin.000050 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:78' |
| mysql-bin.000050 | 259 | Query | 1 | 366 | create database gtid charset utf8 |
| mysql-bin.000050 | 366 | Gtid | 1 | 431 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:79' |
| mysql-bin.000050 | 431 | Query | 1 | 528 | use `gtid`; create table t1(id int) |
| mysql-bin.000050 | 528 | Gtid | 1 | 593 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:80' |
| mysql-bin.000050 | 593 | Query | 1 | 665 | BEGIN |
| mysql-bin.000050 | 665 | Table_map | 1 | 710 | table_id: 232 (gtid.t1) |
| mysql-bin.000050 | 710 | Write_rows | 1 | 750 | table_id: 232 flags: STMT_END_F |
| mysql-bin.000050 | 750 | Xid | 1 | 781 | COMMIT /* xid=15 */ |
| mysql-bin.000050 | 781 | Gtid | 1 | 846 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:81' |
| mysql-bin.000050 | 846 | Query | 1 | 918 | BEGIN |
| mysql-bin.000050 | 918 | Table_map | 1 | 963 | table_id: 232 (gtid.t1) |
| mysql-bin.000050 | 963 | Write_rows | 1 | 1003 | table_id: 232 flags: STMT_END_F |
| mysql-bin.000050 | 1003 | Xid | 1 | 1034 | COMMIT /* xid=18 */ |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
16 rows in set (0.00 sec)
db01 [gtid]>insert into t1 values (14);
Query OK, 1 row affected (0.00 sec)
db01 [gtid]>insert into t1 values (15);
Query OK, 1 row affected (0.00 sec)
db01 [gtid]>commit;
Query OK, 0 rows affected (0.00 sec)
db01 [gtid]>show binlog events in 'mysql-bin.000050';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000050 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| mysql-bin.000050 | 123 | Previous_gtids | 1 | 194 | dda1ed88-9655-11ea-af56-000c29c4165e:1-77 |
| mysql-bin.000050 | 194 | Gtid | 1 | 259 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:78' |
| mysql-bin.000050 | 259 | Query | 1 | 366 | create database gtid charset utf8 |
| mysql-bin.000050 | 366 | Gtid | 1 | 431 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:79' |
| mysql-bin.000050 | 431 | Query | 1 | 528 | use `gtid`; create table t1(id int) |
| mysql-bin.000050 | 528 | Gtid | 1 | 593 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:80' |
| mysql-bin.000050 | 593 | Query | 1 | 665 | BEGIN |
| mysql-bin.000050 | 665 | Table_map | 1 | 710 | table_id: 232 (gtid.t1) |
| mysql-bin.000050 | 710 | Write_rows | 1 | 750 | table_id: 232 flags: STMT_END_F |
| mysql-bin.000050 | 750 | Xid | 1 | 781 | COMMIT /* xid=15 */ |
| mysql-bin.000050 | 781 | Gtid | 1 | 846 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:81' |
| mysql-bin.000050 | 846 | Query | 1 | 918 | BEGIN |
| mysql-bin.000050 | 918 | Table_map | 1 | 963 | table_id: 232 (gtid.t1) |
| mysql-bin.000050 | 963 | Write_rows | 1 | 1003 | table_id: 232 flags: STMT_END_F |
| mysql-bin.000050 | 1003 | Xid | 1 | 1034 | COMMIT /* xid=18 */ |
| mysql-bin.000050 | 1034 | Gtid | 1 | 1099 | SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:82' |
| mysql-bin.000050 | 1099 | Query | 1 | 1171 | BEGIN |
| mysql-bin.000050 | 1171 | Table_map | 1 | 1216 | table_id: 232 (gtid.t1) |
| mysql-bin.000050 | 1216 | Write_rows | 1 | 1256 | table_id: 232 flags: STMT_END_F |
| mysql-bin.000050 | 1256 | Table_map | 1 | 1301 | table_id: 232 (gtid.t1) |
| mysql-bin.000050 | 1301 | Write_rows | 1 | 1341 | table_id: 232 flags: STMT_END_F |
| mysql-bin.000050 | 1341 | Xid | 1 | 1372 | COMMIT /* xid=21 */ |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
23 rows in set (0.00 sec)
db01 [gtid]>select * from t1;
+------+
| id |
+------+
| 10 |
| 11 |
| 14 |
| 15 |
+------+
4 rows in set (0.00 sec)
通过binlog events可以看到,插入4行数据对应的GTID为dda1ed88-9655-11ea-af56-000c29c4165e:80-82。
现在通过mysqlbinlog 恢复事务,恢复时排除dda1ed88-9655-11ea-af56-000c29c4165e:81
[root@QXY data]# mysqlbinlog --include-gtids='dda1ed88-9655-11ea-af56-000c29c4165e:80-82' --exclude-gtids='dda1ed88-9655-11ea-af56-000c29c4165e:81' mysql-bin.000050 > 2.sql
3、删除t1表的全部数据
db01 [gtid]>delete from t1;
Query OK, 4 rows affected (0.00 sec)
4、使用第2步的binlog日志恢复,恢复之后发现t1表还是没有数据
db01 [gtid]>
db01 [gtid]>set sql_log_bin=off
-> ;
Query OK, 0 rows affected (0.00 sec)
db01 [gtid]>
db01 [gtid]>
db01 [gtid]>source 2.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
db01 [gtid]>select * from t1;
Empty set (0.00 sec)
db01 [gtid]>
造成t1表无数据的原因是,GTID的幂等性,由于GTID对应的事务已经存在了,所以source 2.sql的时候不会执行,那么要怎么才能让mysql执行呢,这个时候就需要使用mysqlbinlog时加上--skip-gtids。
5、使用--skip-gtids参数再次备份binlog
[root@QXY data]# mysqlbinlog --skip-gtids --include-gtids='dda1ed88-9655-11ea-af56-000c29c4165e:80-82' --exclude-gtids='dda1ed88-9655-11ea-af56-000c29c4165e:81' mysql-bin.000050 > 3.sql
6、对比加和不加--skip-gtids参数导出的文件差异
[root@QXY data]# diff 2.sql 3.sql
12a13
> /*!50616 SET @@SESSION.GTID_NEXT='AUTOMATIC'*//*!*/;
14,15d14
< #210618 11:06:18 server id 1 end_log_pos 194 CRC32 0x73fb6cbd Previous-GTIDs
< # dda1ed88-9655-11ea-af56-000c29c4165e:1-77
21,22d19
< #210618 11:07:53 server id 1 end_log_pos 593 CRC32 0x2ad6ce6f GTID last_committed=2 sequence_number=3
< SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:80'/*!*/;
54,55d50
< #210618 11:08:36 server id 1 end_log_pos 1099 CRC32 0xa2098eb0 GTID last_committed=4 sequence_number=5
< SET @@SESSION.GTID_NEXT= 'dda1ed88-9655-11ea-af56-000c29c4165e:82'/*!*/;
82d76
< SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
发现2.sql不加--skip-gtids时,每个事务开始前,都会有SET @@SESSION.GTID_NEXT
7、使用--skip-gtids之后参数的文件导入数据库
db01 [gtid]>set sql_log_bin=off
-> ;
Query OK, 0 rows affected (0.00 sec)
db01 [gtid]>
db01 [gtid]>source 3.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
db01 [gtid]>select *from t1;
+------+
| id |
+------+
| 10 |
| 14 |
| 15 |
+------+
3 rows in set (0.00 sec)
db01 [gtid]>
可以发现,数据可以正常导入,并且成功过滤掉了第二次插入id=11的数据