Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.2Mb
PDF (A4) - 38.3Mb
PDF (RPM) - 33.1Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 133.2Kb
Man Pages (Zip) - 189.3Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

17.2.5.4 Replication Channel Based Filters

This section explains how to work with replication filters when multiple replication channels exist, for example in a multi-source replication topology. Before MySQL 8.0, replication filters were global - filters were applied to all replication channels. From MySQL 8.0, replication filters can be global or channel specific, enabling you to configure multi-source replication slaves with replication filters on specific replication channels. Channel specific replication filters are particularly useful in a multi-source replication topology when the same database or table is present on multiple masters, and the slave is only required to replicate it from one master.

For more background information, see Section 17.1.4, “MySQL Multi-Source Replication” and Section 17.2.3, “Replication Channels”.

Important

On a MySQL server instance that is configured for Group Replication, channel specific replication filters can be used on replication channels that are not directly involved with Group Replication, such as where a group member also acts as a replication slave to a master that is outside the group. They cannot be used on the group_replication_applier or group_replication_recovery channels. Filtering on these channels would make the group unable to reach agreement on a consistent state.

Overview of Replication Filters and Channels

When multiple replication channels exist, for example in a multi-source replication topology, replication filters are applied as follows:

  • Any global replication filter specified is added to the global replication filters of the filter type (do_db, do_ignore_table, and so on).

  • Any channel specific replication filter adds the filter to the specified channel’s replication filters for the specified filter type.

  • Each slave replication channel copies global replication filters to its channel specific replication filters if no channel specific replication filter of this type is configured.

  • Each channel uses its channel specific replication filters to filter the replication stream.

The syntax to create channel specific replication filters extends the existing SQL statements and command options. When a replication channel is not specified the global replication filter is configured to ensure backwards compatibility. The CHANGE REPLICATION FILTER statement supports the FOR CHANNEL clause to configure channel specific filters online. The --replicate-* command options to configure filters can specify a replication channel using the form --replicate-filter_type=channel_name:filter_details. For example, suppose channels channel_1 and channel_2 exist before the server starts, starting the slave with the command line options --replicate-do-db=db1 --replicate-do-db=channel_1:db2 --replicate-do-db=db3 --replicate-ignore-db=db4 --replicate-ignore-db=channel_2:db5 would result in:

  • Global replication filters: do_db=db1,db3, ignore_db=db4

  • Channel specific filters on channel_1: do_db=db2 ignore_db=db4

  • Channel specific filters on channel_2: do_db=db1,db3 ignore_db=db5

To monitor the replication filters in such a setup use the replication_applier_global_filters and replication_applier_filters tables.

Configuring Channel Specific Replication Filters at Startup

The replication filter related command options can take an optional channel followed by a colon, followed by the filter specification. The first colon is interpreted as a separator, subsequent colons are interpreted as literal colons. The following command options support channel specific replication filters using this format:

  • --replicate-do-db=channel:database_id

  • --replicate-ignore-db=channel:database_id

  • --replicate-do-table=channel:table_id

  • --replicate-ignore-table=channel:table_id

  • --replicate-rewrite-db=channel:db1-db2

  • --replicate-wild-do-table=channel:table regexid

  • --replicate-wild-ignore-table=channel:table regexid

If you use a colon but do not specify a channel for the filter option, for example --replicate-do-db=:database_id, the option configures the replication filter for the default replication channel. The default replication channel is the replication channel which always exists once replication has been started, and differs from multi-source replication channels which you create manually. When neither the colon nor a channel is specified the option configures the global replication filters, for example --replicate-do-db=database_id configures the global --replicate-do-db filter.

If you configure multiple rewrite-db=from_name->to_name options with the same from_name database, all filters are added together (put into the rewrite_do list) and the first one takes effect.

Changing Channel Specific Replication Filters Online

In addition to the --replicate-* options, replication filters can be configured using the CHANGE REPLICATION FILTER statement. This removes the need to restart the server, but the slave applier thread must be stopped while making the change. To make this statement apply the filter to a specific channel, use the FOR CHANNEL channel clause. For example:

CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1) FOR CHANNEL channel_1;

When a FOR CHANNEL clause is provided, the statement acts on the specified channel's replication filters. If multiple types of filters (do_db, do_ignore_table, wild_do_table, and so on) are specified, only the specified filter types are replaced by the statement. In a replication topology with multiple channels, for example on a multi-source replication slave, when no FOR CHANNEL clause is provided, the statement acts on the global replication filters and all channels’ replication filters, using a similar logic as the FOR CHANNEL case. For more information see Section 13.4.2.2, “CHANGE REPLICATION FILTER Syntax”.

Removing Channel Specific Replication Filters

When channel specific replication filters have been configured, you can remove the filter by issuing an empty filter type statement. For example to remove all REPLICATE_REWRITE_DB filters from a replication channel named channel_1 issue:

CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=() FOR CHANNEL channel_1;

Any REPLICATE_REWRITE_DB filters previously configured, using either command options or CHANGE REPLICATION FILTER, are removed.

The RESET SLAVE ALL statement removes channel specific replication filters that were set on channels deleted by the statement. When the deleted channel or channels are recreated, any global replication filters specified for the slave are copied to them, and no channel specific replication filters are applied.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.