在线添加复制过滤是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: