Replication Between Two Percona XtraDB Clusters, GTIDs and Schema Changes

Replication Between Two Percona XtraDB ClustersI got this question on the “How to Avoid Pitfalls in Schema Upgrade with Percona XtraDB Cluster (PXC)” webinar and wanted to answer it in a separate post.

Will RSU have an effect on GTID consistency if replication PXC cluster to another cluster?

Answer for this: yes and no.

Galera assigns its own GTID for the operations, replicated to all nodes of the cluster. Such operations include DML ( INSERT/UPDATE/DELETE ) on InnoDB tables and DDL commands, executed with default TOI method. You can find more details on how GTIDs work in the Percona XtraDB Cluster in this blog post.

However, DDL commands, executed with RSU method, are applied locally and have their own, individual, GTID.

Let’s set up a replication between two PXC clusters and see how it works.

First, let’s use the default wsrep_osu_method  TOI and create three tables on each node of the source cluster:

You see that all GTIDs have the same UUID: 24f602ff-cb98-11ea-beb2-ba09d9a11266  and the number of the transaction increase no matter which node is the source of the change.

All changes successfully replicate. As a result, the replica has received and applied GTIDs as can be seen in the SHOW SLAVE STATUS  output:

With RSU method we are executing DDL on each node while it is out of sync with the rest of the cluster. After the operation finishes nothing replicated to other nodes by itself, rather relies on the DBA to perform changes manually. Therefore GTID for such an operation uses local UUID:

As you see that this same operation created GTIDs with three different UUIDs on the three nodes: 25394777-cb98-11ea-a23a-98af65266957 , 322ff3eb-cb98-11ea-8a94-98af65266957  and 3ab8cf00-cb98-11ea-b433-98af65266957 .

Replica cluster received GTID from the node which set up as a replication source:

So by default RSU does not generate any issue with GTID.

However, if later you need to perform a failover and setup any other node as a replication source node, the replica will try to apply local GTIDs and fail with an error:

The only solution here is to inject empty transaction instead of one, created by the RSU operation: