Replication filters allow users to configure replicas to intentionally skip certain events.

Replication filters are also used to selectively exclude certain databases, tables, or statements from being replicated to a replica server. This can be useful in situations where you have a large database with some tables that are not needed on the replica or where certain statements should be excluded from replication for security reasons.

There are two types of replication filters in MariaDB/MySQL:

• Database-level replication filters
• Table-level replication filters

Database-level replication filters:

Database-level replication filters: These filters apply to entire databases and are specified using the Replicate-do-db and replicate-ignore-db configuration options. The former specifies which databases should be replicated, while the latter specifies which databases should be ignored.

Replicate-do-db is a configuration option in MariaDB/MySQL used to specify which databases should be replicated to a replica server in a replication setup.When this option is used, only the specified databases are replicated to the replica server. Any changes made to tables in other databases are not replicated.

Replicate-ignore-DB is a configuration option in MariaDB/MySQL that can be used in a replication setup to specify which databases should not be replicated to a replica server.

Syntax:

SET GLOBAL Replicate_Do_DB=database_name

SET GLOBAL Replicate_Ignore_DB=database_name

Table-level replication filters:

Table-level replication is a feature in MariaDB/MySQL that allows you to selectively replicate only certain tables from a database to a replica server in a replication setup.

Replicate-do-table is a configuration option in MariaDB/MySQL that allows you to selectively replicate specific tables from a database to a replica server in a replication setup.

Replicate-ignore-Table MariaDB/MySQL also provides the replicate-ignore-option, which can be used to exclude specific tables from replication.

Syntax:

SET GLOBAL Replicate_Do_TABLE=db_name.table_name

SET GLOBAL Replicate_Ignore_TABLE=db_name.table_name

Note: Be careful when using replicate-do-db It can cause differences between the source and replica servers. If a table is moved to another database, it might still replicate and create problems.
In this Scenario we Have 1 source and 3 replica of Different Configuration.

Summary:
We have three databases: World, Sakila, and Managerie.

• On Replica 1, we want to replicate only the World and Managerie databases.
• On Replica 2, we want to replicate only the Sakila and Managerie databases.
• On Replica 3, we want to replicate all databases, exactly the same as on the master server.

Even after using DB-replication Filters , some functionalities are not yet applied.

For ex: Here I used DB Managerie , in that I created a table 5G for world DB.

So, when Show Tables comments are executed for World DB in Replica-Servers where Ignore-DB filters are set, it should not show the tables from World DB, but unfortunately show the tables from World DB that are ignored, so i decided to use replicate_wild_do_table or replicate_wild_Ignore_table.

Syntax:

SET GLOBAL replicate_wild_do_table=db_name.tbl_prefix%.

SET GLOBAL replicate_wild_Ignore_table=db_name.tbl_prefix%.

Here I used Managerie DB and Created Table for World DB in Source, I’m Ignored Sakila DB in replica1 and For replica2 I Ignored World DB.

Replica 1:

Here I allowed World and Managerie DB’s using Both DB-level and Table-level filters and Ignored Sakila DB (DB & Table Level) , as Said Earlier tables are replicated for Ignored-DB’s until we Use Wild_Ignore.

OUTPUT:

When executing the SHOW TABLES command for the World database on replica Server 1, the 5G table appears.
This is because we had previously configured the replicate-wild-do-table filter on replica 1 to specifically allow replication of tables from the World database.As a result, replication filters are applied, the 5G table from the world database is correctly replicated and visible on replica server 1.

DO:

SET GLOBAL Replicate_Wild_Do_Table=’world.%,Managerie.%’;

IGNORE:

SET GLOBAL Replicate_Wild_Ignore_Table=’sakila.%’;

• In this.% is a pattern that matches any database name or any table name in a database, respectively.
• “wild” usually refers to the use of wildcard characters to match a pattern of database or table names.
  

Replica 2:

Here I allowed Sakila and Managerie DB’s using Both DB-level and Table-level filters and Ignored World DB (DB & Table Level) , as Said Earlier tables are not replicated for Ignored-DB’s until we Use Wild_Ignore.

OUTPUT:

When we See the Show_tables for world DB command in Replica Server 2 It never shows the Table 5G , Because I already set the WILD_IGNORE(Table Level) filter for World DB in Replica-2 mentioned earlier so it didn’t show the Table 5G.

DO:

SET GLOBAL Replicate_Wild_Do_Table=’sakila.%,Managerie.%’;

IGNORE:

SET GLOBAL Replicate_Wild_Ignore_Table=’world.%’;

Replica 3:

For Replica 3, no replicate-wild-do-table or replicate-wild-ignore-table filters were configured. As a result, it replicates all databases from the master server without any restrictions.

Conclusion:

Replication filters enable you to manage which data is copied, improving the effectiveness and targeting of replication. Make careful use of them to prevent irregularities and maintain a dependable and tidy setup.This improves replication performance, reduces needless data transfer, and makes disaster recovery configurations easier. To prevent misconfigurations, it’s crucial to comprehend the capabilities and actions of each filter type, particularly the distinctions between statement-based and row-based replication. Maintaining data integrity and operational reliability requires careful planning and testing of your replication filter configuration.