Using Replication Filters Deliberately: Replicating Only What Matters

1. Why replication filters: reporting replicas and multi-store setups

A full replica by default replicates every database and every table from the source, regardless of whether the target system actually needs that data at all. For a reporting system that only accesses aggregated order and product data, that means unnecessarily transferred data volume and, far more critically, an unnecessary copy of sensitive tables such as customer addresses or payment data sitting on a system that has no real need to access them.

Replication filters address exactly this: they let you define, at the database or table level, which parts of the binlog actually get applied on a given replica. For multi-store environments with a separate database per tenant, this opens up a second, equally practical use case: a replica can be limited to just a single tenant's database, for example for a dedicated, tenant-specific analytics or support server.

2. Statement-based filters at the instance level: replicate-do-db and replicate-ignore-db

The simplest filter level operates on entire databases: replicate-do-db limits replication to explicitly named databases, replicate-ignore-db excludes named databases and replicates everything else. Under statement-based replication, the server evaluates the currently active database set via USE, not the tables actually touched inside the statement.

This exact evaluation logic is a common source of trouble with cross-database statements: an UPDATE reporting_db.summary SET ... WHERE EXISTS (SELECT 1 FROM shop_db.orders ...) gets filtered purely based on the database active via USE, even when the statement itself touches a different, supposedly excluded database. Anyone working with cross-database queries like this should use database-level filters with extra caution or fall back to table-based filters instead.

# In the replica's configuration file (mysqld section)
replicate-do-db = shop_db
replicate-ignore-db = shop_db_reporting_internal

3. Table level: replicate-do-table and wildcard filters

Filters at the table level operate far more precisely: replicate-do-table and replicate-ignore-table target individual, fully named tables, while replicate-wild-do-table and replicate-wild-ignore-table allow wildcard patterns with % and _, for example to exclude every table matching a given prefix at once. For a reporting replica, this lets you exclude individual sensitive tables without pulling the entire database into a database-level filter.

A typical pattern for a Magento-adjacent reporting replica explicitly excludes tables carrying personal or payment-related data, while keeping the bulk of the schema available for analysis. It matters that filtering still happens at the level of whole tables, native MySQL replication filtering does not support column-level filtering, for example to exclude only specific fields of a customer table.

replicate-wild-ignore-table = shop_db.customer_entity%
replicate-wild-ignore-table = shop_db.sales_order_payment%
replicate-wild-ignore-table = shop_db.customer_address_entity%

4. How MySQL evaluates the filter order

When both do and ignore rules are active at the same time, the server evaluates do rules first: if at least one replicate-do-table or replicate-wild-do-table rule exists, an event only gets replicated if it is explicitly covered by one of those rules, regardless of any additionally defined ignore rules. Only once no do rule matches at all do the ignore rules come into play, excluding explicitly named patterns from an otherwise complete replication.

This order often clashes with the intuitive expectation that do and ignore rules cooperate as equals. In practice this means: whoever accidentally sets both a do rule for shop_db and an ignore rule for a single table inside that database needs to understand precisely that the do rule takes precedence, and the ignore rule stays without effect unless it is also covered by a matching do rule.

5. Specifics of row-based filters

Under row-based replication, the default binlog format in most production environments since MySQL 5.7, events are not filtered based on the SQL text but based on the target table, which is explicitly encoded in every row event. That makes filtering noticeably more robust against cross-database statements, because the actually affected table is known independently of the calling SQL context, unlike the statement-based evaluation through the active USE database.

One important difference still applies though: replicate-do-db and replicate-ignore-db still evaluate the database that the target table resides in for row-based events, not the session database active at execution time. For environments running row format, this behavior is in practice more intuitive and predictable than its statement-based counterpart, which is why row format is also the more robust choice from a filtering standpoint.

6. CHANGE REPLICATION FILTER as a modern, dynamic alternative

Besides static configuration through the server config file, CHANGE REPLICATION FILTER lets you set filter rules at runtime, without restarting the server. The replication thread has to be stopped for this, but a full restart of the mysqld process is not required, which is a real operational advantage in environments with strict availability requirements.

This dynamic variant works well for temporary adjustments, for example to briefly exclude an additional table from a filter during a migration. For permanent filter rules, the static configuration file remains the more robust choice, since CHANGE REPLICATION FILTER falls back to the values in the config file after a server restart unless combined with a PERSIST option.

STOP REPLICA;
CHANGE REPLICATION FILTER
  REPLICATE_WILD_IGNORE_TABLE = ('shop_db.customer_entity%', 'shop_db.sales_order_payment%');
START REPLICA;

SHOW REPLICA STATUS\G

7. Pitfalls when changing a filter after the fact

The biggest practical pitfall arises when a filter changes after the replica has already been running with the old filter for an extended period. If, for example, a previously excluded table gets added into replication later on, the replica holds no historical data for that table, only the changes from the moment the filter changed onward, resulting in an inconsistent, partially empty dataset that looks unremarkable at first glance.

The only reliable fix in that case is a full resync of the replica for the affected table, usually through a targeted data export from the source or a complete replica rebuild from a current backup. Under GTID-based replication, this gets further complicated by the fact that gtid_purged and the replica's already known GTID set do not automatically line up with the new filter configuration, which is why a clean resync usually remains the only viable path, rather than trying to incrementally close the gap.

8. Use case multi-store: separate databases per tenant

In multi-store architectures with a dedicated database per tenant, replication filters let you build dedicated replicas that hold only a single tenant's data, for example for a tenant-specific support access or an isolated test environment with production-like but privacy-restricted data. Scoping replicate-do-db to a specific tenant database implements this scenario without any additional infrastructure such as separate ETL pipelines.

It matters that shared, cross-tenant reference data, where it exists, either gets included in the filter rule as well or gets deliberately documented as a known limitation, since a too-narrowly scoped database filter would otherwise break functionality that depends on that shared data, without the resulting error obviously pointing back at replication filtering at first glance.

9. Use case reporting replica: deliberately excluding sensitive tables

For a reporting replica feeding business intelligence tools or data warehouse load processes, the GDPR angle is the central driver behind replication filters: tables carrying direct personal data, such as customer addresses, payment tokens, or contact history, should not physically exist there in the first place, rather than being secured after the fact purely through access rights. A consistently filtered replica structurally reduces both the attack surface and the blast radius of a possible data leak, independent of how careful access control on the target system happens to be.

In practice it pays to document the list of sensitive tables centrally and actively check, with every new Magento module installation, whether new, potentially sensitive tables have appeared that need to be added to the existing filter configuration. That upkeep is a recurring but necessary effort, easily forgotten without a fixed process, until a new table quietly ends up on the reporting replica.

10. Summary

11. FAQ: Replication Filters: Key Facts at a Glance