Recently, I’ve been looking into issues with the interactions between MySQL asynchronous replication and Galera replication. In this blog post, I’d like to share what I’ve learned.
MySQL asynchronous replication and Galera replication interactions are complicated due to the number of factors involved (Galera replication vs. asynchronous replication, replication filters, and row-based vs. statement-based replication). So as a start, I’ll look at an issue that came up with setting up an asynchronous replication channel between two Percona XtraDB Cluster (PXC) clusters.
Here’s a view of the desired topology:
We want to set up an asynchronous replication channel between two PXC clusters. We also set
log-slave-updates on the async slave (PXC node 2a in the topology diagram).
This is an interesting configuration and results in unexpected behavior as the replication depends on the node where the operation was performed. Let’s use
CREATE TABLE as an example.
CREATE TABLEon Node 1a. The table replicates to Node 1b, but not to the nodes in cluster 2.
CREATE TABLEon Node 1b. The table replicates to all nodes (both cluster 1 and cluster 2).
Some background information
Understanding the problem requires some knowledge of MySQL threads. However, as a simplification, I’ll group the threads into three groups:
- Main MySQL Client Thread: This thread handles the requests for the client connection (here the client is an external entity).
- Async Replication Threads: There are multiple threads in use here, some handle I/O, and some apply the updates, but we will view them as a single entity for simplicity.
- Galera Threads: There are also multiple threads here, similar to the Async Replication Threads. (The name Galera here refers to the underlying replication technology used by PXC.)
For more information on MySQL threads, see
Why is the data not replicating?
In the first case (
CREATE TABLE executed on Node1a)
- The table is replicated from Node1a -> Node 1b via Galera replication.
- The table is not replicated because the async replication threads are not picking up the changes.
In the second case (
CREATE TABLE executed on Node 1b)
- The table is replicated from Node1b -> Node 1a via Galera replication.
- The table is replicated from Node1b -> Node 2a via async replication. This differs from the first case because the statement is executed on the Main MySQL client thread. The async replication threads pick up the changes and send them to Node 2a.
- The table is replicated from Node 2a -> Node 2b via Galera replication because
log-slave-updateshas been enabled on Node2a.
That last part is the important bit. We can view the Galera replication threads as another set of asynchronous replication threads. So if data is coming in via async replication, they have to be made visible to Galera by
log-slave-updates. This is true in the other direction also:
log-slave-updates must be enabled for Galera to supply data to async replication.
This is very similar to chained replication
In this scenario, the answer is to set
log-slave-updates on Node 1b (the async master) and on Node 2a (the async slave).
log-slave-updates on node 1b to allow the async threads to pickup the changes from the Galera threads.
log-slave-updates on node 2a to allow the Galera threads to pickup the changes from the async threads. Starting with PXC 5.7.17, calling
START SLAVE on a PXC node will return an error unless
log-slave-updates is enabled.
You must enable
log-slave-updates on the node for data to be transferred between Galera and asynchronous replication.
If you plan to use MySQL asynchronous replication with Percona XtraDB Cluster (either as async master or slave), we recommend that you enable
log-slave-updates on all nodes within the cluster. This to (1) to ensure that any async replication connections to/from the cluster work correctly and (2) to ensure that all the nodes within a cluster share the same configuration and behavior.
Recommended configuration diagram for the clusters:
The post Percona XtraDB Cluster, MySQL Asynchronous Replication and log-slave-updates appeared first on Percona Database Performance Blog.