If you were to ask me the question, “Would it be possible to set up a replicate filter within the Percona XtraDB Cluster?” my immediate response would be to question you right back. Why would you even want the cluster nodes to have different data when you’re using a synchronous replication cluster? That’s because, instinctively, the situation presents itself as a paradox. The main purpose of the Percona XtraDB Cluster (PXC) is to ensure that all the data is automatically synchronized on all the nodes in the cluster. Filtering out certain tables on any of the nodes seemingly defeats the whole purpose of the PXC, so why should it be possible to be attempted?
However, there must be a reason for your asking. If you were to insist, I would explain that there could be scenarios wherein someone would want to filter out certain tables in one of the nodes within the PXC. I’ll delve into two of these possible scenarios below.
Scenario #1: We have a PXC running, while we also want an asynchronous replication from an outside primary to replicate certain tables to the PXC. The topology may look like below:
1 2 3 4 | outside-primary __PXC node1[RW] __PXC node2[RW] __PXC node3[RW] |
Assuming there are two tables: test.t1 test.f1 in the outside-primary, we only want the table test.t1 to be replicated to the PXC. In this case, we would like to set up replication from the outside-primary to node1 of the PXC cluster with a replicate filter like REPLICATE_WILD_DO_TABLE = (‘test.t1’).
We are expecting the table test.t1 of the outside-primary can be replicated to node1 of the PXC cluster, and through node1, it can be synced to the whole cluster. Also, all the writes on any of the nodes within the PXC cluster can be synced to the whole cluster as the PXC cluster normally does.
Scenario #2: We have two PXCs running, and we want the two clusters synced with each other, except for a table test.local_session. The topology may look like below:
1 2 3 | PXC1 node1[RW] <--> PXC2 node1[RW] __PXC1 node2[RW] __PXC2 node2[RW] __PXC1 node3[RW] __PXC2 node3[RW] |
In this case, we would like to set up a replicate filter like REPLICATE_WILD_IGNORE_TABLE = (‘test.local_session’) on PXC1 node1 and PXC2 node1 simultaneously. We expect all the writes on each cluster to be synced within the same cluster; at the same time, all the writes from a PXC cluster can also be replicated to another cluster except for the table test.local_session.
Now that we’ve considered some of the real-world scenarios where it would be necessary to be able to filter out certain tables on certain nodes for outside replication(asynchronous) while expecting not to impact the replication(synchronous) within the PXC, it’s time for the bigger question: does PXC support that, and if so, how? Let us find it out via the below tests.
Test #1: Set up the topology as Scenario #1 using Percona Server 5.7.43 and PXC-5.7.43. On the node1 of PXC, it is replicating from the outside primary, with Replicate_Wild_Do_Table: test.t1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | node1 [localhost:28044] {msandbox} ((none)) > show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: msandbox Master_Port: 21044 ... Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: test.t1 Replicate_Wild_Ignore_Table: ... Seconds_Behind_Master: 0 ... Replicate_Ignore_Server_Ids: Master_Server_Id: 10001 ... Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: |
On the outside-primary, we create 2 tables, test.t1, and test.f1, and insert into value 1 to test.t1 and value 2 to test.f1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | master [localhost:21044] {msandbox} (test) > create table t1(c1 int primary key); Query OK, 0 rows affected (0.01 sec) master [localhost:21044] {msandbox} (test) > create table f1(c1 int primary key); Query OK, 0 rows affected (0.01 sec) master [localhost:21044] {msandbox} (test) > insert into t1 values(1); Query OK, 1 row affected (0.03 sec) master [localhost:21044] {msandbox} (test) > insert into f1 values(2); Query OK, 1 row affected (0.00 sec) master [localhost:21044] {msandbox} (test) > select * from t1 ; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) master [localhost:21044] {msandbox} (test) > select * from f1; +----+ | c1 | +----+ | 2 | +----+ 1 row in set (0.00 sec) |
We can see the table test.t1 was replicated to the PXC node1, node2, and node 3, but test.f1 was not, as expected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | node1 [localhost:28044] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.01 sec) node1 [localhost:28044] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.01 sec) node2 [localhost:28045] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) node2 [localhost:28045] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) node3 [localhost:28046] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) node3 [localhost:28046] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
Now, let us create a table f1 and insert value 1 into it on node2 of the PXC, and see what is going to happen.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | node2 [localhost:28045] {msandbox} (test) > create table f1(c1 int primary key); Query OK, 0 rows affected (0.02 sec) node2 [localhost:28045] {msandbox} (test) > insert into f1 values(1); Query OK, 1 row affected (0.00 sec) node2 [localhost:28045] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | f1 | | t1 | +----------------+ 2 rows in set (0.01 sec) node2 [localhost:28045] {msandbox} (test) > select * from f1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
The table test.f1 was replicated to node3, as expected
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | node3 [localhost:28046] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | f1 | | t1 | +----------------+ 2 rows in set (0.00 sec) node3 [localhost:28046] {msandbox} (test) > select * from f1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.01 sec) |
How about node1? which has a replicate filter there: Replicate_Wild_Do_Table: test.t1.
Oops, the DDL was replicated(create table f1), but the DML was NOT replicated(insert into test.t1).
1 2 3 4 5 6 7 8 9 10 | node1 [localhost:28044] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | f1 | | t1 | +----------------+ 2 rows in set (0.00 sec) node1 [localhost:28044] {msandbox} (test) > select * from f1; Empty set (0.00 sec) |
So we know PXC-5.7.43 does support replicating from outside the cluster with a replicate filter that works well. However, the replicate filter will also apply to the replication inside the PXC, which is NOT expected.
How about PXC 8.0?
Test #2: Set up the topology as Scenario #1 using Percona Server 8.0.34 and PXC-8.0.34. On the node1 of PXC, it is replicating from the outside primary, with Replicate_Wild_Do_Table: test.t1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | node1 [localhost:29435] {msandbox} ((none)) > show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 127.0.0.1 Master_User: msandbox Master_Port: 22435 ... Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: test.t1 Replicate_Wild_Ignore_Table: ... Seconds_Behind_Master: 0 ... Replicate_Ignore_Server_Ids: Master_Server_Id: 10001 ... Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: ... |
On the outside primary, we create 2 tables test.t1 and test.f1, and insert into value 1 to test.t1 and value 2 to test.f1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | master [localhost:22435] {msandbox} (test) > create table t1(c1 int primary key); Query OK, 0 rows affected (0.02 sec) master [localhost:22435] {msandbox} (test) > create table f1(c1 int primary key); Query OK, 0 rows affected (0.03 sec) master [localhost:22435] {msandbox} (test) > insert into t1 values(1); Query OK, 1 row affected (0.01 sec) master [localhost:22435] {msandbox} (test) > insert into f1 values(2); Query OK, 1 row affected (0.01 sec) master [localhost:22435] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) master [localhost:22435] {msandbox} (test) > select * from f1; +----+ | c1 | +----+ | 2 | +----+ 1 row in set (0.00 sec) |
We can see the table test.t1 was replicated to the PXC node1, node2, and node3, but test.f1 was not, as expected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | node1 [localhost:29435] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) node1 [localhost:29435] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) node2 [localhost:29436] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) node2 [localhost:29436] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) node3 [localhost:29437] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) node3 [localhost:29437] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
Now, let us create a table f1 and insert value 1 into it on node2 of the PXC, and see what is going to happen.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | node2 [localhost:29436] {msandbox} (test) > create table f1(c1 int primary key); Query OK, 0 rows affected (0.04 sec) node2 [localhost:29436] {msandbox} (test) > insert into f1 values(1); Query OK, 1 row affected (0.01 sec) node2 [localhost:29436] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | f1 | | t1 | +----------------+ 2 rows in set (0.00 sec) node2 [localhost:29436] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) node2 [localhost:29436] {msandbox} (test) > select * from f1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
The table test.f1 was replicated to node3, as expected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | node3 [localhost:29437] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | f1 | | t1 | +----------------+ 2 rows in set (0.00 sec) node3 [localhost:29437] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) node3 [localhost:29437] {msandbox} (test) > select * from f1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
It works on node1 as well! Even with replicate filter there: Replicate_Wild_Do_Table: test.t1. the DDL(create table f1), and the DML(insert into test.f1) are both replicated within the PXC. It means the replicate filter does NOT affect the replication inside the PXC, which is what we expected:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | node1 [localhost:29435] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | f1 | | t1 | +----------------+ 2 rows in set (0.00 sec) node1 [localhost:29435] {msandbox} (test) > select * from t1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) node1 [localhost:29435] {msandbox} (test) > select * from f1; +----+ | c1 | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
Test #3: Set up the topology as Scenario #2, we created two clusters, each running with three nodes, while setting up cluster1-node1 and cluster2-node1 are replicating to each other with both setting up CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (‘test.t1’); using Percona Server 8.0.34.
On the cluster1-node1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | node1 [localhost:29334] {msandbox} ((none)) > show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 127.0.0.1 Master_User: msandbox <strong>Master_Port: 29435</strong> ... Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: <strong>Replicate_Wild_Ignore_Table: test.t1</strong> ... Seconds_Behind_Master: 0 ... Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: ... |
On cluster2-node1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | node1 [localhost:29435] {msandbox} ((none)) > show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 127.0.0.1 Master_User: msandbox <strong>Master_Port: 29334</strong> ... Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: <strong>Replicate_Wild_Ignore_Table: test.t1</strong> ... Seconds_Behind_Master: 0 ... Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: ... |
On the cluster1-node2, we created table test.t1 and test.t2 and insert into value of 29335
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | node2 [localhost:29335] {msandbox} (test) > create table t1(c1 int primary key); Query OK, 0 rows affected (0.03 sec) node2 [localhost:29335] {msandbox} (test) > create table t2(c1 int primary key); Query OK, 0 rows affected (0.04 sec) node2 [localhost:29335] {msandbox} (test) > insert into t1 values(29335); Query OK, 1 row affected (0.03 sec) node2 [localhost:29335] {msandbox} (test) > insert into t2 values(29335); Query OK, 1 row affected (0.01 sec) node2 [localhost:29335] {msandbox} (test) > select * from t1; +-------+ | c1 | +-------+ | 29335 | +-------+ 1 row in set (0.00 sec) node2 [localhost:29335] {msandbox} (test) > select * from t2; +-------+ | c1 | +-------+ | 29335 | +-------+ |
The two tables are replicated(synchronous) to the cluster1-node1, as they are in the same cluster, even with CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (‘test.t1’).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | node1 [localhost:29334] {msandbox} ((none)) > select * from test.t1; +-------+ | c1 | +-------+ | 29335 | +-------+ 1 row in set (0.00 sec) node1 [localhost:29334] {msandbox} ((none)) > select * from test.t2; +-------+ | c1 | +-------+ | 29335 | +-------+ 1 row in set (0.00 sec) |
And the table test.t2 was replicated(asynchronous) but the table test.t1 was NOT replicated(asynchronous) across the cluster to cluster2-node1 as it has CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (‘test.t1’).
1 2 3 4 5 6 7 8 9 | node1 [localhost:29435] {msandbox} ((none)) > select * from test.t1; <strong>ERROR 1146 (42S02): Table 'test.t1' doesn't exist</strong> node1 [localhost:29435] {msandbox} ((none)) > select * from test.t2; +-------+ | c1 | +-------+ | 29335 | +-------+ 1 row in set (0.00 sec) |
All worked as expected.
Test #4: Well, I will leave it to you to test and verify what will happen if we use Percona Server for MySQL 5.7 to set up the topology as Scenario #2.
Now, we know PXC-8.0.34 does support replication from outside the cluster(asynchronous) with the replicate filter working well. More importantly, in our case, the replicate filter will NOT apply to the replication inside(synchronous) the PXC, which is what we expect.
Why does the replicate filter in PXC-5.7.43 affect the replication inside the PXC, while the replicate filter in PXC-8.0.34 does not?
Searched online, we can find out that prior to MySQL 8.0, MySQL did NOT support setting up a replicate filter “for channel”, meaning the replicate filter will work globally applying to all the replications. However, after MySQL 8.0, MySQL supports setting up a replicate filter for channel: “Use the FOR CHANNEL channel clause to make a replication filter specific to a replication channel, for example on a multi-source replica. Filters applied without a specific FOR CHANNEL clause are considered global filters, meaning that they are applied to all replication channels. “
Within PXC, it seems to set a replicate filter even without explicitly using the “for channel” clause, it by default takes it as “for channel” instead of taking it as a global filter. The Percona XtraDB Cluster ensures that all the data is automatically synchronized on all the nodes within the whole cluster.
Conclusion
- Prior to MySQL 8.0, namely MySQL 5.7 and before, you can set up a replicate filter within the Percona XtraDB Cluster. The replicate filter will apply globally to the outside replication(asynchronous) and within the PXC cluster replication(synchronous).
- Since MySQL 8.0, you can set up a replicate filter within the Percona XtraDB Cluster. The filter will only apply to the outside replication(asynchronous) for a certain channel without impacting the global replication(synchronous) within the PXC cluster.
Takeaway: Do not use a replicate filter on PXC 5.7, as it may cause the PXC cluster to be out of sync; this also applies to MariaDB10.3, which we verified its behavior the same. Be cautious about using a replicate filter after PXC 8.0 unless you are clear about the expected impacts.
Designed to handle mission-critical workloads, Percona XtraDB Cluster preserves, secures, and protects your data and revenue streams by providing the highest level of availability for your business-critical applications, no matter where they are deployed.
