The analytics replica looked perfectly healthy for six weeks. Replication running, lag near zero, no errors in any log. Then month-end reconciliation came up short by a few hundred thousand rows, and the cause turned out to be one configuration line, replicate_do_db=analytics, plus one application habit: the rollup job connected with USE reporting and then ran UPDATE statements against tables in the analytics schema. Every one of those updates was silently skipped by the applier. Not logged anywhere. Not retried. Just absent, which is the nastiest property of replication filters: their failure mode is silence.
Filters are not inherently evil. They are just sharp tools with two different blades that people constantly mistake for each other. Here is which filter lives in which layer, why the default database decides more than you think, what row-based binlogging does and does not fix, why source-side filters wreck point-in-time recovery, and the safer patterns I reach for instead.
Which layer does each filter actually live in?
binlog-do-db and binlog-ignore-db live on the source, at binary log write time: they decide whether a change is recorded in the binlog at all. replicate-do-db, replicate-ignore-db, replicate-do-table, replicate-ignore-table, and the wild variants live on the replica, at apply time: the source logs everything, ships everything, and the replica's SQL thread discards what does not match. Same family of names, completely different blast radius. A source-side filter means the change never existed as far as every replica and every binlog-based process is concerned. A replica-side filter means the binlog is still a complete record, the divergence is confined to that one replica, and you can always re-point or rebuild from the full stream.
That asymmetry should drive every decision. Replica-side filters are a scalpel you apply where the divergence is wanted and contained. Source-side filters are an amputation shared by every consumer of the binlog, including the backups you have not needed yet. Replica filters can be changed online with CHANGE REPLICATION FILTER, no restart required, which makes them the more reversible choice as well.
Why does the default database decide what gets replicated?
With statement-based logging, database-level filters are evaluated against the session's current database, the one set by USE, not against the schemas of the tables the statement actually touches. That single implementation detail is responsible for nearly every filter horror story. Consider the rollup job:
USE reporting;
UPDATE analytics.daily_rollups AS r
JOIN raw.events AS e ON e.id = r.event_id
SET r.row_count = r.row_count + 1;On a replica with replicate_do_db=analytics, the applier checks the current database, finds reporting, and skips the statement, even though the only table being modified lives in analytics. Flip the filter to replicate_ignore_db=reporting and the same statement is again dropped, this time by matching the USE. Now move the filter to the source with binlog_do_db=reporting: the update is logged and shipped even though it wrote analytics, so the analytics replica receives a change it was never supposed to see. Three plausible configurations, three different wrong answers, and not one of them raises an error on either server. The MySQL manual has warned for years against combining database-level filters with cross-database updates under statement-based replication, and it remains one of the most ignored warnings in the ecosystem.
Does row-based binlog_format fix the filtering problem?
Mostly, for row changes, and not at all for everything else. Row events carry the schema and table they modify, so under binlog_format=ROW the applier's database and table filters match the actual target table instead of the ambient USE database. The cross-database UPDATE above filters correctly under row format: an analytics-do-db replica applies it because the changed rows belong to analytics.daily_rollups. That alone is reason enough to insist on ROW format on any topology that uses filters.
The cracks show up at the edges. DDL is still logged as statements and still evaluated through the default database, so CREATE TABLE reporting.work AS SELECT from analytics tables, or a DROP issued from a different current schema, can filter differently than the row traffic around it. Stored procedures, functions, and events have their own logging rules that interact with filters in ways that reward careful reading. And replicate-rewrite-db, which remaps one schema name to another, composes with the wild table filters in an order that surprises almost everyone the first time. My working rule: if you must filter, run row format and prefer table-level wild filters, replicate_wild_do_table = ('analytics.%'), because matching real table names is the least surprising semantics the filter family offers.
How do source-side filters break point-in-time recovery?
Point-in-time recovery is restore the last full backup, then replay the binary logs up to the target moment. That recipe silently assumes the binlog is a complete record of every committed change. binlog-do-db and binlog-ignore-db void the assumption: the filtered-out changes were never logged, so a PITR replay reconstructs only the subset of history that passed the filter, and the restored server is missing data with no indication that anything is missing. mysqlbinlog does not warn. The restore does not fail. You find out weeks later, the way I did, when someone asks a question the data should be able to answer.
The rule I enforce is simple: the server whose binlog feeds backups and PITR logs everything, no exceptions. If bandwidth or replica storage demands filtering, do it downstream: one complete archive replica close to the source keeps the full stream for recovery, and filtered replicas hang off that. This composes well with crash-safety hygiene on the replicas themselves; the checklist in MySQL replica crash safety covers the other half of what a replica owes you before you trust it.
How do you audit filters and detect drift?
SHOW REPLICA STATUS exposes the active filter sets in the Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, and Replicate_Wild_Ignore_Table fields, and that is the right first look. For anything scripted, performance_schema.replication_applier_filters is the queryable source, and its configured_by and active_since columns tell you whether the filter came from startup options or a later CHANGE REPLICATION FILTER:
SELECT channel_name, filter_name, filter_rule,
configured_by, active_since
FROM performance_schema.replication_applier_filters;Its sibling, performance_schema.replication_applier_global_filters, covers channel-independent filters. Auditing the configuration is the easy half. The hard half is drift, and the uncomfortable truth is that Seconds_Behind_Source is useless for it: a replica can be zero seconds behind and materially different, because applying a filtered stream successfully says nothing about whether the data matches. Drift detection needs data comparison. pt-table-checksum remains the standard tool, walking tables in chunks and comparing CRC32 digests between source and replica; for small reference tables, CHECKSUM TABLE on both ends is a decent poor-man's version:
CHECKSUM TABLE analytics.daily_rollups, analytics.monthly_rollups;Row counts, the check everyone reaches for first, lie routinely: deleted-then-reinserted data preserves counts while changing content. If you run filters at all, budget for a weekly checksum, because with filters in play drift is a when, not an if. And keep GTIDs on so that when you find drift, re-pointing and rebuilding is deterministic; errant transactions under GTID is the failure you will meet next if it is not.
What are safer patterns than filtering?
The pattern I recommend first removes the need entirely: replicate everything to a dedicated replica per consumer, and expose only what each consumer should see through views and grants. A reporting user with SELECT on a set of views over the analytics schema gets exactly the same visible surface as a filtered replica, but the data underneath is complete, the replica is a valid promotion candidate, and PITR still works. The filtered-replica approach buys you less storage and less write load on the replica, and in my experience those savings are real only at sizes where you can also afford the discipline filters demand.
When filtering is genuinely justified, usually bandwidth between regions or a replica that truly cannot hold the full dataset, the defensible shape is: full unfiltered binlog on the source, one complete archive replica for recovery, filtered replicas downstream of it, row format everywhere, wild table filters over database filters, scheduled checksums, and a standing rule that a filtered replica is never promoted without a full data rebuild. Promotion is where the theory dies: a filtered replica that becomes a source has silently lost data, and no failover tooling will tell you. If your failover path might touch a filtered replica, the lag and health checks in replication lag diagnosis belong in that runbook too.
Where MonPG stands on MySQL
I build MonPG, so straight answer: it monitors PostgreSQL today, and MySQL support is in active development, coming soon rather than shipping. The filter failure modes in this note are a monitoring design brief: applier filter inventories, checksum divergence, lag that is not lag, and promotion-candidate hygiene are all things a monitor should surface before month-end reconciliation does. The MySQL monitoring (coming soon) page tracks that work as it lands. Until then, the equivalent replication-health workflow already runs on the PostgreSQL side, and the rest of these MySQL field notes are on the blog.