In 8.0.1, we introduced per-channel replication filters. This allows user to specify different replication filter rules for different slave replication channels. Therefore, the slave will be able to filter out (or transform) the execution of selected replicated changes on a per-source basis when using multi-source replication.

In 5.7, we have global replication filters. This allows slave to filter out (or transform) the execution of selected replicated data from all sources when using multi-source replication.

User Stories

The per-channel replication filters are needed in case when using multi-source replications. This feature is typically used in the following way:

In the following examples, a slave has slave replication channels ‘channel1’ for Master1, ‘channel2’ for Master2 and ‘channel3’ for Master3.

Example1: Consider a Master1 with databases DB11, DB12 and DB13 and a Master2 with databases DB21, DB22 and DB23. If a slave wants to replicate everything from Master1, and just wants to replicate the data of DB21 from Master2, then we would filter data from Master2 by using per-channel replication filter ‘REPLICATE-DO-DB=DB21‘ on ‘channel2’.

Example2: Consider a Master1 with databases DB1, a Master2 with databases DB1 and a Master3 with databases DB1. If a slave wants to filter out
replicated data of DB1 from all other sources except Master1, then we would filter data from Master2 and Master3 by using per-channel replication filter ‘REPLICATE-IGNORE-DB=DB1‘ on ‘channel1’ and ‘channel2’.

Example3: Consider a Master1 with databases DB1 and a Master2 with databases DB1. If a slave wants to replicate everything from Master1 and Master2 without possible conflict, then we would transform data from Master2 by using per-channel replication filter ‘REPLICATE-REWRITE-DB=(DB1->DB2)‘ on ‘channel2’.

Use startup options: --replicate-* to configure replication filters

--replicate-do-db=<channel_name>:<database_id>
--replicate-ignore-db=<channel_name>:<database_id>
--replicate-do-table=<channel_name>:<table_id>
--replicate-ignore-table=<channel_name>:<table_id>
--replicate-rewrite-db=<channel_name>:<db1->db2>
--replicate-wild-do-table=<channel_name>:<table regexid>
--replicate-wild-ignore-table=<channel_name>:<table regexid>

Without specifying channel_name and a followed ‘colon’ in filter variable, the startup options configure global replication filters. See below:

--replicate-do-db=<database_id>
--replicate-ignore-db=<database_id>
--replicate-do-table=<table_id>
--replicate-ignore-table=<table_id>
--replicate-rewrite-db=<from_db>-><to_db>
--replicate-wild-do-table=<table regex>
--replicate-wild-ignore-table=<table regex>

Global replication filters are retained in 8.0.1 for backward compatibility.

Note: By startup options, you can add the filters to my.cnf to be persistent after any possible restart.

How global and per-channel replication filters work together?

1. Any global replication filter option just adds the filter to global replication filters on the filter type (do_db, do_ignore_table, etc);
2. Any per-channel replication filter option adds the filter to the specified channel’s replication filters on the filter type;
3. Each slave replication channel copies global replication filters to its per-channel replication filters if no per-channel replication filter on the filter type when it is being configured;
4. Each channel uses only its per-channel replication filters to filter the replication stream.

Example: Suppose channels ‘ch1’ and ‘ch2’ exist before the server starts, the command line options --replicate-do-db=db1 --replicate-do-db=ch1:db2 --replicate-do-db=db3 --replicate-ignore-db=db4 --replicate-ignore-db=ch2:db5 would result in:

global replication filters: do_db=db1,db3, ignore_db=db4
filters on channel ‘ch1’:   do_db=db2 ignore_db=db4
filters on channel ‘ch2’:   do_db=db1,db3 ignore_db=db5

Use ‘CHANGE REPLICATION FILTER‘ to set replication filters on-line

CHANGE REPLICATION FILTER filter [, filter...] [FOR CHANNEL <channel_name>]

filter:
REPLICATE_DO_DB = (db_list)
| REPLICATE_IGNORE_DB = (db_list)
| REPLICATE_DO_TABLE = (tbl_list)
| REPLICATE_IGNORE_TABLE = (tbl_list)
| REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
| REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
| REPLICATE_REWRITE_DB = (db_pair_list)

1. If a ‘FOR CHANNEL‘ clause is provided, the statement acts on the specified channel’s replication filters, if you have several types of filters (do_db, do_ignore_table, wild_do_table, etc), only those that you mention explicitly in the statement are replaced;
2. If no ‘FOR CHANNEL‘ clause is provided, the statement acts on global and all channels’ replication filters, has the similar logic with the ‘FOR CHANNEL’ case.

Note: This can be done on-line but the slave SQL thread must be stopped first.

Use ‘RESET SLAVE ALL [FOR CHANNEL <channel_name>]‘ to remove channel’s replication filters

1. If a ‘FOR CHANNEL’ clause is provided, the statement removes the specified channel and the channel’s replication filters;
2. If no ‘FOR CHANNEL’ clause is provided, the statement remove all channels and all channel’s replication filters;
3. You can “remove” replication filters with ‘CHANGE REPLICATION FILTER‘ alone, e.g., CHANGE REPLICATION FILTER Replicate_Do_DB=(), Replicate_Do_Table = () [FOR CHANNEL <channel_name>]

INTRODUCE P_S.replication_applier_filters to monitor channels’ replication filters

On each specific channel, you can see that what is the filter type by ‘FILTER_NAME‘, what is the filter rule by ‘FILTER_RULE‘, how the filter rule was configured by ‘CONFIGURED_BY‘, when the filter rule was configured by ‘ACTIVE_SINCE‘, and how many times the filter rule was hit by ‘COUNTER‘.

Note: ‘SHOW SLAVE STATUS‘ was also slightly extended to show parts of this information.

INTRODUCE P_S.replication_applier_global_filters to monitor global replication filters

On each global replication filter, you can see that what is the filter rule by ‘FILTER_RULE‘, how the filter rule is configured by ‘CONFIGURED_BY‘, when the filter rule was configured by ‘ACTIVE_SINCE‘.

Summary

This feature introduces per-channel replication filters, which can apply replication filters for each replication channel when using multi-source replication, can be applied on-line (CHANGE REPLICATION FILTER filter [, filter...] [FOR CHANNEL <channel_name>]) or added to the configuration file as startup options: --replicate-*, and can be monitored by table performance_schema.replication_applier_global_filters. It is available in MySQL 8.0.1. Please try it out and let us know the feedback.