replicate_rewrite_db as a system variable in MariaDB

MariaDB 10.11.0, our latest preview release, features quite a number of improvements. The one we’ll talk about here is replicate-rewrite-db. This option has become a system variable from 10.11.0 based on MDEV-15530. Before this version it was just an option used by the mariadbd and mariadb-binlog binaries. There have been no behaviour changes with the option; it has simply become a dynamic variable.

How to try out this feature

The fastest way is to take MariaDB 10.11 for a spin in docker / podman (consult the blog mariadb-replication-using-containers) with the following commands:

To start a primary 10.11.0 MariaDB container, clone the directory, navigate to the cloned directory and run the following command:

docker run -d --rm --name mariadb-primary \
-v $PWD/config-files/primarycnf:/etc/mysql/conf.d:z \
-v $PWD/primaryinit:/docker-entrypoint-initdb.d:z \
-v $PWD/log-files-primary:/var/lib/mysql \
-e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=True \
quay.io/mariadb-foundation/mariadb-devel:10.11

To start the replica/secondary run the command to start the mariadbd process as well as specify the replicate-rewrite-db option on the command line:

docker run -d --rm --name mariadb-secondary-1 \
-v $PWD/config-files/secondary-1:/etc/mysql/conf.d:z \
-v $PWD/secondaryinit:/docker-entrypoint-initdb.d:z \
-v $PWD/log-files-secondary-1:/var/lib/mysql \
-e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=True \
quay.io/mariadb-foundation/mariadb-devel:10.11 --replicate-rewrite-db='db1->db2'

Besides specifying the option as a command line argument, you can use a configuration file. For reference please see the previous post about using plugins.

You can check the system variable on the secondary side:

$ docker exec mariadb-secondary-1 mariadb -uroot -e "show global variables like 'replicate_rewrite%'";
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| replicate_rewrite_db | db1->db2 |
+----------------------+----------+

The same information can be obtained from show replica status:

$ docker exec mariadb-secondary-1 mariadb -uroot -e "show replica status \G"|grep Replicate_Rewrite
          Replicate_Rewrite_DB: db1->db2

To dynamically change the variable, connect to the secondary, stop replica threads and set the variable. Additionally you can add a pair list of databases.

STOP REPLICA;
SET @@GLOBAL.replicate_rewrite_db="db1->db1_repl, db2->db2_repl";
START REPLICA;
select @@global.replicate_rewrite_db;
+-------------------------------+
| @@global.replicate_rewrite_db |
+-------------------------------+
| db1->db1_repl,db2->db2_repl   |
+-------------------------------+

Now to validate the changes, create database db1_repl on the secondary and execute table creation on the primary. You can check the effect on the secondary:

# On secondary
docker exec mariadb-secondary-1 mariadb -uroot -e "create database db1_repl;";
# On primary
docker exec mariadb-primary mariadb -uroot -e "create or replace database db1; use db1; create table tp(t int); insert into tp values(1),(2);";
# On secondary
docker exec mariadb-secondary-1 mariadb -uroot -e "use db1_repl; show tables;";
+--------------------+
| Tables_in_db1_repl |
+--------------------+
| tp                 |
+--------------------+

If you’re running podman, just replace docker with podman.
Alternatively, you can download the binary directly from mariadb.org/download