Tags

, , , ,

Multi-Source Replication is one of the awesome features in MySQL 5.7 which allows the slave to get the replication streams from multiple masters by having a replication channel for each master.
But what about the replication filters? Can we set replication filters per channel in the multi-source replication?

In brief, replication filtering is a way used to eliminate part of the master’s events (not) to be replicated to the slaves (Also known as Partial Replication). This can be done on either master (using --binlog-do-db and --binlog-ignore-db options) or slaves (using --replicate-* options) while it is not recommended on the master anyway at least to provide Point in Time Recovery (PiTR).

Worth mentioning here that changing the replication filters online is yet another very nice improvement in MySQL 5.7 as well in which we have to stop only the SLAVE SQL_THREAD – instead of restarting the whole MySQL service – to change the replication filters by using “CHANGE REPLICATION FILTER” statement.

Back to the main question, can we set replication filters per channel in MySQL Multi-Source Replication?
Sorry to say that but – at least for now – the answer is no!

Update: Replication filters per channel is now implemented in MySQL starting from version 8.0.1. Check this post for more details.

Case Scenario

In the middle of a migration process, it was required to setup a slave for two masters, both masters have a common DB, same name, same structure, same tables and approximately same data but the slave should replicate this database from only one of them!
MySQL Replication Filters are global , or in other words, will be applied for all running replication channels, which means either we replicate that database from both servers or ignore it from both. If we can add a replication filter per channel (like adding the FOR CHANNEL keyword to CHANGE REPLICATION FILTER statement), we would have solved such problems.
I’ve filed a feature request to track this issue if you’re interested.

You may have your own case scenarios where you need to set replication filters differently for each replication channel.

Workaround!!

Before getting this feature implemented in MySQL – if it will be – in such situations, one can do one of the followings:

  1. Use a MariaDB 10.x slave to replicate from multi MySQL masters as the feature is already implemented in MariaDB by executing SET GLOBAL channel/connection_name.replicate_ignore_db='db_name'; (At least, the SLAVE SQL_THREAD must be stopped before executing the previous command).
  2. Change the DB names in a way to make them unique among all masters in which replication filters can control which one should be replicated/ignored on the slave.
  3. Accept replicating the DB from both which might not match the main purpose plus the possibility of breaking the replication due to conflict as multi-source replication does not implement any conflict detection or resolution when applying the transactions.
  4. Ignore replicating that DB from both servers  🙂

Other solutions or workarounds are welcome!

Finally, MySQL 5.7 has great features and improvements but as many improvements implemented in MySQL as more challenges appear in the road!

Update: Replication filters per channel is now implemented in MySQL starting from version 8.0.1. Check this post for more details.

Advertisements