Galera nodes and GTID drifting

Updated at by

The drift

Sometimes GTIDs drift among the galera nodes. Even with wsrep_gtid_mode=1 set on the server. I've usually experienced this after upgrading a single node and running mysql_upgrade --upgrade-system-tables --skip-write-binlog on it. Sometimes it was stable and sometimes the gap grew over time. (looks like it was fixed in Feb 2024 MariaDB Jira MDEV-21950).

Example of the inconsistent binlogs:

galera-1:
end_log_pos 475521299 CRC32 0x014143dc   Xid = 5389209496
end_log_pos 475521341 CRC32 0xfebb8ff6   GTID 0-20-7480997106 trans

galera-2:
end_log_pos 380794823 CRC32 0x3a8498d4   Xid = 5389209496
end_log_pos 380794865 CRC32 0x696ae1a5   GTID 0-20-7465272277 trans

Ideally I would like to run binlog replicas with master_use_gtid = slave_pos and flip replication source by issuing CHANGE MASTER TO MASTER_HOST=<the other galera node>;. But the inconsistency of binlogs makes this impossible. Two ways to get around this:

Option 1 : force gtids back into sync in the cluster.

Having ascending GTID numbers on all nodes is nice and the only way if replicas have gtid_strict_mode=1.

GTIDS on the galera nodes:
1->2->3->10     <--- drifter!
1->2->3->4
1->2->3->4

> SET SESSION wsrep_gtid_seq_no=200;
> CREATE TABLE meh (id int unsigned primary key); # GTID 200
> DROP TABLE meh; # GTID 201

GTIDs on the galera nodes:
1->2->3->10->200->201
1->2->3->4 ->200->201
1->2->3->4 ->200->201

But beware that there might be a bug still lingering when INSERT statement is recorded twice in binlog. MariaDB Jira | Duplicate gtid event in binlog so creating a table might be safer.

Option 2 : resolve binlog position by Xid

After stopping slave the last event in binlog should be annoted with Xid = <some number> this implies the transaction which started with a GTID number has been applied to all galera nodes. In high load env this might take a few tries of stopping and starting replication.

mysqlbinlog i-am-a-relay-log-relay-bin.000016|grep -Eo "(Xid =|GTID ).*"

...
GTID 0-20-6003397 trans
Xid = 1461682
GTID 0-20-6003398 trans
Xid = 1461683            <---- Xid as last event

And with a little helper script I dig up the binlog position from another galera node.

[root@db-3 ~]# resolve_binlog_pos_for_xid 1461683
For Xid 1461683
===
# Number of rows: 1
# at 6788045
#240730 10:45:16 server id 20  end_log_pos 6788076 CRC32 0x0c91b8f3     Xid = 1461683
COMMIT/*!*/;
DELIMITER ;
===
CHANGE MASTER TO MASTER_HOST='10.1.1.12', MASTER_LOG_FILE='i-am-binlog.000160', MASTER_LOG_POS=6788076;

And then I can apply the CHANGE MASTER TO and continue replicating from a new host.

Is it safe to use GTIDs from a galera cluster 2024-09-03

Currently I can't cause my testing clusters to drift but I would ensure binlog tails are identical on both masters before any switcharoos. Have a great day!

Leave a comment