Movatterモバイル変換

CHANGE REPLICATION FILTERfilter[,filter][, ...] [FOR CHANNELchannel]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 replicamysqld 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:

CHANGE REPLICATION FILTER requires theREPLICATION_SLAVE_ADMIN privilege (or the deprecatedSUPER privilege).

Use theFOR CHANNELchannel clause to make a replication filter specific to a replication channel, for example on a multi-source replica. Filters applied without a specificFOR CHANNEL clause are considered global filters, meaning that they are applied to all replication channels.

The following list shows theCHANGE REPLICATION FILTER options and how they relate to--replicate-* server options:

The precise effects ofREPLICATE_DO_DB andREPLICATE_IGNORE_DB filters are dependent on whether statement-based or row-based replication is in effect. SeeSection 19.2.5, “How Servers Evaluate Replication Filtering Rules”, for more information.

Multiple replication filtering rules can be created in a singleCHANGE 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 replicamysqld 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 theFOR CHANNELchannel clause to set a replication filter on a replication 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 aFOR 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 anER_SLAVE_CONFIGURATION error. If issued against Group Replication channels, the statement fails with anER_SLAVE_CHANNEL_OPERATION_NOT_ALLOWED error.

On a replica with multiple replication channels configured, issuingCHANGE REPLICATION FILTER with noFOR 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 seeSection 19.2.5.4, “Replication Channel Based Filters”.

If the same filtering rule is specified multiple times, only thelast such rule is actually used. For example, the two statements shown here have exactly the same effect, because the firstREPLICATE_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);

Names of tables and database not containing any special characters need not be quoted. Values used withREPLICATION_WILD_TABLE andREPLICATION_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 withREPLICATE_REWRITE_DB representpairs of database names; each such value must be enclosed in parentheses. The following statement rewrites statements occurring on databasedb1 on the source to databasedb2 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 tworewrite-db rules, one rewriting databasedbA todbB, and one rewriting databasedbC todbD:

CHANGE REPLICATION FILTER  REPLICATE_REWRITE_DB = ((dbA, dbB), (dbC, dbD));

TheCHANGE 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 existingREPLICATE_DO_DB andREPLICATE_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.

TheRESET 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, seeSection 19.2.5, “How Servers Evaluate Replication Filtering Rules”.