On the same day last year, I wrote a post about replication filters in MySQL Multi Source Replication and whether we can set the filters per replication channel or not (Bug #80843). My feature request – as well as some others – has been implemented in MySQL 8.0.1. Thanks Oracle for the implementation. (Full list of changes can be checked out here)

In this post, I’ll demonstrate how to set replication filters per channel in MySQL Multi Source Replication.

Setting up and configuring Multi Source Replication:

Note: If you know how to setup MySQL Multi Source Replication you can jump to the next section.

1. Install MySQL on three servers, master1, master2 and slave.
2. Make sure to enable binary logging on the master servers, master1 and master2 (e.g. add log_bin=/path/mysql-bin to my.cnf) and have a unique server_id value for the three servers.
3. Add the following variables to the slave’s my.cnf file:
   master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
4. Create a replication user on both masters to be used by the slave:
   CREATE USER 'repl'@'slave_ip' IDENTIFIED BY 's3cret';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip';
5. Get the binary logs position on each master server by executing “SHOW MASTER STATUS;” command.
6. Set two replication channels on the slave as below:
   Channel for master1:
   CHANGE MASTER TO MASTER_HOST='master1_ip', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='s3cret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=381 FOR CHANNEL 'master1';
   Channel for master2:
   CHANGE MASTER TO MASTER_HOST='master2_ip', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='s3cret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=381 FOR CHANNEL 'master2';
7. Start replication:
   START SLAVE; # ==> This will start all channels on that slave or you can do START SLAVE FOR CHANNEL 'channel_name'  for each channel instead.
8. Check the replication on the slave to confirm it is working:
   mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.138.0.9
[…]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
[…]
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.138.0.11
[…]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
[…]
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master2
Master_TLS_Version:
2 rows in set (0.00 sec)

OK, now we have got the multi source replication setup and running, how can we set replication filters for each channel?

Apply Replication filter for each channel:

There are two ways of setting the replication filters per channel as below:

1. Using “CHANGE REPLICATION FILTER … FOR CHANNEL 'channel_name'” statement. This can be done online but the slave SQL_THREAD must be stopped first.
2. Using system variables in my.cnf file as channel_name.filter=value (e.g. master1.replicate_do_db=db1). This will require a MySQL restart.

Note: You can do it online using CHANGE REPLICATION FILTER and then add the filter to my.cnf to be persistent after any possible restart.

Back to our example. I’ll create two databases (db1 & db2) on each master. The slave should replicate db1 only from master1 and replicate db2 only from master2. Before creating the databases, we need to apply the filters on the slave first:

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE REPLICATION FILTER replicate_do_db=(db1) FOR CHANNEL 'master1';
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE REPLICATION FILTER replicate_do_db=(db2) FOR CHANNEL 'master2';
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE\G
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.138.0.9
[…]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
[…]
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.138.0.11
[…]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db2
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
[…]
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master2
Master_TLS_Version:
2 rows in set (0.00 sec)

Test and confirm the filters:

First, we will create db1 on both masters, insert some data and check from which master the data has been replicated to the slave:

On master1:

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)
mysql> USE db1
Database changed
mysql> CREATE TABLE t1 (id INT(10) PRIMARY KEY, name VARCHAR(50))ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (1,'master1');
Query OK, 1 row affected (0.00 sec)

On master2:

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)
mysql> USE db1
Database changed
mysql> CREATE TABLE t1 (id INT(10) PRIMARY KEY, name VARCHAR(50))ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t1 VALUES (1,'master2');
Query OK, 1 row affected (0.01 sec)

On the slave, check from which master the slave replicated db1:

mysql> SELECT * FROM db1.t1;
+----+---------+
| id | name |
+----+---------+
| 1 | master1 |
+----+---------+
1 row in set (0.00 sec)

Well, as expected. The slave replicated db1 only from master1. Let’s do the same for db2:

On master 1:

mysql> CREATE DATABASE db2;
Query OK, 1 row affected (0.00 sec)
mysql> USE db2
Database changed
mysql> CREATE TABLE t1 (id INT(10) PRIMARY KEY, name VARCHAR(50))ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (1,'master1');
Query OK, 1 row affected (0.01 sec)

On master2:

mysql> CREATE DATABASE db2;
Query OK, 1 row affected (0.01 sec)
mysql> USE db2
Database changed
mysql> CREATE TABLE t1 (id int(10) PRIMARY KEY, name VARCHAR(50))ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (1,'master2');
Query OK, 1 row affected (0.00 sec)

and on the slave:

mysql> SELECT * FROM db2.t1;
+----+---------+
| id | name |
+----+---------+
| 1 | master2 |
+----+---------+
1 row in set (0.00 sec)

The slave replicated db2 only from master2!

Conclusion:

• Starting from MySQL 8.0.1, we can apply replication filters for each replication channel in MySQL Multi Source Replication.
• The filter can be applied online (CHANGE REPLICATION FILTER …. FOR CHANNEL ‘channel_name’) or added to the config file as channel_name.filter=value.