关于mysql 从库过滤的一些测试

关于mysql 从库过滤的一些测试我们来进行mysql库过滤的相关参数的测试现在主从准备两个test的数据库root@localhost:(none)01:34:48> show databases;+--------------------+| Database           |+--------------------+| information_schema || kiwi               || mysql              || oss                || performance_schema || test               || test2              |+--------------------+7 rows in set (0.00 sec)1 测试 replicate_do_db我们在从库的配置文件中添加 replicate_do_db=test主库执行root@localhost:(none)01:50:32> use test;Database changedroot@localhost:test01:52:53> create table t1 (id int(10));Query OK, 0 rows affected (0.03 sec)root@localhost:test01:53:34> desc test.t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(10) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)在从库查看root@localhost:(none)10:20:37> desc test.t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(10) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)换到test2库上面主库执行root@localhost:test201:54:03>  create table t1 (id int(10));Query OK, 0 rows affected (0.02 sec)root@localhost:test201:54:08> desc test2.t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(10) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)从库执行root@localhost:(none)10:21:52> desc test2.t1;ERROR 1146 (42S02): Table 'test2.t1' doesn't exist证明在从库除了test以外的其他的都被过滤掉了2 测试 replicate_ignore_db从库配置文件添加replicate_ignore_db     =test主库上使用数据库test2root@localhost:test02:00:51> use test2;Database changedroot@localhost:test202:04:24> create table t1 (id int(10));Query OK, 0 rows affected (0.02 sec)root@localhost:test02:07:57> desc test2.t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(10) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)查看从库的test2数据库root@localhost:(none)10:31:46> desc test2.t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(10) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)主库上使用数据库testroot@localhost:test202:05:21> use test;Database changedroot@localhost:test02:07:49>  create table t1 (id int(10));Query OK, 0 rows affected (0.01 sec)root@localhost:test02:09:13> desc test.t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(10) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)从库上进行查看root@localhost:(none)10:33:33> desc test.t1;ERROR 1146 (42S02): Table 'test.t1' doesn't exist证明忽略了从库test上进行的复制3 测试replicte_do_table在从库的参数文件中添加replicate_do_table      =test.t1在主库上面执行root@localhost:test02:26:05> insert into test.t1 values (10);Query OK, 1 row affected (0.00 sec)root@localhost:test02:33:54> select * from test.t1;+------+| id   |+------+|   10 |+------+1 row in set (0.00 sec)在从库上面查看root@localhost:(none)11:02:12> select * from test.t1;+------+| id   |+------+|   10 |+------+1 row in set (0.00 sec)root@localhost:test02:34:04> use test;Database changedroot@localhost:test02:36:25> create table t2 (id int(10));Query OK, 0 rows affected (0.01 sec)root@localhost:test02:36:42> desc t2;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(10) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)从库上面查看root@localhost:test11:04:16> desc t2;ERROR 1146 (42S02): Table 'test.t2' doesn't exist我们在往test2的t1表中插入数据root@localhost:test202:42:55> insert into t1 values(10);Query OK, 1 row affected (0.00 sec)root@localhost:test202:43:05> select * from test2.t1;+------+| id   |+------+|   10 |+------+1 row in set (0.00 sec)在从库上面查看root@localhost:test11:04:16> desc t2;ERROR 1146 (42S02): Table 'test.t2' doesn't existroot@localhost:test11:04:56> select * from test2.t1;Empty set (0.00 sec)由此可见，replicate_do_table 只应用指定的表的相关的查询语句4 测试replicate_ignore_table在从库的配置文件中添加replicate_ignore_table  =test.t1root@localhost:test03:05:12> select * from t1;+------+| id   |+------+|   10 ||   20 ||   30 |+------+3 rows in set (0.00 sec)root@localhost:test03:05:42> insert into test.t1 values(50);Query OK, 1 row affected (0.00 sec)root@localhost:test03:07:53> select * from t1;+------+| id   |+------+|   10 ||   20 ||   30 ||   50 |+------+4 rows in set (0.00 sec)root@localhost:test03:08:02> insert into t1 values (60);Query OK, 1 row affected (0.00 sec)root@localhost:test03:08:35> select * from t1;+------+| id   |+------+|   10 ||   20 ||   30 ||   50 ||   60 |+------+在从库查看root@localhost:test11:36:14> select * from t1;+------+| id   |+------+|   10 ||   20 ||   30 |+------+3 rows in set (0.00 sec)数据没用应用过来，查看其他的表root@localhost:test03:10:35> select * from t2;+------+| id   |+------+|   10 |+------+1 row in set (0.00 sec)root@localhost:test03:10:42> insert into t2 values (20);Query OK, 1 row affected (0.00 sec)root@localhost:test03:11:18> select * from t2;+------+| id   |+------+|   10 ||   20 |+------+2 rows in set (0.00 sec)在从库查看root@localhost:test11:36:40> select * from t2;+------+| id   |+------+|   10 ||   20 |+------+2 rows in set (0.00 sec)可见只忽略那一张表的相关内容，其他的内容照常复制5 replicate_wild_do_table，replicate_wild_ignore_table  可以进行通配符匹配的执行只复制或者忽略复制一些表的内容  我们在从库配置文件中设置  replicate_wild_ignore_table=test.%  主库上我们进行插入操作  root@localhost:test03:13:53> insert into t1 values(20);Query OK, 1 row affected (0.00 sec)root@localhost:test03:20:49> select * from t1;+------+| id   |+------+|   20 |+------+1 row in set (0.00 sec)root@localhost:test03:20:58> select * from t2;+------+| id   |+------+|   10 ||   20 |+------+2 rows in set (0.00 sec)root@localhost:test03:22:23> insert into t2 values (30);Query OK, 1 row affected (0.00 sec)root@localhost:test03:22:50>  select * from t2;+------+| id   |+------+|   10 ||   20 ||   30 |+------+3 rows in set (0.00 sec)我们在从库上面进行查询root@localhost:test11:49:10> select * from t1;Empty set (0.00 sec)root@localhost:test11:49:15> select * from t2;+------+| id   |+------+|   10 ||   20 |+------+2 rows in set (0.00 sec)root@localhost:test11:50:35>  select * from t2;+------+| id   |+------+|   10 ||   20 |+------+2 rows in set (0.00 sec)发现这些都没有被反馈到从库上面来

在测试的我们发现有这样的问题

我们在从库上设置了 Replicate_Ignore_DB: test

在主库 我们在test2库更新test的时候，发现从库还是应用了复制

主库root@localhost:test2:03:44:36> select * from test.t1;+------+| id   |+------+|   10 |+------+1 row in set (0.00 sec)root@localhost:test2:03:44:44> insert into test.t1 values(20);Query OK, 1 row affected (0.00 sec)root@localhost:test2:03:45:44> select * from test.t1;+------+| id   |+------+|   10 ||   20 |+------+2 rows in set (0.00 sec)备库root@localhost:(none):12:13:30> select * from test.t1;+------+| id   |+------+|   10 ||   20 |+------+2 rows in set (0.00 sec)还是有更新了原因是设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句被忽略。可以使用replicate_wild_do_table和replicate_wild_ignore_table来代替如replicate_wild_do_table=test.%或replicate_wild_ignore_table=mysql.%这样就可以避免出现上述问题了