MySQL Bugs: #80843: Replication filters per channel in Multi-Source Replication

Bug #80843	Replication filters per channel in Multi-Source Replication
Submitted:	24 Mar 2016 6:48	Modified:	18 Oct 2017 13:37
Reporter:	Abdel-Mawla Gharieb	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Mutli-Source Replication, replication filters

Description:
Before MySQL 5.7 or - in other words - in the traditional MySQL Replication, the replication filters are OK as the slave has only one master which is not the case in the Multi-Source Replication in 5.7!

If two masters or more for the same slave have a same DB, we can not use the replication filters to make the slave replicate it from only one of them as the replication filters are global for all running channels.

Adding the "FOR CHANNEL" keyword to the "CHANGE REPLICATION FILTER" statement might be useful here.

How to repeat:
Configure Multi-Source Replication and create same DB on more than one master for that slave.

The way to reproduce it, I setup a multi-source slave with three channels for box02,03 and 04 and setup a filter as below. The filter was applied on all the channels and what was said here before by Abdel-Mawla is very welcomed as it cann be added to the CHANGE REPLICATION FILTER sintaxe a for channel to point out to what channel the filter will be added.

Test-case output:

mysql> stop slave; change replication filter replicate_rewrite_db=((box02,box03)); start slave;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.15 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.12
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 824
               Relay_Log_File: file01-relay-bin-box02.000022
                Relay_Log_Pos: 534
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                  Master_UUID: fa11b361-d572-11e5-b63e-0800274fb806
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
      [...snip...]
                Auto_Position: 1
         Replicate_Rewrite_DB: (box02,box03)
                 Channel_Name: box02
           Master_TLS_Version:
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.13
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 314
               Relay_Log_File: file01-relay-bin-box03.000021
                Relay_Log_Pos: 487
        Relay_Master_Log_File: mysql-bin.000008
             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:
[...snip...]
                Auto_Position: 1
         Replicate_Rewrite_DB: (box02,box03)
                 Channel_Name: box03
           Master_TLS_Version:
*************************** 3. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.14
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 314
               Relay_Log_File: file01-relay-bin-box04.000021
                Relay_Log_Pos: 487
        Relay_Master_Log_File: mysql-bin.000008
             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:
[...snip...]
                Auto_Position: 1
         Replicate_Rewrite_DB: (box02,box03)
                 Channel_Name: box04
           Master_TLS_Version:
3 rows in set (0.00 sec)

My use case:
Two functional shards: e.g. users and comments

The users shard has
- users
- avatars
- bio's
- groups
- etc.

The comments shard has:
- user_comments
- post_comments

Both shards are 1 master with 3 slaves

It might be useful to replicate the list of users to the comments shard.
This needs a filter because I don't want to replicate the other tables.

But then later on I might want to replicate one table from another functional shard (e.g. posts).
I could do this with replicate_do_table('user.user', 'posts.post_categories'), but that won't work if the names of the tables are not globally unique over all functional shards.

Previously this would not have been possible, but because of both GTID and multisource replication we are now very close.

To add another perhaps very common use case:
- replication of "common" tables between different replication chains.