Replication Filters, Delayed Replicas, Read Scaling, and Reporting Workloads

Learning outcomes

Replication makes it tempting to solve every topology problem with another replica: filter it for reports, delay it for recovery, send reads to it, and assume it is “just another copy.” Each of those choices changes semantics. This lesson teaches the boundaries explicitly so that a reporting optimization does not quietly become a recovery or correctness problem.

Filtering changes the meaning of “replica”

A filtered replica intentionally does not represent the complete source. That may be useful for a reporting domain, but it disqualifies the server from some promotion or recovery roles unless the missing data is explicitly acceptable. MySQL recommends doing replication filtering on the replica rather than suppressing source binary logging, because source logs remain a complete change stream for other replicas and recovery.

Database-level filters have a famous semantic trap: for statement-format events, rules can depend on the default database selected with USE; for row-format events, MySQL can test the database actually affected by the row change. Table/wildcard filters are often easier to reason about for current ROW-based topologies.

Lab: filter out one table, then prove the consequence

STOP REPLICA SQL_THREAD;CHANGE REPLICATION FILTER  REPLICATE_WILD_DO_TABLE = ('servicehub_repl_lab.work_orders');START REPLICA SQL_THREAD;SELECT *FROM performance_schema.replication_applier_filters;SHOW REPLICA STATUS\G

The change requires the replication SQL/applier thread to be stopped. Filters created by CHANGE REPLICATION FILTER are runtime rules and are not a substitute for documented persistent configuration.

INSERT INTO servicehub_repl_lab.work_orders(site_code,status,summary,priority)VALUES ('BAKU-04','OPEN','Filtered replica work-order test',3);INSERT INTO servicehub_repl_lab.replication_markers(marker_name)VALUES (CONCAT('FILTERED_OUT_', UUID()));

SELECT summaryFROM servicehub_repl_lab.work_ordersWHERE summary='Filtered replica work-order test';SELECT marker_nameFROM servicehub_repl_lab.replication_markersWHERE marker_name LIKE 'FILTERED_OUT_%';

The work order should arrive while the marker does not. This is not “lag”; it is policy. Monitoring must know which data is intentionally absent so it does not raise false drift alarms—or worse, promote an intentionally incomplete server as though it were a full replica.

Remove the filter: removing policy does not backfill skipped history

STOP REPLICA SQL_THREAD;CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ();START REPLICA SQL_THREAD;SELECT *FROM performance_schema.replication_applier_filters;

Events already filtered out are not magically replayed by deleting the rule. This is a critical operational point. If you need the previously omitted table data, you must provision/copy the missing state through a controlled method. Configuration reversal is not historical repair.

Delayed replication: intentionally stale by transaction

A delayed replica applies transactions at least a configured number of seconds after the source commit. In MySQL 8.4, configure this with SOURCE_DELAY. The delay is observable in Performance Schema and SHOW REPLICA STATUS.

STOP REPLICA;CHANGE REPLICATION SOURCE TO SOURCE_DELAY=30;START REPLICA;SELECT CHANNEL_NAME, DESIRED_DELAYFROM performance_schema.replication_applier_configuration;SELECT CHANNEL_NAME, SERVICE_STATE, REMAINING_DELAYFROM performance_schema.replication_applier_status;

INSERT INTO servicehub_repl_lab.replication_markers(marker_name)VALUES (CONCAT('DELAY_', UUID()));SELECT marker_name, created_atFROM servicehub_repl_lab.replication_markersORDER BY marker_id DESC LIMIT 1;

Immediately querying the replica should not yet show the newest marker. After the delay elapses and the applier runs, it should appear.

STOP REPLICA;CHANGE REPLICATION SOURCE TO SOURCE_DELAY=0;START REPLICA;SELECT CHANNEL_NAME, DESIRED_DELAYFROM performance_schema.replication_applier_configuration;

A delayed replica is not a backup

Use delayed replication as one recovery aid alongside tested backups and binary-log retention. Chapter 13 remains the recovery foundation.

Read scaling means accepting a consistency contract

If an API writes a work order to the source and immediately reads from an asynchronous replica, the read can miss its own write. That is not a replication bug; it follows from asynchronous apply. Decide which reads tolerate staleness. For read-after-write requirements, route to the writer or use a GTID-aware waiting/consistency strategy instead of guessing that lag is “usually small.”

-- On this replica, create a barrier for everything the receiver-- has already accepted into the channel.SET @received = (  SELECT RECEIVED_TRANSACTION_SET  FROM performance_schema.replication_connection_status  WHERE CHANNEL_NAME='');SELECT WAIT_FOR_EXECUTED_GTID_SET(@received, 2) AS wait_result;-- 0 means the set was executed before timeout; 1 means timeout.

Do not turn this into a wait before every replica read; that can erase read-scaling benefits. Use it only where the business consistency contract requires it.

Reporting workloads can cause lag

A reporting query may consume CPU, I/O, buffer-pool pages, temporary space, and locks on metadata, competing with replication workers. Long DDL can also block or serialize application of later events. Monitor replica CPU/I/O and worker processing times alongside receiver/applier state. “The network is fast” does not mean the applier can keep up.

Filter rule evaluation has an order

Replication filters are not independent checkboxes evaluated in arbitrary order. MySQL applies documented database/table/wildcard/rewrite rules in a defined sequence, and mixing multiple do/ignore styles can become difficult to reason about. Oracle explicitly recommends avoiding unnecessary mixtures of do and ignore filters or wildcard and non-wildcard rules when a simpler rule can express the requirement.

The distinction between row- and statement-format evaluation is especially important. With row events, MySQL knows which table/database is being changed. With statement events, database-level filters can depend on the current default database established by USE, which means a statement that fully qualifies another database can surprise an operator who expected the object name itself to control the decision.

For the course's ROW-based ServiceHub topology, a wildcard table filter is intentionally explicit. Before deploying any production filter, write positive and negative examples for cross-database statements, DDL, renamed schemas, and each replication channel. A filter that is not testable should not be part of a failover topology.

Backfill is a data migration, not a filter toggle

Suppose the reporting replica ignored replication_markers for a month, then the team decides that table is needed. Removing the filter only changes treatment of future events. The missing month must be copied from a trusted source at a consistent boundary, and the copy must be coordinated with incoming replication so no rows are duplicated or missed.

At small scale that might mean a controlled logical export/import while the applier is paused, followed by GTID/application catch-up. At large scale it may be safer to provision a new replica from a current complete backup and apply the desired filter from the beginning. The key point is conceptual: topology configuration cannot retroactively rewrite history.

Document filtered replicas with an explicit data contract—schemas/tables included, whether DDL is expected, who owns backfill, and whether the node is excluded from promotion. This prevents an emergency operator from seeing a green replication dashboard and assuming the node is a complete copy.

Read scaling needs freshness classes

Not every read has the same consistency requirement. A public dashboard that is 30 seconds stale may be acceptable; an operator who just changed a work order and immediately reopens it may require read-your-write behavior; a payment or inventory decision may require the authoritative writer. Classify reads before routing them.

A useful design is to define freshness classes such as authoritative, bounded-stale, and eventually consistent. Authoritative/read-after-write traffic goes to the writer or waits for a known GTID. Bounded-stale traffic can use replicas only while measured apply delay remains inside a documented threshold. Eventually consistent analytics can tolerate more lag but still needs alerting when the replica stops entirely.

The WAIT_FOR_EXECUTED_GTID_SET() barrier shown earlier is useful when the caller has a specific causal dependency. It is not free: waiting consumes latency and can pile up connections when a replica is unhealthy. Time out, fall back or fail according to the product contract, and monitor how often the wait path is used.

Delayed replicas need a stop-the-clock procedure

A delayed replica helps only if operators can stop its applier before the unwanted transaction reaches the delay window. That requires monitoring, an incident trigger, and a practiced command path. If nobody notices the destructive source statement until after the delayed applier executes it, the delay provided no recovery benefit.

When using a delayed node as a recovery aid, record the source transaction/timestamp of the incident, stop the applier, preserve the node, and choose whether to extract data or promote a cloned/recovered copy. Do not casually remove the delay and let the bad transaction apply while investigating. Also remember that STOP REPLICA itself takes effect immediately; the configured delay does not delay operator control commands.

Finally, delayed replicas consume storage and operations just like ordinary replicas and may fall farther behind than the configured delay if the applier cannot keep up. DESIRED_DELAY is policy; observed processing timestamps and REMAINING_DELAY show runtime behavior.

Production judgment

Every specialized replica needs a declared role: complete failover candidate, stale reporting replica, delayed recovery aid, filtered domain replica, or some carefully documented combination. Promotion runbooks must exclude candidates whose filtering/delay policy makes them unsuitable. The next lesson turns these policies into a concrete lag, drift, and promotion diagnosis process.

Authoritative references

• MySQL 8.4 — How Servers Evaluate Replication Filtering Rules
• MySQL 8.4 — CHANGE REPLICATION FILTER
• MySQL 8.4 — Delayed Replication
• MySQL 8.4 — Performance Schema Replication Tables
• MySQL 8.4 — GTID Functions