Geen cache-versie.

Caching uitgeschakeld. Standaardinstelling voor deze pagina:ingeschakeld (code DEF204)
Als het scherm te langzaam is, kunt u de gebruikersmodus uitschakelen om de cacheversie te bekijken.

Rechercher dans le manuel MySQL

13.4.2.2 CHANGE REPLICATION FILTER Syntax

Inhoudsopgave
  1. CHANGE REPLICATION FILTER filter[, filter]
  2.   [, ...] [FOR CHANNEL channel]
  3.  
  4. filter:
  5.     REPLICATE_DO_DB = (db_list)
  6.   | REPLICATE_IGNORE_DB = (db_list)
  7.   | REPLICATE_DO_TABLE = (tbl_list)
  8.   | REPLICATE_IGNORE_TABLE = (tbl_list)
  9.   | REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
  10.   | REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
  11.   | REPLICATE_REWRITE_DB = (db_pair_list)
  12.  
  13. db_list:
  14.     db_name[, db_name][, ...]
  15.  
  16. tbl_list:
  17.     db_name.table_name[, db_name.table_name][, ...]
  18. wild_tbl_list:
  19.     'db_pattern.table_pattern'[, 'db_pattern.table_pattern'][, ...]
  20.  
  21. db_pair_list:
  22.     (db_pair)[, (db_pair)][, ...]
  23.  
  24. db_pair:
  25.     from_db, to_db

CHANGE REPLICATION FILTER sets one or more replication filtering rules on the slave in the same way as starting the slave mysqld with replication filtering options such as --replicate-do-db or --replicate-wild-ignore-table. Unlike the case with the server options, this statement does not require restarting the server to take effect, only that the slave SQL thread be stopped using STOP SLAVE SQL_THREAD first (and restarted with START SLAVE SQL_THREAD afterwards). CHANGE REPLICATION FILTER requires the REPLICATION_SLAVE_ADMIN or SUPER privilege. Use the FOR CHANNEL channel clause to make a replication filter specific to a replication channel, for example on a multi-source replication slave. Filters applied without a specific FOR CHANNEL clause are considered global filters, meaning that they are applied to all replication channels.

Note

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 replication slave to a master 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 slave after substituting new name on slave for specified database on master. 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 17.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:

  1. CHANGE REPLICATION FILTER
  2.     REPLICATE_DO_DB = (d1), REPLICATE_IGNORE_DB = (d2);

Issuing the statement just shown is equivalent to starting the slave mysqld with the options --replicate-do-db=d1 --replicate-ignore-db=d2.

On a multi-source replication slave, which uses multiple replication channels to process transaction from different sources, use the FOR CHANNEL channel clause to set a replication filter on a replication channel:

  1. 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 slave 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 slave 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 replication slave with multiple replication channels configured, issuing CHANGE REPLICATION FILTER with no FOR CHANNEL clause configures the replication filter for every configured slave 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 17.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:

  1. CHANGE REPLICATION FILTER
  2.     REPLICATE_DO_DB = (db1, db2), REPLICATE_DO_DB = (db3, db4);
  3.  
  4. CHANGE REPLICATION FILTER
  5.     REPLICATE_DO_DB = (db3, db4);
Caution

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:

  1. CHANGE REPLICATION FILTER
  2.     REPLICATE_WILD_DO_TABLE = ('db1.old%');
  3.  
  4. CHANGE REPLICATION FILTER
  5.     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 master to database db2 on the slave:

  1. 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:

  1. CHANGE REPLICATION FILTER
  2.   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:

  1. CHANGE REPLICATION FILTER
  2.     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 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.

For more information, see Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”.


Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-change-replication-filter.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut