Considerations for MySQL replication Filtering

Considerations for MySQL replication Filtering: when there are DB filtering rules, the USE statement must be used in statment mode, and the form of db. Table Name, db. View, db. Storage program cannot appear.

Considerations for MySQL replication Filtering: when there are DB filtering rules, the USE statement must be used in statment mode, and the form of db. Table Name, db. View, db. Storage program cannot appear.

1. replicate_do_db and replicate_ignore_db do not appear at the same time. It is prone to confusion. It is meaningless.
Replicate_Do_DB: db1
Replicate_Ignore_DB: db2

Statement mode:
Use the use statement
Use db1; insert into tb1 values (1 );
Use db2; insert into tb2 values (2 );
The above data can be correctly copied on slave, tb1 has data, and tb2 has no data.

Do not use the use statement
Insert into db1.tb1 values (11 );
Insert into db2.tb2 values (11 );
As a result, tb1 has no data and tb2 has no data (as mentioned in the document, there should be data)

Row mode:
Whether or not the use statement is used, it can be copied correctly.
Summary: For the statement mode, the SQL statement must use the use statement and set multiple replicate_do_db to copy multiple databases.

2. When table-level filtering is performed only, statement mode can be correctly copied regardless of whether the use statement is used. The row mode is also supported. (The full name of the table that matches the table name: 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.
(Do and ignore should not be mixed unless DB filtering occurs)

3. When DB filtering rules are available, the USE statement must be used in statment mode. The format db. Table Name, db. view, and db. Storage program cannot appear. Row mode wins!
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 the first rule is skipped, the table rule is executed, and the row is inserted.

In statement mode,
The insert statement is ignored, and the table filtering rules are stopped at the DB level, so no check is performed.