关于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 changed
root@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
主库上使用数据库test2
root@localhost:test02:00:51> use test2;
Database changed
root@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)
主库上使用数据库test
root@localhost:test202:05:21> use test;
Database changed
root@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 changed
root@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 exist
root@localhost:test11:04:56> select * from test2.t1;
Empty set (0.00 sec)
由此可见，replicate_do_table 只应用指定的表的相关的查询语句

4 测试replicate_ignore_table
在从库的配置文件中添加
replicate_ignore_table  =test.t1
root@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.%
这样就可以避免出现上述问题了