MySQL Replication过滤选项replicate*过滤规则

增加下面选项时

Replicate_Do_DB: test1

Replicate_Ignore_Table: test1.test1_1

test1.test1_1 test1.test1_2

在master上执行use test1;drop table test1_1;

在slave上执行show slave status\G未报错

在master上执行use test1;drop table test1_2;

在slave上执行show slave status\G报错

结论：说明Replicate_Ignore_Table起作用（文档中的流程图不一致）

选项同上，并且表结构都有

在master上执行use test1;insert into test1_1 values()，查看从库未同步（文档中的流程图不一致）

在master上执行use test1;insert into test1_2 values()，查看从库同步

Replicate_Do_DB: test1

Replicate_Do_Table: test2.test2_1

Replicate_Ignore_Table: test1.test1_1

主从表结构都相同，都是

在主库上执行use test2;insert into test2_1 values()，查看从库未同步

也就是说Replicate_Do_DB未匹配并且不包含replicate-ignore-db时丢弃（文档中的流程图不一致）

Replicate_Do_DB: test1

Replicate_Ignore_DB: test2

主从表结果都相同，都是

在主库上执行use test3;insert into test3_1 values()，查看从库未同步（文档中的流程图不一致）

通过上面的测试得出结论：

①当选项中包含Replicate_Do_DB时，没有match到的语句全部丢弃了，也就是说包含Replicate_Do_DB，Replicate_Ignore_DB没有起作用

②mysql官方文档中的流程图不准确

③当Replicate_Do_DB选项匹配时，会进一步的通过Replicate_Do_Table和Replicate_Ignore_Table进行筛选。

文档中提到的一些注意事项：

①In general, to make it easier to determine what effect an option set will have, it is recommended that you avoid mixing "do" and "ignore" options, or wildcard and nonwildcard options.

避免混合使用do和ignore以及通配符和非通配符

②First, as a preliminary condition, the slave checks whether statement-based replication is enabled. If so, and the statement occurs within a stored function, the slave executes the statement and exits. If row-based replication is enabled, the slave does not know whether a statement occurred within a stored function on the master, so this condition does not apply.

For statement-based replication, replication events represent statements (all changes making up a given event are associated with a single SQL statement); for row-based replication, each event represents a change in a single table row (thus a single statement such as UPDATE mytable SET mycol = 1 may yield many row-based events). When viewed in terms of events, the process of checking table options is the same for both row-based and statement-based replication.

当binlog-format为row-level的时候，执行行所引起的触发器、存储过程、UDF等是不会执行，因为此时mysql不能确定引发的sql语句是否已经写入到binlog中。当binlog-format为row-level时，一条语句会产生很多binlog event。

最后mysql文档中举了一个例子：

Suppose that we have two tables mytbl1 in database db1 and mytbl2 in database db2 on the master, and the slave is running with the following options (and no other replication filtering options):

replicate-ignore-db = db1

replicate-do-table = db2.tbl2

Now we execute the following statements on the master:

USE db1;

INSERT INTO db2.tbl2 VALUES (1);

The results on the slave vary considerably depending on the binary log format, and may not match initial expectations in either case.

Statement-based replication. The USE statement causes db1 to be the default database. Thus the --replicate-ignore-db option matches, and the INSERT statement is ignored. The table options are not checked.

Row-based replication. The default database has no effect on how the slave reads database options when using row-based replication. Thus, the USE statement makes no difference in how the --replicate-ignore-db option is handled: the database specified by this option does not match the database where the INSERT statement changes data, so the slave proceeds to check the table options. The table specified by --replicate-do-table matches the table to be updated, and the row is inserted.

当binlog-format为statement 时，replicate-do-db等会按照use语句进行过滤，这个时候假如使用类似着这样的操作，user test1;update test2.test2_1等会出现过滤结果跟猜测的不一致的情况发生。