shutterstock_253451584MySQL 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.

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.

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.

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.

Moreover, multiple, different replication filters can be set in one command and should be separated with a comma as below:

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:

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.

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.

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.

Share this post

Comments (4)

  • aftab

    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.

    November 6, 2015 at 8:48 am
  • aftab

    Remember to add replication filters in mysql options file too, otherwise replication filter settings would be lost after MySQL restart!

    November 16, 2015 at 5:09 am
  • doublemarket

    This is long awaited feature and thank you for the quick summary.
    I translated the post into Japanese since it’s useful;

    Please let me know if it’s problem.

    November 18, 2015 at 9:44 pm
  • Mohammad Musleh

    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?

    January 15, 2019 at 5:30 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.