Filtering mysqlbinlog Replay by Database and Table #

When an incident is confined to one schema — a bad migration on orders, an accidental DELETE in billing — replaying the entire binary log chain onto a recovery target is slow and reviews poorly, because it re-applies thousands of unrelated transactions you have to reason about. Filtering the replay to just the affected database cuts apply time and shrinks the review surface to the rows that actually matter. But naive filtering is a trap: mysqlbinlog --database behaves differently for row-based and statement-based events, and filtering by table is not something the CLI does at all. This page defines when database filtering is correct, why row-based logging is the prerequisite, and how to filter by table safely at apply time.

Context & Prerequisites #

This narrows the replay driven in mysqlbinlog Replay Scripting for Point-in-Time Recovery Automation; the ordering, exit-code gating, and checkpointing there still apply. You need MySQL 8.0.22+ client tools, Python 3.10+, and — critically — binlog_format=ROW upstream, because the semantics of --database are only clean for row events, per ROW vs STATEMENT vs MIXED Formats. The archived segments come from Automated Binlog Archiving to Object Storage.

Step-by-Step Implementation #

1. Confirm row-based logging before filtering #

mysqlbinlog --database=db filters row events by the database recorded in each event’s table map — precise and reliable. For statement events it filters by the session’s current default database (USE), which misses statements that qualify tables cross-schema, so statement logs filter incorrectly.

-- MySQL 8.0.22+  — must return ROW before trusting --database
SELECT @@GLOBAL.binlog_format;

PITR relevance: row-based events carry the table identity intrinsically, so filtering is exact; statement-based filtering depends on fragile USE context and can silently drop or keep the wrong statements.

2. Filter by database at decode time #

Pass --database to the decoder to emit only events for the affected schema.

# MySQL 8.0.22+  — replay only the 'billing' schema
mysqlbinlog --verify-binlog-checksum --database=billing \
  mysql-bin.000123 mysql-bin.000124 > billing_only.sql

PITR relevance: on a large multi-schema primary this can reduce the applied stream by an order of magnitude, cutting RTO and making the diff a human can review before promotion.

3. Filter by table at apply time, not in the CLI #

mysqlbinlog has no --table flag. To scope to a table, decode the database then filter the decoded events, or apply into a target and use a table-scoped verification. A robust approach parses events with python-mysql-replication and keeps only the target table.

# Python 3.10+  — table-level selection over decoded row events
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import DeleteRowsEvent, UpdateRowsEvent, WriteRowsEvent

def table_events(settings, server_id, schema: str, table: str):
    stream = BinLogStreamReader(
        connection_settings=settings, server_id=server_id,
        only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent],
        only_schemas=[schema], only_tables=[table], resume_stream=True,
    )
    try:
        for event in stream:
            yield event
    finally:
        stream.close()

PITR relevance: table-scoped recovery lets you reconstruct one table to a point in time without disturbing others on the same target — the finest-grained selective recovery, using the streaming library compared in Python Binlog Parsing Library Reference.

4. Preserve ordering and DDL awareness #

Filtering row events is safe, but DDL is logged as statements even under binlog_format=ROW. If the incident window includes an ALTER TABLE, ensure the filtered stream still carries the DDL for the target table, or the row events will not apply against a mismatched schema.

PITR relevance: a filtered replay that drops the DDL preceding its row events fails with a column-mismatch error; keep schema-changing statements for the target objects.

Configuration Snippet & Reference Table #

Verification Checklist #

• binlog_format is ROW before any --database filtering is trusted.
• The filtered stream contains only events for the target schema (verified by a decode count).
• Any DDL affecting the target table within the window is present in the filtered stream.
• Table-level filtering is done via only_tables or post-decode, not an imaginary CLI flag.
• The filtered replay applies with no column-mismatch or missing-table errors.
• The recovered table’s row count and checksums match the expected point-in-time state.

Gotchas & Version-Specific Caveats #

--database filters statements by default-database context. Under statement or mixed logging, --database=billing keeps only events logged while billing was the active schema, so a DELETE billing.invoices issued from a session in another schema is dropped. This is why row-based logging is a hard prerequisite for correct filtering.

There is no mysqlbinlog --table. Table scoping happens at apply time or through a parsing library; expecting a CLI flag leads to over-broad replays.

DDL is always statement-logged. Even with binlog_format=ROW, ALTER/CREATE/DROP are logged as statements. A table-filtered replay must retain the DDL that its row events depend on.

8.4 renamed replication filters. Server-side replicate-do-db/replicate-do-table semantics and some variable spellings changed in 8.4; if you push filtering to the target’s replication channel rather than the CLI, confirm the version’s spelling.

Frequently Asked Questions #

Related #

• mysqlbinlog Replay Scripting — the base replay driver this scopes down.
• ROW vs STATEMENT vs MIXED Formats — why row-based logging is required for correct filtering.
• Python Binlog Parsing Library Reference — the streaming library for table-level event selection.

Back to mysqlbinlog Replay Scripting.