Behavior of replication filters XtraDB

Hi,

I am looking for documentation on how replication filters (like binlog-do-db) are supposed to behave in XtraDB.

I have noticed that after binary logging together with the ‘binlog-do-db = $dbname’ I am getting unexpected behavior. I enabled the setting on 1 node in a 3 node cluster. What I expected was that the node with the binary logging enabled and a filter for that $dbname would only log events for $dbname into the binary log. What we unfortunately got is that the node with the binlogging enabled did not replicate any update from the other nodes aside the ones that matched $dbname. To me that is very unexpected as binary logging itself is not required for replication and I also could not find a mention of this in the documention here. After running into the issue I did find that MariaDB has a little bit of info on this topic mentioned on their website (Configuring MariaDB Galera Cluster - MariaDB Knowledge Base), but using XtraDB that is usually not my first go to.

Hi @Juul_S ,

This is my test:

node_1: binlog-do-db=bindb
node_2: no binlog-do-db option

Case 1:
On node_1 do:

create database bindb;
create table bindb.t1(a int primary key);

create database test;
create table test.t1(a int primary key);

insert into bindb.t1 values (0);
insert into test.t1 values (0);

Result:
Everything is replicated to node_2 as expected.
Only ‘bindb’-related inserts are logged into binlog as expected (for DDLs see below)

Case 2:
On node_2 do:

insert into bindb.t1 values (100);
insert into test.t1 values (100);

Result:
Everything is replicated to node_1 as expected.
On node_1 only ‘bindb’-related inserts are logged into binlog as expected (see the explanation below)

Why do we expect node_1 to binlog only ‘bindb’-related events in case 2?
binlog-do-db is the parameter related to MySql replication. If you configure it on node_1, I assume you would like this node to be (for example) the source for MySql async replication.
As PXC is master-master replication, it doesn’t matter which node receives writes. You can treat PXC as one black-box that receives writes and does the magic inside. Having that said, whatever PXC node you write, you expect that only ‘bindb’ will be replicated by MySql async replication.

Questionable is if logging of DDL related to ‘test’ database on node_1 is OK. According to THIS, I think they should be skipped if no default database is selected, but I see MySql and inherently Percona Server logs them as well.

Moreover, setting binlog-do-db requires node restart, I think more convenient and flexible would be setting replicate-do-db on the async replica node.

The behavior you are describing is different from what we are experiencing.
So we have 3 nodes.
node_1: binlog-do-db = bindb
node_2: skip-log-bin
node_3: skip-log-bin

All the nodes are running Percona XtraDB 8.0.33-25.1 on Ubuntu 22.04, xtradb being installed from the percona repository (1:8.0.33-25-1.jammy)

Node_2 and node_3 don’t have a binlog-do-db setting and ofcourse node_1 has a log-bin = /path/to/logfile configured.

Case 1:
On node_1 do:

create database bindb;
create table bindb.t1(a int primary key);

create database test;
create table test.t1(a int primary key);

insert into bindb.t1 values (1);
insert into test.t1 values(1);

select * from bindb.t1;
+---+
| a |
+---+
| 1 |
+---+

select * from test.t1;
+---+
| a |
+---+
| 1 |
+---+

On node_2 do:

mysql>  select * from bindb.t1;
+---+
| a |
+---+
| 1 |
+---+

mysql> select * from test.t1;
Empty set (0,00 sec)
mysql> show create table test.t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

On node_3 do:

select * from bindb.t1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

mysql> select * from test.t1;
Empty set (0,00 sec)
mysql> show create table test.t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

Result:

  • DML and DDL regarding ‘bindb’ is replicated to node_2 and node_3.
  • DDL regarding ‘test’ is replicated to node_2 and node_3.
  • What I do not expect is DML regarding ‘test’ not being replicated
  • As expected is DML regarding ‘bindb’ logged into binlog as expected, DDL is logged for both.

Case 2:
On node_2 do:

mysql> insert into bindb.t1 values (2);
mysql> insert into test.t1 values (2);
mysql> select * from bindb.t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0,00 sec)

mysql> select * from test.t1;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0,00 sec)

On node_1:

mysql> select * from bindb.t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0,00 sec)

mysql> select * from test.t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+

On node_3:

mysql> select * from bindb.t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0,00 sec)

mysql> select * from test.t1;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0,00 sec)

Result:

  • value 2 for both databases is replicated to node_1 and node_3
  • On node_1 only the insert for bindb is logged.

So regarding the results the surprise to me is that in case 1 not both inserts have been replicated as I thought the replication mechanism was completely independant of binary logging and its options. So MariaDB mentions this:

Like with MariaDB replication, replication filters can be used to filter write sets from being replicated by Galera Cluster’s certification-based replication. However, they should be used with caution because they may not work as you’d expect.

So this lead to me question, how is this supposed to work in XtraDB. I am (or was) under the assumption that the binlog-do-db setting would not have consequences for the replication to node_2 and node_3. I think I am seeing the behavior as described in the MariaDB documentation on how replication filters can be used to filter write sets from being replicated by Galera’s mechanism.

As to why we enabled binlogging, our intention was to see if we could see a pattern regarding updates in a specific database. We figured we enable binary logging together with the filter to minimize a possible performance impact by logging just enough for what we needed. We did not expect the binlog-do-db to have seemingly impact what is being replicated.

Is anyone able tell if the situation that we are experiencing is undocumented, but correct, behavior or is it incorrect behavior.

Hi @Juul_S ,
Yes, this is expected behavior. Galera replication in fact bases on binlog events replication (Galera write-set is just a collection of binlog events related to the transaction + Galera specific meta data), so in simple, what is binloggled, will be replicated by PXC. And the opposite: what is not, will not be replicated.
You may of course disable binlog on PXC node, but internally it uses binlog cache infrastructure to collect all events needed to construct the write-set, it just does not write to binlog file.

1 Like

We weren’t sure if I was hitting a bug or some undocumented behavior (at least I couldn’t find information on this topic aside from the MariaDB url) so thank you for the clarification regarding this :slight_smile: