MySQL replication filter repeat how to resolve

1, replicate_do_db and replicate_ignore_db do not appear at the same time. Prone to confusion. is meaningless.

Replicate_do_db:db1

Replicate_ignore_db:db2

Statement mode:

Using the USE statement

Use Db1;insert into TB1 values (1);

Use Db2;insert into TB2 values (2);

The above on the slave can be copied correctly, tb1 have data, TB2 no data.

No USE statement

INSERT into DB1.TB1 values (11);

INSERT into DB2.TB2 values (11);

Results tb1 No data, TB2 no data (according to the document, should have)

Row mode:

Copy correctly whether use statement is used or not

Summary: For statement mode, the SQL statement must use the using statement and set up multiple replicate_do_db to replicate multiple db.

2, only for table-level filtering, for statement mode, whether or not using use statement, can be copied correctly. The row mode is also possible. (table name it matches the full name of the table: Database name + table name)

Replicate_do_table=db1.tb1

Statement mode:

Use DB1; INSERT into TB1 values (22);

INSERT into DB1.TB1 values (22);

The replication results are consistent.

(same do and ignore do not mix unless a filter db is present)

3, there are DB filtering rules, the statment mode must use the using statement, cannot appear db. Table name, db. view, db. stored program in this form. Row mode is a victory!

Replicate-ignore-db = DB1

Replicate-do-table = Db2.tbl2

Use DB1;

INSERT into Db2.tbl2 VALUES (1);

In row mode,

The default db is not DB1, so skip the first rule, execute the table's rules, and the row is inserted

In the statement mode,

The INSERT statement was ignored and the filter rules for the table were stopped at the DB level, so no checks were done.