MySQL multiple GTID Executed GTID set

Question

I believe I have an issue of sorts with MySQL Replication on the slaves. Instead of there being one clean GTID under 'Executed GTID Set' there are multiple GTIDs.

Retrieved_Gtid_Set: 54206435-24c7-11e5-bd59-1458d055bee8:354806-354826
Executed_Gtid_Set: 54206435-24c7-11e5-bd59-1458d055bee8:1-354826, cd975336-24c7-11e5-bd5c-1458d04f9808:1-24

I've tried a slave reset all but this doesn't seem to clear it.

Has anyone seen this before? What might be the best way of resolving this?

Thanks in advance

Answer 1

There's nothing to solve here. These are just GTIDs of different servers.

The first part of a GTID is the UUID (unique universal ID, or something like that, IIRC).

54206435-24c7-11e5-bd59-1458d055bee8:354806-354826
<........host......................>:<...GTIDs...>

I'd guess, that these are GTIDs from the master and the localhost in your executed GTID set.

When you do show slave status\G on the slave there's also a line like this:

Master_UUID: ee624d91-76da-11e4-8f3a-ecf4bbce03bc

which tells you the UUID of the master. On the master you can do

root@localhost:(none) > show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | c5ca7103-2eb7-11e5-be29-0050569b085a |
+-----------+------+------+-----------+--------------------------------------+

In summary, everything's normal. These are most likely GTIDs from your master and some statements you executed on the slave.

Answer 2

Gtids are created as:

<server_uuid>:<sequence_number> to uniquely identify the transactions.
Here, the retrieved gtid is the set of GTID transactions that came by replication (txns that makes upto relay log). So, if you are replicating from multiple masters, retreived_gtid_set can too be multiple.

Similarly, the executed gtids are the set of gtids that are executed on the server. This comprises of:

1. Transactions from replication
   
2. Transactions executed on the local server itself.
   

Since, multiple servers have different unique uuids, it is normal to have multiple gtid sets in order to identify what set of transactions came from which server.

Server uuids can be found in auto.cnf file in mysql data directory

Answer 3

If you want to reset the GTID you can run following commands

STOP SLAVE SQL_THREAD;
RESET MASTER;
SHOW SLAVE STATUS\G;

To set the multi source mysql master slave replication: https://www.svastikkka.com/2024/03/mysql-multi-source-master-slave.html