1. What are Replication Filters in MySQL?
2. Replication Filter types in MySQL?
   1. Binary log Filter
      1. i) Binlog_do_db
      2. ii) Binlog_ignore_db
   2. Replicate Filter
      1. i) Replicate_do_db
      2. ii) Replicate_ignore_db
      3. iii) Replicate_do_table
      4. iv) Replicate_ignore_table
      5. v) Replicate_wild_do_table
      6. vi) Replicate_wild_ignore_table
      7. vii) Replicate_rewrite_db
   3. Multi-source replication Filter

What are Replication Filters in MySQL?A replication filter is used to filter out the necessary databases and tables that will be replicated in the replica.The replication filter can be set either in my. cnf or the command line.The change made in those databases will be logged in binary log files. The filter can be created in both source (using –binlog-* option)  and replica (using –replicate-* option)Flow Diagram Of Replication Filter

Replication Filter types in MySQL?

1. Binary log filter
   • Binlog_do_db
   • Binlog_ignore_db
2. Replicate filter
   • Replicate_do_db
   • Replicate_ignore_db
   • Replicate_do_table
   • Replicate_ignore_table
   • Replicate_wild_ignore_table
   • Replicate_rewrite_db
3. Multi-Source replication filter

Binary log Filter      In the source server, Binary log filters are used to filter out the writes inside the binlog files. By mentioning the filters, we can control the writes to binlog files, which will be simultaneously replicated in the replica server as well.

‍i) Binlog_do_db        By using the Binlog_do_db variable, the binary log will only log the changes made by the mentioned database. To specify more than one database, use this option multiple times, once for each database. In my. cnf file add the following line.

In the MySQL configuration file, add the

 

binlog_do_db=imdb
	

‍ii) Binlog_ignore_dbBy using the Binlog_ignore_db variable, the binary log will ignore the changes made by the mentioned database. Changes made by those databases will not be logged in binlog files.

In the MySQL configuration file, add the

 
binlog_ignore_db=imdb
	

Replicate Filter

      Replication filter will be applied in the replica server, where you can restrict the replicating process in the replica using this replicating filter.

i) Replicate_do_db

In replica, If you want to replicate the changes made by the specific database, then you can use Replicate_do_db.

In the MySQL configuration file, add the

 
replicate_do_db=imdb
	

ii) Replicate_ignore_db

In the replica server, If you want to ignore the changes made by the specific database then we can use the Replicate_ignore_db variable. This variable will replicate the changes made by all the other databases, except the specific one.

In the MySQL configuration file, add the

 
replicate_ignore_db=imdb
	

iii) Replicate_do_table

In replica, If you want to replicate the changes made by the specific table inside a database, then you can use the Replicate_do_table variable.

In the MySQL configuration file, add the

 

replicate_do_table= imdb.movies
	

iv) Replicate_ignore_table

In the replica server, If you want to ignore the changes made by a specific table inside a database, then you can use Replicate_ignore_table. This variable will replicate changes of all other tables in a database, except the mentioned one.

In the MySQL configuration file, add the

 

replicate_Ignore_Table= imdb.actors
	

v) Replicate_wild_do_table

The replica threads will be restricted to replicating tables that match the specified wildcard pattern. Patterns can contain the % and _ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator.

In the MySQL configuration file, add the following

 

replicate_wild_do_table= imdb.movies%
	

vi) Replicate_wild_ignore_table

The replica threads will be prohibited from replicating tables that match the specified wildcard pattern. Replica will not replicate the changes made by those tables that match the mentioned wildcard pattern.

In the MySQL configuration file, add the

 
replicate_wild_ignore_table= imdb.movies%
	

vii) Replicate_rewrite_db

The replica to create a replication filter that translates the default database to to_name if it was from_name on the source. Only statements involving tables are affected if from_name is the default database on the source.

In the MySQL configuration file, add the

 

replicate_rewrite_db=imdb->imdb_full
	

Multi-source replication Filter

In Multi-Source replication, a replica will have more than one source. Each channel will be created for every source. We can filter the replication process in replicas by applying the channel-based replication filters. If the identical database or table is present on multiple sources, and the replica wants to replicate it from one source, then a channel based replication filter helps a lot.

We have two source -source 1, source 2, and one replica

The following is an example of setting a channel-based replication filter for a specific channel. Here we have applied the filter for the channel – source 2. Here the selected database is imdb and the table chosen is movies.

In the MySQL configuration file, add the

 
replicate_do_table=’source 2’:imdb.movies
	

Hope, this blog help you to know in detail about MySQL replication filters in details. For further reference on filters, use the ref manual here.

Replicate with Confidence! Mydbops offers comprehensive MySQL database management services to help you master replication filters and optimize your environment. Contact us today and unlock the full potential of your MySQL replication strategy!

{{cta}}