Documentation Home
MySQL 9.1 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.4Mb
PDF (A4) - 40.5Mb
Man Pages (TGZ) - 259.5Kb
Man Pages (Zip) - 366.7Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.1 Reference Manual  / ...  / Replication  / Replication Implementation  / How Servers Evaluate Replication Filtering Rules  /  Interactions Between Replication Filtering Options

19.2.5.3 Interactions Between Replication Filtering Options

If you use a combination of database-level and table-level replication filtering options, the replica first accepts or ignores events using the database options, then it evaluates all events permitted by those options according to the table options. This can sometimes lead to results that seem counterintuitive. It is also important to note that the results vary depending on whether the operation is logged using statement-based or row-based binary logging format. If you want to be sure that your replication filters always operate in the same way independently of the binary logging format, which is particularly important if you are using mixed binary logging format, follow the guidance in this topic.

The effect of the replication filtering options differs between binary logging formats because of the way the database name is identified. With statement-based format, DML statements are handled based on the current database, as specified by theUSE statement. With row-based format, DML statements are handled based on the database where the modified table exists. DDL statements are always filtered based on the current database, as specified by theUSE statement, regardless of the binary logging format.

An operation that involves multiple tables can also be affected differently by replication filtering options depending on the binary logging format. Operations to watch out for include transactions involving multi-tableUPDATE statements, triggers, cascading foreign keys, stored functions that update multiple tables, and DML statements that invoke stored functions that update one or more tables. If these operations update both filtered-in and filtered-out tables, the results can vary with the binary logging format.

If you need to guarantee that your replication filters operate consistently regardless of the binary logging format, particularly if you are using mixed binary logging format (binlog_format=MIXED), use only table-level replication filtering options, and do not use database-level replication filtering options. Also, do not use multi-table DML statements that update both filtered-in and filtered-out tables.

If you need to use a combination of database-level and table-level replication filters, and want these to operate as consistently as possible, choose one of the following strategies:

  1. If you use row-based binary logging format (binlog_format=ROW), for DDL statements, rely on theUSE statement to set the database and do not specify the database name. You can consider changing to row-based binary logging format for improved consistency with replication filtering. SeeSection 7.4.4.2, “Setting The Binary Log Format” for the conditions that apply to changing the binary logging format.

  2. If you use statement-based or mixed binary logging format (binlog_format=STATEMENT orMIXED), for both DML and DDL statements, rely on theUSE statement and do not use the database name. Also, do not use multi-table DML statements that update both filtered-in and filtered-out tables.

Example 19.7 A--replicate-ignore-db option and a--replicate-do-table option

On the replication source server, the following statements are issued:

USE db1;CREATE TABLE t2 LIKE t1;INSERT INTO db2.t3 VALUES (1);

The replica has the following replication filtering options set:

replicate-ignore-db = db1replicate-do-table = db2.t3

The DDL statementCREATE TABLE creates the table indb1, as specified by the precedingUSE statement. The replica filters out this statement according to its--replicate-ignore-db = db1 option, becausedb1 is the current database. This result is the same whatever the binary logging format is on the replication source server. However, the result of the DMLINSERT statement is different depending on the binary logging format:

  • If row-based binary logging format is in use on the source (binlog_format=ROW), the replica evaluates theINSERT operation using the database where the table exists, which is named asdb2. The database-level option--replicate-ignore-db = db1, which is evaluated first, therefore does not apply. The table-level option--replicate-do-table = db2.t3 does apply, so the replica applies the change to tablet3.

  • If statement-based binary logging format is in use on the source (binlog_format=STATEMENT), the replica evaluates theINSERT operation using the default database, which was set by theUSE statement todb1 and has not been changed. According to its database-level--replicate-ignore-db = db1 option, it therefore ignores the operation and does not apply the change to tablet3. The table-level option--replicate-do-table = db2.t3 is not checked, because the statement already matched a database-level option and was ignored.

If the--replicate-ignore-db = db1 option on the replica is necessary, and the use of statement-based (or mixed) binary logging format on the source is also necessary, the results can be made consistent by omitting the database name from theINSERT statement and relying on aUSE statement instead, as follows:

USE db1;CREATE TABLE t2 LIKE t1;USE db2;INSERT INTO t3 VALUES (1);

In this case, the replica always evaluates theINSERT statement based on the databasedb2. Whether the operation is logged in statement-based or row-based binary format, the results remain the same.