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?
- Binary log filter
- Binlog_do_db
- Binlog_ignore_db
- Replicate filter
- Replicate_do_db
- Replicate_ignore_db
- Replicate_do_table
- Replicate_ignore_table
- Replicate_wild_ignore_table
- Replicate_rewrite_db
- 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
mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000003 | 456 | imdb | | |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec) |
ii) Binlog_ignore_db
By 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
mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000006 | 156 | | imdb | |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec) |
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 the MySQL configuration file, add the
In replica, If you want to replicate the changes made by the specific database, then you can use Replicate_do_db.
replicate_do_db=imdb
mysql> show slave status\G |
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
| mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.168.211 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000006 Read_Master_Log_Pos: 156 Relay_Log_File: relay-log.000015 Relay_Log_Pos: 321 Relay_Master_Log_File: binlog.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: imdb Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: |
iii) Replicate_do_table
In the MySQL configuration file, add the
replicate_do_table= imdb.movies
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.
mysql> show slave status\G; |
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
| mysql> show slave status\G *************************** 1. row *************************** Sl ave_IO_State: Waiting for master to send event |
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%
mysql> show slave status\G |
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%
mysql> show slave status\G |
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
mysql> show slave status\G |
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
mysql> stop slave for channel 'source 2'; |
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.
mydbops.wordpress.com 
Like a bad movie, replication filters never have a good ending in production. Remember to always test before releasing to production, and when you believe your test plan is complete, guess what? Testing never ends
LikeLike