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.
