Hi,
I’m currently facing an strange issue with xtrabackup 8 and percona-server 8.
I have two instance of mysql (mysqlA and mysqlB) using gtid base replication. I try to simulate a master switch and restoration of the old master using a backup taken from a slave.
mysqlA is the source, mysqlB is the replica using gtid based replication. sysbench is used as client.
the percona-server configuration is quite simple
gtid_mode=ON
enforce_gtid_consistency
log_bin=ON
log_replica_updates
expire_logs_days=10
skip_slave_start
binlog_format=ROW
for both mysql
here is the steps i follow:
- launch a sysbench client against mysqlA
- During sysbench is running take a backup of mysqlB
- once sysbench has finished, stop the replication between mysqlA and mysqlB by issuing a stop replica command mysqlB
- prepare the backup taken from mysqlB
- shut down mysqlA,remove its data and move back prepared backup to mysqlA
- start mysqlA
- start some writes on the new master (mysqlB) using sysbench.
- Connect to mysqlA and issue a
change master to master_host='<ip>', master_port=<port>, master_auto_position=1;to setup the replication. - start the replication between mysqlB and mysqlA by issuing a ``start replica user=‘’ password=‘’;```
Before starting the replication threads on mysqlA i expect the transaction applied after the backup has been take applied on mysqlA before applying new writes done on mysqlB
All writes done after the backup taken from mysqlB are lost on mysqlA.
Here is the output of ```show global variables like ‘%gtid%’ on both mysql instances at different times:
on mysqlA after first sysbench run and backup already taken:
+----------------------------------+----------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 0c4b68da-487e-11ec-b492-f02f74d6b108:1-35776 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+----------------------------------------------+
8 rows in set (0.00 sec)
On mysqlB at the same time:
+----------------------------------+----------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 0c4b68da-487e-11ec-b492-f02f74d6b108:1-35776 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+----------------------------------------------+
8 rows in set (0.01 sec)
Output of xtrabackup_slave_info file:
SET GLOBAL gtid_purged='0c4b68da-487e-11ec-b492-f02f74d6b108:1-23967';
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
show global variables like '%gtid%' output after restoration of backup on mysqlA:
+----------------------------------+----------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 0c4b68da-487e-11ec-b492-f02f74d6b108:1-23967 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 0c4b68da-487e-11ec-b492-f02f74d6b108:1-23967 |
| session_track_gtids | OFF |
+----------------------------------+----------------------------------------------+
8 rows in set (0.01 sec)
output of show global variables like '%gtid%' after some writes on the new master (mysqlB)
on mysqlA:
+----------------------------------+------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 0117a59d-4871-11ec-a7ba-f02f74d6b108:1-948,
0c4b68da-487e-11ec-b492-f02f74d6b108:1-23967 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | 0117a59d-4871-11ec-a7ba-f02f74d6b108:949#10 |
| gtid_purged | 0c4b68da-487e-11ec-b492-f02f74d6b108:1-23967 |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
on mysqlB
+----------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 0117a59d-4871-11ec-a7ba-f02f74d6b108:1-2796,
0c4b68da-487e-11ec-b492-f02f74d6b108:1-35776 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
we clearly see some transaction a missing in the gtid_executed. After check those transaction are present in the binlog of mysqlB and should have been applied instead of ignored.
Am i missing something or is it a bug that sounds really critical ?