# Behavior of replication filters XtraDB

**URL:** <https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728>\
**Category:** Percona XtraDB Cluster\
**Created:** [October 6, 2023, 1:12pm UTC](https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728 "2023-10-06T13:12:13Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juul\_S](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/juul_s/32/12608_2.png) [@Juul\_S](https://forums.percona.com/u/Juul_S)\
**Post date:** [October 6, 2023, 1:12pm UTC](https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728/1 "2023-10-06T13:12:13Z")

</div>

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](https://mariadb.com/kb/en/configuring-mariadb-galera-cluster/#replication-filters)), but using XtraDB that is usually not my first go to.

---

<div class="post-metadata">

**Author:** ![Kamil\_Holubicki](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kamil_holubicki/32/5693_2.png) [@Kamil\_Holubicki](https://forums.percona.com/u/Kamil_Holubicki)\
**Post date:** [October 10, 2023, 7:35am UTC](https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728/2 "2023-10-10T07:35:40Z")

</div>

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:

```auto
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:

```auto
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](https://dev.mysql.com/doc/refman/8.0/en/replication-rules-db-options.html), 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.

---

<div class="post-metadata">

**Author:** ![Juul\_S](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/juul_s/32/12608_2.png) [@Juul\_S](https://forums.percona.com/u/Juul_S)\
**Post date:** [October 10, 2023, 12:37pm UTC](https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728/3 "2023-10-10T12:37:13Z")

</div>

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:

```auto
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:

```auto
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:

```auto
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:

```auto
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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Juul\_S](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/juul_s/32/12608_2.png) [@Juul\_S](https://forums.percona.com/u/Juul_S)\
**Post date:** [November 24, 2023, 9:07am UTC](https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728/4 "2023-11-24T09:07:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kamil\_Holubicki](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kamil_holubicki/32/5693_2.png) [@Kamil\_Holubicki](https://forums.percona.com/u/Kamil_Holubicki)\
**Post date:** [November 24, 2023, 12:57pm UTC](https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728/5 "2023-11-24T12:57:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Juul\_S](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/juul_s/32/12608_2.png) [@Juul\_S](https://forums.percona.com/u/Juul_S)\
**Post date:** [November 24, 2023, 3:44pm UTC](https://forums.percona.com/t/behavior-of-replication-filters-xtradb/25728/6 "2023-11-24T15:44:13Z")

</div>

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 🙂
