MySQL/MariaDB replication filter is an important feature when we need to replicate only certain databases or tables. Having this configuration option change dynamically is really convenient, but in this article, we’ll note that some replication filters are not dynamic, and you should be aware of that.
The use case here is to replicate one database from primary to a replica under a different name. That is, to replicate the database named “db_primary” from primary to “db_replica” on a replica MariaDB server.
Here we have db_primary and db_replica as two MariaDB servers. Let’s begin.
1 2 3 4 5 6 7 | MariaDB [db_primary]> SELECT @@version, @@binlog_format; +---------------------+-----------------+ | @@version | @@binlog_format | +---------------------+-----------------+ | 10.5.24-MariaDB-log | ROW | +---------------------+-----------------+ 1 row in set (0.000 sec) |
On the primary, I created a database named “db_primary”, created a table, and inserted a single record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | MariaDB [(none)]> CREATE DATABASE db_primary; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> CREATE TABLE db_primary.t (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT); Query OK, 0 rows affected (0.010 sec) MariaDB [(none)]> INSERT INTO db_primary.t VALUES (NULL); Query OK, 1 row affected (0.002 sec) MariaDB [(none)]> SELECT * FROM db_primary.t; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.000 sec) |
On the replica, created a database “db_replica”.
1 2 | MariaDB [(none)]> CREATE DATABASE db_replica; Query OK, 1 row affected (0.000 sec) |
I then used mysqldump to export the database without the create-db command so I could easily import it into a replica database named “db_replica”.
1 | $ mysqldump --no-create-db db_primary | mysql -udba -p -h192.168.56.19 db_replica |
The next step is to set up replication filtering and rewrite. While doing that, I noticed that CHANGE REPLICATION FILTER commands don’t work in MariaDB 10.5 but it works with MySQL.
1 2 | MariaDB [db_replica]> CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((db_primary, db_replica)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REPLICATION FILTER REPLICATE_REWRITE_DB=((db_primary, db_replica))' at line 1 |
As per the MariaDB docs, you have to use SET GLOBAL to set up these variables dynamically.
However, while doing so, I noticed that there is no such variable available to set dynamically.
1 2 | MariaDB [db_replica]> SET GLOBAL replicate_rewrite_db=`db_primary->db_replica`; ERROR 1193 (HY000): Unknown system variable 'replicate_rewrite_db' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | MariaDB [db_replica]> SHOW GLOBAL VARIABLES LIKE 'replicate%'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | replicate_annotate_row_events | ON | | replicate_do_db | | | replicate_do_table | | | replicate_events_marked_for_skip | REPLICATE | | replicate_ignore_db | | | replicate_ignore_table | | | replicate_wild_do_table | | | replicate_wild_ignore_table | | +----------------------------------+-----------+ 8 rows in set (0.002 sec) |
Thus, to configure this database rewrite feature, the replicate_rewrite_db parameter has to be changed in the configuration file, and MariaDB on the replica server has to be restarted. I also added replicate_do_db=”db_primary” in my.cnf to persist it.
1 2 3 | $ egrep -Ri "replicate" /etc/my* /etc/my.cnf.d/server.cnf:replicate_do_db=db_primary /etc/my.cnf.d/server.cnf:replicate_rewrite_db=db_primary->db_replica |
After the MariaDB restart, I set up the replication between the MariaDB primary and replica instance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | MariaDB [db_replica]> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.18 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master.000002 Read_Master_Log_Pos: 1142 Relay_Log_File: mariadb-10-5-21-replica-relay-bin.000008 Relay_Log_Pos: 552 Relay_Master_Log_File: master.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: db_primary |
Then I inserted a record into the MariaDB primary instance.
1 2 3 4 5 6 7 8 9 10 11 | MariaDB [db_primary]> INSERT INTO db_primary.t VALUES (NULL); Query OK, 1 row affected (0.002 sec) MariaDB [db_primary]> SELECT * FROM t; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.001 sec) |
Replicate-rewrite-db problem
However, that did not get replicated to the MariaDB replica instance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | MariaDB [db_replica]> SELECT * FROM t; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.000 sec) MariaDB [db_replica]> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.18 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master.000002 Read_Master_Log_Pos: 1484 Relay_Log_File: mariadb-10-5-21-replica-relay-bin.000008 Relay_Log_Pos: 844 Relay_Master_Log_File: master.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: db_primary |
This is expected behavior because –replicate-* replication filtering rules are applied locally on the replica instance and not from the primary. For such filtering rules SQL thread would look for the database name specified in –replicate-do-db and restrict the replication to that particular database (in this case, db_replica). And because of the same reason replication did not fail here on replica.
This can be verified from the relay log entry on the replica instance.
1 2 3 4 | ### INSERT INTO `db_primary`.`t` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ # Number of rows: 1 |
Our configuration of replicate_do_db=”db_primary” was wrong. So I changed the replicate_do_db = “db_replica” on the replica instance.
1 2 3 4 5 6 7 | MariaDB [db_replica]> SELECT @@replicate_do_db; +-------------------+ | @@replicate_do_db | +-------------------+ | db_replica | +-------------------+ 1 row in set (0.000 sec) |
The next step, reconfigure the replication on the instance, insert a new record, and start replication.
1 2 3 4 5 | MariaDB [db_replica]> CHANGE MASTER TO master_user='repl', master_password='XXXX', master_host='192.168.56.18', master_log_file='master.000002', master_log_pos=1484; Query OK, 0 rows affected (0.011 sec) MariaDB [db_replica]> START SLAVE; Query OK, 0 rows affected (0.001 sec) |
And now a new record is inserted on the replica instance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | MariaDB [db_replica]> select * from t; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.000 sec) MariaDB [db_replica]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.18 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master.000002 Read_Master_Log_Pos: 1695 Relay_Log_File: mariadb-10-5-21-replica-relay-bin.000002 Relay_Log_Pos: 713 Relay_Master_Log_File: master.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: db_replica |
Inserted one more record on the primary, and it got replicated successfully on the replica.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | MariaDB [db_primary]> INSERT INTO t VALUES (NULL); Query OK, 1 row affected (0.003 sec) MariaDB [db_primary]> SELECT * FROM t; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.001 sec) MariaDB [db_replica]> SELECT * FROM t; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.000 sec) |
Will a database named “db_primary2” replicate?
I tested one more thing: I created a new database on the primary to make sure that replication filtering works as expected on the replica, and neither this new database gets replicated nor breaks the replication on the replica.
1 2 | MariaDB [db_primary]> CREATE DATABASE db_primary2; Query OK, 1 row affected (0.001 sec) |
On replica, there were no issues because replication filtering worked as expected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | MariaDB [db_replica]> SHOW DATABASES LIKE '%db_primary%'; Empty set (0.000 sec) MariaDB [db_replica]> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.18 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master.000002 Read_Master_Log_Pos: 2049 Relay_Log_File: mariadb-10-5-21-replica-relay-bin.000002 Relay_Log_Pos: 1017 Relay_Master_Log_File: master.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: db_replica |
As per the MariaDB document, replicate_rewrite_db option can not be set dynamically until MariaDB 10.11. So I tested it for MariaDB 10.11 and noticed that it is available to be set dynamically and visible under global variables as well as in show slave output.
1 2 3 4 5 6 7 8 9 10 | MariaDB [(none)]> SELECT @@version; +-----------------+ | @@version | +-----------------+ | 10.11.7-MariaDB | +-----------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SET GLOBAL replicate_rewrite_db=`db_primary->db_replica`; Query OK, 0 rows affected (0.000 sec) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | MariaDB [(none)]> SELECT @@replicate_rewrite_db; +------------------------+ | @@replicate_rewrite_db | +------------------------+ | db_primary->db_replica | +------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.18 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master.000002 Read_Master_Log_Pos: 2049 Relay_Log_File: mariadb-10-5-21-replica-relay-bin.000005 Relay_Log_Pos: 552 Relay_Master_Log_File: master.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Rewrite_DB: db_primary->db_replica |
In conclusion, when a database with a different name needs to be replicated on the replica server in MariaDB versions prior to 10.11, the inability to dynamically set the replicate-rewrite-db parameter presents a challenge. This constraint necessitates careful planning, as altering database replication configurations requires restarting the MariaDB service.
The problem with replicate_do_db is understandable, because rewrite-db filter is applied before any other replication filters.