You can ignore certain tables using the REPLICATE_IGNORE_TABLE and REPLICATE_WILD_IGNORE_TABLE options. The REPLICATE_WILD_IGNORE_TABLE option allows wildcard characters, where as REPLICATE_IGNORE_TABLE only accepts full table names:
mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = ('db1.table1'); mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%'); You can also set filters for a channel by specifying the channel name:
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (d1) FOR CHANNEL 'master-1';
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 in 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
*************************** 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.000005
Read_Master_Log_Pos: 156
Relay_Log_File: relay-log.000011
Relay_Log_Pos: 365
Relay_Master_Log_File: binlog.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: imdb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
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 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
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.000007
Read_Master_Log_Pos: 659
Relay_Log_File: relay-log.000018
Relay_Log_Pos: 868
Relay_Master_Log_File: binlog.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: imdb.movies
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
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 ***************************
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.000007
Read_Master_Log_Pos: 1517
Relay_Log_File: relay-log.000020
Relay_Log_Pos: 893
Relay_Master_Log_File: binlog.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: imdb.actors
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
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
*************************** 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.000007
Read_Master_Log_Pos: 2089
Relay_Log_File: relay-log.000022
Relay_Log_Pos: 893
Relay_Master_Log_File: binlog.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: imdb.movies%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2089
Relay_Log_Space: 1096
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
*************************** 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.000007
Read_Master_Log_Pos: 3730
Relay_Log_File: relay-log.000024
Relay_Log_Pos: 1181
Relay_Master_Log_File: binlog.000007
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: imdb.movies%
Last_Errno: 0
Last_Error:
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
*************************** 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.000007
Read_Master_Log_Pos: 4228
Relay_Log_File: relay-log.000026
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000007
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4228
Relay_Log_Space: 524
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 290a305f-a62f-11eb-8830-080027b81a94
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 290a305f-a62f-11eb-8830-080027b81a94:1-7
Auto_Position: 0
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
mysql> stop slave for channel 'source 2';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(imdb.movies) FOR CHANNEL 'source 2';
Query OK, 0 rows affected (0.00 sec)
mysql> start slave for channel 'source 2';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status for channel 'source 2'\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.168.198
Master_User: repl_user2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 4073
Relay_Log_File:
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: imdb.movies
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4073
Relay_Log_Space: 702
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 1c94c9b1-c117-11eb-937d-080027c46d28
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 290a305f-a62f-11eb-8830-080027b81a94:1-7
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: source 2
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
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.
