CHANGE REPLICATION FILTER filter[, filter]
[, ...] [FOR CHANNEL channel]
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)
}
db_list:
db_name[, db_name][, ...]
tbl_list:
db_name.table_name[, db_name.table_name][, ...]
wild_tbl_list:
'db_pattern.table_pattern'[, 'db_pattern.table_pattern'][, ...]
db_pair_list:
(db_pair)[, (db_pair)][, ...]
db_pair:
from_db, to_db
CHANGE REPLICATION FILTER
sets one or more
replication filtering rules on the replica in the same way as
starting the replica mysqld with replication
filtering options such as
--replicate-do-db
or
--replicate-wild-ignore-table
.
Filters set using this statement differ from those set using the
server options in two key respects:
The statement does not require restarting the server to take effect, only that the replication SQL thread be stopped using
STOP REPLICA SQL_THREAD
first (and restarted withSTART REPLICA SQL_THREAD
afterwards).The effects of the statement are not persistent; any filters set using
CHANGE REPLICATION FILTER
are lost following a restart of the replica mysqld.
CHANGE REPLICATION FILTER
requires the
REPLICATION_SLAVE_ADMIN
privilege
(or the deprecated SUPER
privilege).
Use the FOR CHANNEL
clause to make a
replication filter specific to a replication channel, for
example on a multi-source replica. Filters applied without a
specific channel
FOR CHANNEL
clause are considered
global filters, meaning that they are applied to all replication
channels.
Global replication filters cannot be set on a MySQL server
instance that is configured for Group Replication, because
filtering transactions on some servers would make the group
unable to reach agreement on a consistent state. Channel
specific replication filters can be set on replication
channels that are not directly involved with Group
Replication, such as where a group member also acts as a
replica to a source that is outside the group. They cannot
be set on the group_replication_applier
or group_replication_recovery
channels.
The following list shows the CHANGE REPLICATION
FILTER
options and how they relate to
--replicate-*
server options:
REPLICATE_DO_DB
: Include updates based on database name. Equivalent to--replicate-do-db
.REPLICATE_IGNORE_DB
: Exclude updates based on database name. Equivalent to--replicate-ignore-db
.REPLICATE_DO_TABLE
: Include updates based on table name. Equivalent to--replicate-do-table
.REPLICATE_IGNORE_TABLE
: Exclude updates based on table name. Equivalent to--replicate-ignore-table
.REPLICATE_WILD_DO_TABLE
: Include updates based on wildcard pattern matching table name. Equivalent to--replicate-wild-do-table
.REPLICATE_WILD_IGNORE_TABLE
: Exclude updates based on wildcard pattern matching table name. Equivalent to--replicate-wild-ignore-table
.REPLICATE_REWRITE_DB
: Perform updates on replica after substituting new name on replica for specified database on source. Equivalent to--replicate-rewrite-db
.
The precise effects of REPLICATE_DO_DB
and
REPLICATE_IGNORE_DB
filters are dependent on
whether statement-based or row-based replication is in effect.
See Section 19.2.5, “How Servers Evaluate Replication Filtering Rules”, for more information.
Multiple replication filtering rules can be created in a single
CHANGE REPLICATION FILTER
statement by
separating the rules with commas, as shown here:
CHANGE REPLICATION FILTER
REPLICATE_DO_DB = (d1), REPLICATE_IGNORE_DB = (d2);
Issuing the statement just shown is equivalent to starting the
replica mysqld with the options
--replicate-do-db=d1
--replicate-ignore-db=d2
.
On a multi-source replica, which uses multiple replication
channels to process transaction from different sources, use the
FOR CHANNEL
clause to set a
replication filter on a replication channel:
channel
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (d1) FOR CHANNEL channel_1;
This enables you to create a channel specific replication filter
to filter out selected data from a source. When a FOR
CHANNEL
clause is provided, the replication filter
statement acts on that replication channel, removing any
existing replication filter which has the same filter type as
the specified replication filters, and replacing them with the
specified filter. Filter types not explicitly listed in the
statement are not modified. If issued against a replication
channel which is not configured, the statement fails with an
ER_SLAVE_CONFIGURATION error. If issued
against Group Replication channels, the statement fails with an
ER_SLAVE_CHANNEL_OPERATION_NOT_ALLOWED
error.
On a replica with multiple replication channels configured,
issuing CHANGE REPLICATION FILTER
with no FOR CHANNEL
clause configures the
replication filter for every configured replication channel, and
for the global replication filters. For every filter type, if
the filter type is listed in the statement, then any existing
filter rules of that type are replaced by the filter rules
specified in the most recently issued statement, otherwise the
old value of the filter type is retained. For more information
see Section 19.2.5.4, “Replication Channel Based Filters”.
If the same filtering rule is specified multiple times, only the
last such rule is actually used. For
example, the two statements shown here have exactly the same
effect, because the first REPLICATE_DO_DB
rule in the first statement is ignored:
CHANGE REPLICATION FILTER
REPLICATE_DO_DB = (db1, db2), REPLICATE_DO_DB = (db3, db4);
CHANGE REPLICATION FILTER
REPLICATE_DO_DB = (db3, db4);
This behavior differs from that of the
--replicate-*
filter options where specifying
the same option multiple times causes the creation of multiple
filter rules.
Names of tables and database not containing any special
characters need not be quoted. Values used with
REPLICATION_WILD_TABLE
and
REPLICATION_WILD_IGNORE_TABLE
are string
expressions, possibly containing (special) wildcard characters,
and so must be quoted. This is shown in the following example
statements:
CHANGE REPLICATION FILTER
REPLICATE_WILD_DO_TABLE = ('db1.old%');
CHANGE REPLICATION FILTER
REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');
Values used with REPLICATE_REWRITE_DB
represent pairs of database names; each
such value must be enclosed in parentheses. The following
statement rewrites statements occurring on database
db1
on the source to database
db2
on the replica:
CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB = ((db1, db2));
The statement just shown contains two sets of parentheses, one
enclosing the pair of database names, and the other enclosing
the entire list. This is perhaps more easily seen in the
following example, which creates two
rewrite-db
rules, one rewriting database
dbA
to dbB
, and one
rewriting database dbC
to
dbD
:
CHANGE REPLICATION FILTER
REPLICATE_REWRITE_DB = ((dbA, dbB), (dbC, dbD));
The CHANGE REPLICATION FILTER
statement replaces replication filtering rules only for the
filter types and replication channels affected by the statement,
and leaves other rules and channels unchanged. If you want to
unset all filters of a given type, set the filter's value
to an explicitly empty list, as shown in this example, which
removes all existing REPLICATE_DO_DB
and
REPLICATE_IGNORE_DB
rules:
CHANGE REPLICATION FILTER
REPLICATE_DO_DB = (), REPLICATE_IGNORE_DB = ();
Setting a filter to empty in this way removes all existing
rules, does not create any new ones, and does not restore any
rules set at mysqld startup using --replicate-*
options on the command line or in the configuration file.
The RESET REPLICA
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 replica are copied to
them, and no channel specific replication filters are applied.
For more information, see Section 19.2.5, “How Servers Evaluate Replication Filtering Rules”.