MySQL 5.7 has a lot of enhancements and new features. I summarized this list previously in this blog post.
Adding replication filters online is one of the MySQL 5.7 features described in this manual. However, I will describe and summarize a few examples in this blog post.
Filtering replication events is also known as Partial Replication. Partial replication can be done from the master or slave. Filtering events on the master server via binlog-do-db and binlog-ignore-db is not a good idea, as explained in this post. However, if you really need it, partial replication on the slave is a better option. Our CEO Peter Zaitsev wrote a detailed blog post some time back on filtered MySQL replication that you may find useful.
Partial replication works differently for statement-based and row-based replication. You can find details in the manual and on this manual page. It is useful to know how MySQL evaluates partial replication rules.
Prior to MySQL 5.7, adding/changing replication rules required bouncing the MySQL server. In MySQL 5.7 adding/changing replication filter rules becomes an online operation without restarting MySQL server, using the CHANGE REPLICATION FILTER command. Let me share a few examples:
Currently, the slave server runs without filtered replication and this can be verified by the slave status and the last five variables. Replicate_* has an empty value, meaning no replication filter rules are set.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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: . |
The master database server contains db1-db4. Let’s replicate only db1 and db2 out of the four databases. This can be done with the help of replicate-do-db option.
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 |
This error is CHANGE REPLICATION FILTER is not supported on a running slave. We have to stop the SQL slave thread first and re-run the command to set replicate-do-db option. Checking the slave status confirmed that the slave now only replicates db1 and db2 out of the four databases from the master.
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.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: . |
In order to remove that filter, you need an empty value for the filter name; i.e., replicate-do-db as below. The slave status verified that there are no replication filters set against the replicate-do-db variable.
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 30 | 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: . |
Moreover, multiple, different replication filters can be set in one command and should be separated with a comma as below:
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 30 31 | 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% . |
The slave status verifies that there are a couple of replication filters set where db1.db1_new replicates binary log events to slave, which ignores replication events on the slave for db1.db1_old table(s) as per Replicate_Wild_Ignore_Table filter. Also, if the database or table name doesn’t contain any special characters, then it’s not necessary to be quoted as a value for the filter. However, Replicate_Wild_Do_Table and Replicate_Wild_Ignore_Table are string expressions and may contain wild cards, so they must be quoted.
With the CHANGE REPLICATION FILTER command, you can’t set the same filtering rule multiple times, as opposed to behavior where you can set multiple filters for the same rule in my.cnf file by specifying it multiple times. With the CHANGE REPLICATION FILTER command, if you try to set multiple filters for the same rule then only the last rule will be activated and all above rules will be ignored as illustrated in the following example:
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 30 31 32 33 34 | 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: . |
As you can see, db2.db2_tbl1 table is ignored and only the last rule for db2.db2_tbl2 table is activated.
As I mentioned before, to unset filters of any given type you need to set that particular filter to an empty value. The below example will unset Replicate_Wild_Do_Table filter.
1 | mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = (); |
However, you may set multiple filters in one command by separating each rule with a comma as in the previous example with Replicate_Do_DB. Let’s set multiple rules for the Replicate_Wild_Do_Table option via the CHANGE REPLICATION FILTER command.
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 30 31 32 33 | mysql> STOP SLAVE SQL_THREAD; Query OK, 0 rows affected (0.03 sec) mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.db2_tbl1%','db2.db2_tbl2%'); 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.000008 Read_Master_Log_Pos: 154 Relay_Log_File: centos59-relay-bin.000013 Relay_Log_Pos: 369 Relay_Master_Log_File: master-bin.000008 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_tbl1%,db2.db2_tbl2% Replicate_Wild_Ignore_Table: Last_Errno: 0 . . |
Conclusion:
Partial replication is not a great solution in most cases. All of the replicate options replicate-do-table, replicate-ignore-table, replicate-wild-do-table and replicate-wild-ignore-table work differently. You need to use the default database in order to work filtering normally and it behaves differently with a different binlog format. Filters other than replicate-wild-do-table and replicate-wild-ignore-table might not work as expected and events with wild% filters stored procedures and stored functions may be inconsistent.
About the Author
Muhammad IrfanMuhammad Irfan is vastly experienced in LAMP Stack. Prior to joining Percona Support, he worked in the role of MySQL DBA & LAMP Administrator, maintained high traffic websites, and worked as a Consultant. His professional interests focus on MySQL scalability and on performance optimization.
Nice blog!
Just to add small point, to make it easier to determine what effect an option set will have, it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options.
Remember to add replication filters in mysql options file too, otherwise replication filter settings would be lost after MySQL restart!
This is long awaited feature and thank you for the quick summary.
I translated the post into Japanese since it’s useful;
https://yakst.com/ja/posts/3486
Please let me know if it’s problem.
Hi Muhammad;
I have master-slave replication in place utilizing GTID approach; the use case we have is to configure this replication such that some records that exist in some tables on the master, which meet specific criteria do not get replicated to the salve; is this possible?