MySQL Replication Filters: Selective Replication for Specific Databases and Tables

Your analytics team needs a dedicated read replica — but only for the analytics database, not the customer PII stored in users or the financial records in billing. Your compliance team has mandated that payment data never leaves the primary datacenter, yet your reporting infrastructure sits in a secondary region. Or maybe you're consolidating three application databases onto a single replica but only care about two of them. This is exactly the problem MySQL replication filters solve: they let you control which databases and tables flow through replication, giving you surgical control over what each replica receives.

Replication filters are powerful, widely used, and also one of the most frequently misconfigured features in MySQL. A misplaced USE statement in a client session can silently drop an entire transaction with statement-based replication. A filter set on the wrong side of the replication chain produces confusing behavior that only surfaces under load. This guide covers how filters actually work, where to set them, and the specific pitfalls that cause data loss in production.

Server-Side Filters: binlog-do-db and binlog-ignore-db

Server-side filters are configured on the primary and control what gets written to the binary log. If an event never enters the binary log, no replica will ever see it — regardless of that replica's own filter configuration.

binlog-do-db

binlog-do-db creates a whitelist: only events for the specified database are written to the binary log. All other databases are silently excluded.

# my.cnf on the PRIMARY server
[mysqld]
server-id        = 1
log-bin          = mysql-bin
binlog-do-db     = analytics
binlog-do-db     = reporting

With this configuration, changes to analytics and reporting are logged. Changes to users, billing, or any other database are not. You can specify multiple databases by repeating the option.

binlog-ignore-db

binlog-ignore-db is the inverse — a blacklist. Specified databases are excluded from the binary log; everything else is logged.

# my.cnf on the PRIMARY server
[mysqld]
server-id        = 1
log-bin          = mysql-bin
binlog-ignore-db = internal_cache
binlog-ignore-db = tmp_processing

A critical constraint: you cannot mix binlog-do-db and binlog-ignore-db in a useful way. If binlog-do-db is set, binlog-ignore-db is effectively ignored. Pick one approach.

Why server-side filters should be used sparingly

Server-side filters are permanent and global. Once a database is excluded from the binary log, no replica — current or future — can ever receive those events. You lose point-in-time recovery capability for the excluded databases. You also cannot add a new replica later that needs the filtered data. In most architectures, client-side filters on the replica are the better choice because the primary binary log remains complete.

Client-Side Filters: replicate-do-db, replicate-ignore-db, replicate-do-table

Client-side filters are configured on the replica. The replica reads all events from the primary's binary log, then applies filters to decide what to actually execute. This preserves the full binary log on the primary while giving each replica its own filtered view.

replicate-do-db and replicate-ignore-db

These work the same way as their server-side counterparts, but on the replica side:

# my.cnf on the REPLICA server
[mysqld]
server-id          = 2
relay-log          = relay-bin
replicate-do-db    = analytics
replicate-do-db    = reporting

The replica will read every event from the relay log but only apply changes belonging to the analytics and reporting databases.

replicate-do-table and replicate-ignore-table

Table-level filters give you finer control than database-level filters:

# my.cnf on the REPLICA server
[mysqld]
server-id              = 2
relay-log              = relay-bin
replicate-do-table     = analytics.sessions
replicate-do-table     = analytics.events
replicate-ignore-table = analytics.debug_logs

Table filters use the format database.table and support wildcard matching through replicate-wild-do-table and replicate-wild-ignore-table:

# Replicate all tables in analytics except those prefixed with tmp_
replicate-wild-do-table     = analytics.%
replicate-wild-ignore-table = analytics.tmp_%

Filter evaluation order

When multiple filter types are configured on a replica, MySQL evaluates them in this order:

1. replicate-do-db / replicate-ignore-db
2. replicate-do-table / replicate-ignore-table
3. replicate-wild-do-table / replicate-wild-ignore-table

A transaction must pass each applicable filter level. If it's rejected at the database level, table-level filters are never evaluated.

The USE Statement Pitfall with Statement-Based Replication

This is the most dangerous behavior in MySQL replication filters and the source of many production incidents. With statement-based replication (SBR), the database-level filters (binlog-do-db, binlog-ignore-db, replicate-do-db, replicate-ignore-db) are evaluated against the current default database at the time of execution — meaning the database set by the most recent USE statement — not the database referenced in the actual SQL query.

USE other_db;
UPDATE analytics.sessions SET last_seen = NOW() WHERE user_id = 42;

This behavior applies to all statement-based filter options. The only safe options with SBR are table-level filters (replicate-do-table) because those are evaluated against the actual table reference in the statement, not the current database context. Alternatively, switching to row-based replication eliminates this problem entirely.

Row-Based Replication and How Filters Behave Differently

With row-based replication (RBR), the binary log contains actual row change events, each tagged with the specific database and table that was modified. Filter evaluation uses this metadata — not the session's current database — so cross-database queries are handled correctly.

# my.cnf on the PRIMARY — enable row-based replication
[mysqld]
binlog-format = ROW

With RBR active, the same cross-database update that silently failed under SBR will now be correctly evaluated: the row event is tagged with analytics.sessions, so replicate-do-db = analytics correctly includes it regardless of what USE statement preceded the query.

Row-based replication is strongly recommended for any topology using database or table filters. Mixed format (binlog-format = MIXED) provides less protection because MySQL selects statement-based format for many statements, reintroducing the USE pitfall for those events.

One nuance with RBR: DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE) are always logged in statement format even in RBR mode. Your filters still apply to DDL based on the current database context. Make sure your application always issues USE analytics; before DDL, or use fully qualified table names in DDL with awareness that filters evaluate the session database.

Dynamic Replication Filters (CHANGE REPLICATION FILTER without Restart)

Starting with MySQL 5.7, you can modify client-side replication filters on a running replica without editing my.cnf or restarting MySQL. The CHANGE REPLICATION FILTER statement applies changes immediately — but the replication SQL thread must be stopped first.

-- Stop the SQL thread (I/O thread can keep running)
STOP REPLICA SQL_THREAD;

-- Add or replace filter rules
CHANGE REPLICATION FILTER
  REPLICATE_DO_DB = (analytics, reporting),
  REPLICATE_IGNORE_TABLE = (analytics.debug_logs, analytics.tmp_sessions);

-- Restart the SQL thread
START REPLICA SQL_THREAD;

Important: CHANGE REPLICATION FILTER replaces the entire filter list for each option you specify — it does not append. If you had replicate-do-db = analytics set in my.cnf and you run CHANGE REPLICATION FILTER REPLICATE_DO_DB = (reporting), analytics is no longer filtered in. Always specify the complete desired set.

To verify the filters currently active after a dynamic change:

SELECT * FROM performance_schema.replication_applier_filters\G

To make dynamic filters survive a MySQL restart, you must also update my.cnf. Dynamic filters set with CHANGE REPLICATION FILTER are lost on restart unless persisted to the configuration file.

Checking Active Filters: SHOW REPLICA STATUS and performance_schema

There are two primary ways to inspect what filters are active on a replica.

SHOW REPLICA STATUS

SHOW REPLICA STATUS\G

The relevant fields in the output:

Replicate_Do_DB:        analytics,reporting
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: analytics.debug_logs
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

This shows what's configured but gives no detail on which channels (in multi-source replication) a filter applies to.

performance_schema tables

For multi-source replication or more structured inspection, use the performance_schema tables introduced in MySQL 8.0:

-- View per-channel filter configuration
SELECT
  CHANNEL_NAME,
  FILTER_NAME,
  FILTER_RULE
FROM performance_schema.replication_applier_filters
ORDER BY CHANNEL_NAME, FILTER_NAME;

-- View global filters (apply to all channels)
SELECT *
FROM performance_schema.replication_applier_global_filters;

To verify that server-side filters are active on the primary, query the binary log status directly — binlog-do-db and binlog-ignore-db do not appear in SHOW REPLICA STATUS because they're primary-side settings. Check the primary's my.cnf or use:

-- On the PRIMARY:
SHOW VARIABLES LIKE 'binlog%';

When NOT to Use Replication Filters

Replication filters solve specific problems but create significant complexity in others. Avoid them in these situations:

• GTID replication with gaps: If you're using GTID-based replication and apply filters that skip transactions, the GTID set on the replica becomes inconsistent with the primary. This causes errors when you try to use the replica as a failover candidate or promote it. If you need GTID with filters, you must carefully manage the executed GTID set (gtid_executed) or use CHANGE REPLICATION FILTER only for tables where data loss on promotion is acceptable.
• Cross-database foreign keys: If filtered-in tables have foreign key relationships to filtered-out tables, DML on the replica can fail with constraint violations. Filters have no understanding of schema relationships.
• When you need a complete standby for failover: A replica with database filters cannot serve as a full standby for the primary. In a failover scenario, the filtered replica will be missing data and cannot cleanly take over. Use filtering only on dedicated read replicas, never on your failover standby.
• Schema changes on filtered databases: If you filter out a database but later need to add it back, the replica has no record of historical DDL. You'll need to re-initialize the replica from a fresh backup for the newly included database.

Working with JusDB on MySQL Replication

JusDB designs selective replication topologies for MySQL teams — from multi-source filtering to GTID-compatible configurations. Our DBAs ensure your filters work correctly across replication format changes and MySQL upgrades.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

• MySQL GTID Replication Guide
• MySQL Replication Lag: Causes and Fixes
• MySQL Binary Log Management