【MySQL】MySQL 5.7中过滤复制和部分复制的变化

在线添加复制过滤是5.7引入的新特性，使用change replication filter语句。在5.7之前，增加、修改复制规则需要重启mysql。

当前复制是没有开启过滤复制的：

mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 351
Relay_Log_File: centos59-relay-bin.000003
Relay_Log_Pos: 566
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

 

主库包含db1-db4。现在我们只想复制db1、db2：

mysql> change replication filter replicate_do_db = (db1, db2);
error 3017 (hy000): this operation cannot be performed with a running slave sql thread; run stop slave sql_thread first

 

需要先停止sql thread，然后再执行。

mysql> stop slave sql_thread;
query ok, 0 rows affected (0.00 sec)
mysql> change replication filter replicate_do_db = (db1, db2);
query ok, 0 rows affected (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 505
Relay_Log_File: centos59-relay-bin.000003
Relay_Log_Pos: 720
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: db1,db2
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

　　

去除过滤规则，需要给过滤器一个空的名字：

mysql> stop slave sql_thread;
query ok, 0 rows affected (0.03 sec)
mysql> change replication filter replicate_do_db = ();
query ok, 0 rows affected (0.00 sec)
mysql> start slave sql_thread;
query ok, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1629
Relay_Log_File: centos59-relay-bin.000003
Relay_Log_Pos: 1844
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

可以同时设置多个复制过滤规则，多个规则用逗号分割：

mysql> stop slave sql_thread;
query ok, 0 rows affected (0.03 sec)
mysql> change replication filter
replicate_wild_do_table = ('db1.db1_new%'),
replicate_wild_ignore_table = ('db1.db1_old%');
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 448
Relay_Log_File: centos59-relay-bin.000006
Relay_Log_Pos: 663
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db1.db1_new%
Replicate_Wild_Ignore_Table: db1.db1_old1%

　　

change replication filter命令不能像在my.cnf文件中那样设置多个相同的过滤规则，如果有多个，只有最后一个会生效。

mysql> select * from db1.db1_old;
empty set (0.00 sec)
mysql> stop slave sql_thread;
query ok, 0 rows affected (0.03 sec)
mysql> change replication filter
replicate_wild_do_table = ('db2.db2_tbl1%'),
replicate_wild_do_table = ('db2.db2_tbl2%');
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 980
Relay_Log_File: centos59-relay-bin.000006
Relay_Log_Pos: 1195
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db2.db2_tbl2%
Replicate_Wild_Ignore_Table: