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

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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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：

?

1 2	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，然后再执行。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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:

　　

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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:

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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文件中那样设置多个相同的过滤规则，如果有多个，只有最后一个会生效。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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: