MySQL复制过滤测试

发表于 | 分类于 | | 阅读次数:
测试MySQL复制过滤的几个参数

版本和配置

MySQL: 5.7.22-log
binlog-format=ROW
slave_exec_mode=IDEMPOTENT
Master-Slave基于GTID的主从复制
Master-Slave实例包含库test_c、test_b、test_c

复制过滤参数

1
2
3
4
5
6
--replicate-do-db
--replicate-ignore-db
--replicate-do-table
--replicate-wild-do-table
--replicate-ignore-table
--replicate-wild-ignore-table

参数说明可参考：http://dp.imysql.com:8080/node/58

Database-Level Replication 流程图

Table-Level Replication 流程图

测试

库级过滤

Slave配置过滤规则：replicate-do-db=test_a

DDL

CREATE/DROP TABLE

Master执行

1
2
3
4
5
6
7
8
9
10
11
12
mysql> use test_b;
Database changed
mysql> create table test_a.t (pk int primary key);
Query OK, 0 rows affected (0.03 sec)

mysql> use test_a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

Slave复制报错

1
2
Last_SQL_Errno: 1051
Last_SQL_Error: Error 'Unknown table 'test_a.t'' on query. Default database: 'test_a'. Query: 'DROP TABLE `t` /* generated by server */'

查看master binlog信息

1
2
3
4
5
6
7
8
9
10
mysql> show binlog events in 'mysql-bin.000004' from 1340;
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                                                               |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000004 | 1340 | Gtid       |         1 |        1405 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:40' |
| mysql-bin.000004 | 1405 | Query      |         1 |        1525 | use `test_b`; create table test_a.t (pk int primary key)           |
| mysql-bin.000004 | 1525 | Gtid       |         1 |        1590 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:41' |
| mysql-bin.000004 | 1590 | Query      |         1 |        1708 | use `test_a`; DROP TABLE `t` /* generated by server */             |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
4 rows in set (0.01 sec)

ALTER TABLE

Master执行

1
2
3
4
5
mysql> use test_b;
Database changed    
mysql> alter table test_a.t add v varchar(10) not null default '';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Slave复制正常
查看slave binlog信息

1
2
3
4
5
6
7
mysql> show binlog events in 'mysql-bin.000012' from 2301;
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                                                                     |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------+
| mysql-bin.000012 | 2301 | Query      |         1 |        2437 | use `test_b`; alter table test_a.t add v varchar(10) not null default '' |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------+
1 row in set (0.01 sec)

DML

Master执行

1
2
3
4
5
6
7
mysql> use test_b;
Database changed
mysql> 
mysql> insert into test_a.t values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t values(8);
Query OK, 1 row affected (0.00 sec)

Slave复制正常

查看master binlog信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show binlog events in 'mysql-bin.000004' from 1893;
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                                                               |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000004 | 1893 | Gtid       |         1 |        1958 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:43' |
| mysql-bin.000004 | 1958 | Query      |         1 |        2032 | BEGIN                                                              |
| mysql-bin.000004 | 2032 | Table_map  |         1 |        2078 | table_id: 114 (test_a.t)                                           |
| mysql-bin.000004 | 2078 | Write_rows |         1 |        2118 | table_id: 114 flags: STMT_END_F                                    |
| mysql-bin.000004 | 2118 | Xid        |         1 |        2149 | COMMIT /* xid=87 */                                                |
| mysql-bin.000004 | 2149 | Gtid       |         1 |        2214 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:44' |
| mysql-bin.000004 | 2214 | Query      |         1 |        2288 | BEGIN                                                              |
| mysql-bin.000004 | 2288 | Table_map  |         1 |        2334 | table_id: 108 (test_b.t)                                           |
| mysql-bin.000004 | 2334 | Write_rows |         1 |        2374 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000004 | 2374 | Xid        |         1 |        2405 | COMMIT /* xid=89 */                                                |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
10 rows in set (0.00 sec)

表级过滤

Slave配置过滤规则：replicate-do-table=test_a.t

DDL

CREATE/DROP TABLE

Master执行

1
2
3
4
5
6
7
8
9
10
11
12
mysql> use test_b;
Database changed
mysql> create table test_a.t (pk int primary key);
Query OK, 0 rows affected (0.03 sec)

mysql> use test_a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

Slave复制正常

查看Slave的binlog信息

1
2
3
4
5
6
7
8
9
10
mysql> mysql> show binlog events in 'mysql-bin.000012' from 1683;
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type | Server_id | End_log_pos | Info                                                               |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000012 | 1683 | Gtid       |         1 |        1748 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:52' |
| mysql-bin.000012 | 1748 | Query      |         1 |        1868 | use `test_b`; create table test_a.t (pk int primary key)           |
| mysql-bin.000012 | 1868 | Gtid       |         1 |        1933 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:53' |
| mysql-bin.000012 | 1933 | Query      |         1 |        2051 | use `test_a`; DROP TABLE `t` /* generated by server */             |
+------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+
4 rows in set (0.01 sec)

ALTER TABLE

Master执行

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> use test_b
Database changed
mysql> alter table test_a.t add v varchar(10) not null default '';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> use test_a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table test_a.t drop v;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Slave复制正常

查看Slave的binlog信息

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show binlog events in 'mysql-bin.000012';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| mysql-bin.000012 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.22-log, Binlog ver: 4                                               |
| mysql-bin.000012 | 123 | Previous_gtids |         2 |         234 | 82b862a0-ee1f-11e8-b241-0242ac150003:1-8,
82ba33f9-ee1f-11e8-b4e5-0242ac150002:1-44 |
| mysql-bin.000012 | 234 | Gtid           |         1 |         299 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:45'                  |
| mysql-bin.000012 | 299 | Query          |         1 |         435 | use `test_b`; alter table test_a.t add v varchar(10) not null default ''            |
| mysql-bin.000012 | 435 | Gtid           |         1 |         500 | SET @@SESSION.GTID_NEXT= '82ba33f9-ee1f-11e8-b4e5-0242ac150002:46'                  |
| mysql-bin.000012 | 500 | Query          |         1 |         605 | use `test_a`; alter table test_a.t drop v                                           |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

DML

Master执行

1
2
3
4
5
6
7
8
9
10
11
mysql> use test_b
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into test_a.t values(10);        
Query OK, 1 row affected (0.01 sec)

mysql> update t t1,test_a.t t2 set t2.v=t1.pk;    
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Slave复制正常

库表级规则混用

组合多，仅列举一种：do-db & do-table
Slave配置过滤规则：
replicate-do-db=test_a
replicate-do-table=test_a.t

DDL

CREATE/DROP TABLE

Master执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> use test_b
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test_a.t (pk int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> use test_a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

Slave复制报错

1
2
Last_SQL_Errno: 1051
Last_SQL_Error: Error 'Unknown table 'test_a.t'' on query. Default database: 'test_a'. Query: 'DROP TABLE `t` /* generated by server */'

ALTER TABLE

Master执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> use test_b
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table test_a.t add v varchar(10) not null default '';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> use test_a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table test_a.t drop v;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Slave复制报错

1
2
Last_SQL_Errno: 1091
               Last_SQL_Error: Error 'Can't DROP 'v'; check that column/key exists' on query. Default database: 'test_a'. Query: 'alter table test_a.t drop v'

DML

Master执行

1
2
3
4
5
6
7
8
9
10
mysql> use test_b
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into test_a.t values(10);
Query OK, 1 row affected (0.01 sec)

mysql> delete from test_a.t where pk=10;
Query OK, 1 row affected (0.00 sec)

Slave复制正常

结论

  • 当BINLOG以ROW格式记录执行语句时，复制过滤允许跨库执行语句，不以当前默认数据库（用USE语句指定）为主，当BINLOG以STATEMENT格式记录执行语句时，复制过滤以当前默认数据库（用USE语句指定）为主（不允许跨库），可参考上面两次DDL测试，注意：注只有DML语句可以使用ROW格式记录，DDL语句始终以STATEMENT形式记录
  • 库表级别过滤混合使用时，当库级别检测不符合时会直接返回，不会再走到表级别判断，因此建议使用–replicate-wild-do-table=test_a.%来替换–replicate-do-db=test_a做库级过滤，可防止跨库的DDL(CREATE)操作导致Slave复制报错

参考

  1：Mysql 复制过滤详解
  2：MySQL主从复制过滤规则应用