Replica Filters Were Silently Dropping Rows and We Didn

The replica was receiving all binlog events. It was applying most of them. Replicate_Ignore_Table had been set two months earlier to exclude a logging table from replication — a reasonable decision. What nobody noticed was that the filter expression was using a wildcard that matched three other tables that were supposed to be replicated. Rows were silently missing.

The Alert

A reconciliation job detected that the analytics replica was missing 340,000 customer preference records that existed on the primary. The records had been written over the past eight weeks. The replica had never received them — not because of a replication error, but because of a filter that was broader than intended.

The Discovery

Check all active replication filters

MySQL

SHOW SLAVE STATUS\G

-- Key filter fields to inspect:
-- Replicate_Do_DB:
-- Replicate_Ignore_DB:
-- Replicate_Do_Table:
-- Replicate_Ignore_Table:
-- Replicate_Wild_Do_Table:
-- Replicate_Wild_Ignore_Table:   ← THIS was the problem

-- Also check from performance_schema:
SELECT * FROM performance_schema.replication_applier_filters;

Replicate_Wild_Ignore_Table: myapp.audit_log%. The intent was to exclude audit_log. The wildcard audit_log% also matched audit_log_preferences, audit_log_user_settings, and audit_log_consents — all business tables that contained real customer data. Every INSERT to those three tables for two months had been silently dropped on the replica.

Incident Timeline

Time	Event
8 weeks ago	CHANGE REPLICATION FILTER set with Replicate_Wild_Ignore_Table='myapp.audit_log%'
8 weeks ago-now	340,000 rows silently dropped. No errors. No warnings.
Investigation day	Reconciliation job detects missing records on replica
+1 hour	Filter expression identified as the cause
+2 hours	Filter corrected: exact table name, no wildcard
+3 hours	340,000 missing rows manually copied from primary to replica

Root Cause

Wildcard filter expressions in MySQL replication match by SQL LIKE pattern. The % wildcard matches any suffix. audit_log% was intended to match only audit_log but matched any table whose name began with audit_log. MySQL did not warn that the filter was broader than the table it was intended to exclude.

The Fix

Use exact table names in replication filters, never wildcards

MySQL

-- Remove the wildcard filter:
CHANGE REPLICATION FILTER
  Replicate_Wild_Ignore_Table = ('');

-- Replace with exact table exclusion:
CHANGE REPLICATION FILTER
  Replicate_Ignore_Table = ('myapp.audit_log');

-- Verify filters are correct:
SHOW SLAVE STATUS\G
-- Confirm: Replicate_Ignore_Table = myapp.audit_log
-- Confirm: Replicate_Wild_Ignore_Table = (empty)

-- Count-verify tables between primary and replica:
SELECT table_name, table_rows
FROM   information_schema.tables
WHERE  table_schema = 'myapp'
  AND  table_name LIKE 'audit_log%';

Prevention

Replication filters are now reviewed quarterly. Any filter change triggers an automated row count comparison between primary and replica for all tables matching the filter pattern. Wildcard filters require sign-off from a second engineer who verifies no unintended tables are matched.

Auditing Replication Filters for Unintended Scope

Replication filters applied months or years ago can become mismatched with the current schema as tables are renamed, added, or removed. A quarterly audit of filter expressions against the current table list is the only way to detect silent exclusions before data diverges significantly.

List all active replication filters and what they match

MySQL

-- All filters visible here:
SHOW SLAVE STATUS\G
-- Check every filter field:
-- Replicate_Do_DB, Replicate_Ignore_DB
-- Replicate_Do_Table, Replicate_Ignore_Table
-- Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table

-- MySQL 8.0 — filters per channel:
SELECT filter_name, filter_rule, configured_by
FROM   performance_schema.replication_applier_filters;

Test which tables a wildcard filter actually matches

MySQL

-- If Replicate_Wild_Ignore_Table = 'mydb.audit_log%',
-- check which tables in your schema START WITH 'audit_log':
SELECT table_schema, table_name, table_rows
FROM   information_schema.tables
WHERE  table_schema = 'mydb'
  AND  table_name LIKE 'audit_log%'
ORDER BY table_name;

-- Every table returned here is being EXCLUDED from replication.
-- If any of them should be replicated, the wildcard is too broad.
-- Replace with exact table name: Replicate_Ignore_Table = 'mydb.audit_log'

Row count reconciliation between primary and replica

MySQL

-- Run on both primary and replica, compare results:
-- For each table that might be filtered, run on both servers:
SELECT 'primary' AS server, COUNT(*) AS row_count FROM audit_log_preferences
UNION ALL
SELECT 'replica' AS server, COUNT(*) AS row_count FROM audit_log_preferences;

-- Any count mismatch = rows have been silently dropped on the replica

Ananya Menon

MySQL DBA · 9 years production · QueryTuning

Ananya has spent 9 years managing MySQL and MariaDB for SaaS companies. She specialises in replication architecture, data integrity monitoring, and the operational failure modes that only appear after weeks of silent divergence.

Replica Filters Were Silently Dropping Rows and We Didn't Notice for Three Weeks

The Alert

The Discovery

Incident Timeline

Root Cause

The Fix

Prevention

Auditing Replication Filters for Unintended Scope

The Same Problem on Other Engines