MySQL主从备库过滤参数分析和测试

测试环境：

GTID的主从复制，主库（9900）——》备库（9909），存在测试库表：

9900_db1库：t1、t2、t3、t4、t5表

9900_db2库：t6、t7、t8、t9、t10表

1、replicate-do-db参数：

–replicate-do-db=name，只同步指定的数据库，可以使用CHANGE REPLICATION FILTER REPLICATE_DO_DB来创建，比如现在只同步9900_db1库，需要在从库执行：

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql> change replication filter replicate_do_db=(9900_db1); //(9900_db1,…)可指定多个

Query OK, 0 rows affected (0.01 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.04 sec)

您还可以写入永久参数文件(如果要指定多个参数，则需要多次指定此参数)。

此时在主库9900_db2库中的表插入数据的话，将不会同步到备库，在9900_db1库中的表插入数据的话，是可以同步到备库的。

但是需要注意的如下：

①该参数不能再group replication架构中使用，因为可能会使组无法达到一致的状态。

②在binlog为statement格式下（row不会有下面问题）：

sql线程会将replicate-do-db限制的数据库为use使用的数据库。

如果在主库执行如下操作，备库将不会复制（不单单是insert，这里只是用insert举例，只要涉及到复制库和未复制库的跨库操作就不会复制）：

mysql> use 9900_db2;

Database changed

mysql> insert into 9900_db1.t1(name) values(‘gg’);

Query OK, 1 row affected (0.63 sec)

产生这种行为的原因是：仅通过语句很难判断是否该复制（比如跨库update和delete多个表），如果没有必要，只检查默认库比检查所有库要快。

③在binlog为row格式下，以下操作不会同步到备库：

mysql> use 9900_db1;

Database changed

mysql> insert into 9900_db2.t6(name) values(‘gg’);

Query OK, 1 row affected (0.50 sec)

这个也很好理解，因为没有同步9900_db2即便跨同步的库也没用。

总之，在statement格式下的binlog，只要涉及到同步库和未同步库的跨库操作就不会同步；在row格式下的binlog只要涉及到未同步库的操作，都不会同步。

④以下操作在statement格式下和row格式下有区别：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1,9900_db2.t6 set 9900_db1.t1.name=’aaa’,9900_db2.t6.name=’bbb’ where 9900_db1.t1.name=’aa’ and 9900_db2.t6.name=’bb’;

Query OK, 2 rows affected (3.23 sec)

Rows matched: 2 Changed: 2 Warnings: 0

使用的是replicate-do-db指定的库，在binlog为statement格式下会同步到备库，如果不是使用的replicate-do-db库的话将不会同步。

使用的是replicate-do-db指定的库，在binlog为row格式下不论是不是使用的replicate-do-db库，都会只同步9900_db1的操作。

总之，修改操作涉及到同步的库和未同步的库的时候，在binlog为statement格式下如果使用的是（这里使用指的是use dbname）同步的库操作都会同步到备库，如果使用未同步的库的话操作不会同步；在row格式的binlog下无论使用的哪个库都只会同步，replicate-do-db指定的库的操作。

2、replicate-ignore-db参数：

–replicate-ignore-db=name，不同步指定的数据库，可以使用CHANGE REPLICATION FILTER REPLICATE_DO_DB来创建，和replicate-do-db一样，不过多解释了。

这里CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(9900_db1);

需要注意的如下：

①该参数不能再group replication架构中使用，因为可能会使组无法达到一致的状态。同replicate-do-db。

②在binlog为statement格式下，只要use的是replicate-ignore-db的数据库那么将不会复制到备库，use的其他数据库是可以复制到备库的，比如如下操作是可以被复制的，因为use的不是replicate-ignore-db库：

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’; //因为这里是显示指定的9900_db1库基于statement格式下不会进行过滤。

Query OK, 1 row affected (1.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

在binlog为row格式下，无论use的是不是ignore的数据库，都不会更新ignore库中的表。

其实和replicate-do-db大同小异只不过这里是不同步的库。

对于上面的④步中的：

如果为statement格式的话，use的是ignore库，那么不会同步ignore库中的表，其他的表可以同步；如果use的不是ignore库，那么都会同步。

如果为row格式的话，无论use的是不是ignore的库，都不会同步ignore库中的表的操作。

3、replicate-do-table参数：

–replicate-do-table=db_name.tbl_name，只同步指定的表，可以使用CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(9900_db1.t1,9900_db2.t6);进行过滤，也可写入到参数文件，如果要过滤多个表的话，需要指定多次该参数。

测试指定的是只同步9900_db1.t1,9900_db2.t6这两个表，replicate-do-table指定该参数后不管use的是哪个数据库都会进行同步（和binlog格式无关），测试如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2; //使用其他数据库也是如此

Database changed

mysql> update 9900_db1.t1 set name=’aa’ where name=’a’;

Query OK, 1 row affected (1.10 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新，也是无论use哪个数据库都会进行更新（不分binlog格式）。当更新操作中既包含db table表又包含不能同步的表时，statement格式时都会同步到备库，但是如果备库不存在其他表的话主从将会报错（与use数据库无关）；row格式的时候备库只同步db table的表（与use数据库无关）。

4、replicate-ignore-table参数：

–replicate-ignore-table=db_name.tbl_name，不同步指定的表，可以使用CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(9900_db1.t1,9900_db2.t6);进行过滤，也可写入到参数文件，如果要过滤多个表的话，需要指定多次该参数。

测试指定的是不同步9900_db1.t1,9900_db2.t6这两个表，replicate-ignore-table指定该参数后不管use的是哪个数据库都不会进行同步，测试如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.17 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新，也是无论use哪个数据库备库都不会进行更新（不分binlog格式）。对于操作中包含既然包含ignore table表又包含可以同步的表的时候，那么在statement格式下replicate-ignore-table指定的表和可以同步的表都不会同步（与use数据库无关）；在row格式下replicate-ignore-table指定的表不同步外其他的表示可以同步的（与use数据库无关）。

5、replicate-wild-do-table参数：

–replicate-wild-do-table=db_name.tbl_name，只同步指定的表，可以包含%和_通配符，它们与LIKE模式匹配操作符的含义相同。可以使用CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘9900_db1.t1′,’9900_db2.t6’)进行过滤(注意库名表名的引号)，也可以写入参数文件如果不使用通配符等的话，想要同步多个表，需要在参数文件写多次。

测试指定的是只同步9900_db1.t1,9900_db2.t6这两个表，replicate-wild-do-table指定该参数后不管use的是哪个数据库都会进行同步（和binlog格式无关），测试如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2; //使用其他数据库也是如此

Database changed

mysql> update 9900_db1.t1 set name=’aa’ where name=’a’;

Query OK, 1 row affected (1.10 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新，也是无论use哪个数据库都会进行更新（不分binlog格式）。当更新操作中既包含db table表又包含不能同步的表时，statement格式时都会同步到备库，但是如果备库不存在其他表的话主从将会报错（与use数据库无关）；row格式的时候备库只同步db table的表（与use数据库无关）。

replicate-wild-do-table和replicate-do-table效果是一样的，不同的是replicate-wild-do-table可以使用通配符等。

该选项适用于表、视图和触发器，不适用于存储过程和函数等事件。

例如：replication -wild-do-table=foo%.Bar%.只复制使用数据库名称以foo开头、表名称以Bar开头的表的更新。如果表名模式是%，那么它将匹配任何表名。

该选项不适用隐式更新表，比如授权语句grant会隐式更新mysql.user表，备库不受该参数限制。

6、replicate-wild-ignore-table参数：

–replicate-wild-ignore-table=db_name.tbl_name，不同步指定的表，可以使用CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=(‘9900_db1.t1′,’9900_db2.t6’);进行过滤，也可写入到参数文件，如果要过滤多个表的话，需要指定多次该参数。

测试指定的是不同步9900_db1.t1,9900_db2.t6这两个表，replicate-wild-ignore-table指定该参数后不管use的是哪个数据库都不会进行同步( 和binlog格式无关)，测试如下：

mysql> use 9900_db1;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.17 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> use 9900_db2;

Database changed

mysql> update 9900_db1.t1 set name=’a’ where name=’aa’;

Query OK, 1 row affected (1.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果像1中的④进行多个更新，也是无论use哪个数据库备库都不会进行更新（和binlog格式无关）。对于操作中包含既然包含ignore table表又包含可以同步的表的时候，那么在statement格式下replicate-wild-ignore-table指定的表和可以同步的表都不会同步（与use数据库无关）；在row格式下replicate-ignore-table指定的表不同步外其他的表示可以同步的（与use数据库无关）。

因为row格式的binlog中将语句拆分了两个：

replicate-wild-ignore-table和replicate-ignore-table效果是一样的，不同的是replicate-wild-ignore-table可以使用通配符等。

例如：replication-wild-ignore-table=foo%.Bar%.不会复制使用数据库名称以foo开头、表名称以Bar开头的表的更新。如果表名模式是%，那么它将匹配任何表名。

该选项不适用隐式更新表，比如授权语句grant会隐式更新mysql.user表，备库不受该参数限制。如果需要过滤掉GRANT语句或其他管理语句，可能的解决方法是使用–replication-ignore-db参数。

例如：

USE nonreplicated;

GRANT SELECT, INSERT ON replicated.t1 TO ‘someuser’@’somehost’;

上面的语句序列会导致GRANT语句被忽略。

7、replicate-rewrite-db：

–replicate-rewrite-db=from_name->to_name将主库的数据库名from_name转换为从库的to_name，要指定多个需要多次使用该参数，或者写入参数文件，例如：

mysqld –replicate-rewrite-db=”olddb->newdb”

也可以通过CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB来创建。

这里使用主库的9900_db1和从库的test：CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((9900_db1,test));

对于binlog为statement和row格式该参数的效果是不同的：

①statement格式下，需改语句中指定库名(比如update dbname.tblname)这样的话无论use的哪个库都不会同步到test库（如果备库也有和主库同名的库9900_db1，那么会同步到该库），如果语句中不指定库名，use 参数指定的主库的库名（也就是9900_db1）的话直接写表名更新是可以同步到备库test库的。

②row格式下，无论use的是哪个库，也无论更新的时候有没有指定库名，都会同步到备库的test库，而如果备库中9900_db1库也不会同步。

DDL的话不论binlog格式如何，都是根据use的库来进行的，也就是说，指定库名(比如update dbname.tblname)这样的话无论use的哪个库都DDL不会同步到test库（如果备库也有和主库同名的库9900_db1，那么会同步到该库），有可能sql thread会报错；如果语句中不指定库名，use 参数指定的主库的库名（也就是9900_db1）的话直接写表名更新是可以同步到备库test库的。

为确保重写产生预期的结果，尤其是与其他复制筛选选项结合使用时，请在使用选项–replicate-rewrite-db时遵循以下建议：

①在源和具有不同名称的副本上手动创建from_name 和 to_name 数据库。

②如果您使用基于语句的或混合的二进制日志记录格式，请不要使用跨数据库查询，也不要在查询中指定数据库名称。对于 DDL 和 DML语句，都依赖use语句指定的当前数据库，并且在查询中仅使用table名

③如果仅对 DDL 语句使用基于row格式的binlog，依靠use语句指定当前数据库，并且在查询中使用table名，对于DML语句，可以根据需要使用完全限定的table名（db.table）。

如果遵循这些 建议，则可以安全的将–replicate-rewrite-db选项与table级复制过滤选项（例如–replicate-do-table）结合使用。

Original: https://www.cnblogs.com/ordinarydba/p/16424501.html
Author: DB文档搬运工
Title: MySQL主从备库过滤参数分析和测试