Python Binlog Parsing Library Reference: mysql-connector-python vs PyMySQL vs python-mysql-replication #

The three Python libraries this platform relies on solve different problems, and reaching for the wrong one produces code that either cannot see binlog events at all or reimplements a replication client badly. mysql-connector-python and PyMySQL are SQL clients — they run queries, read SHOW BINARY LOGS, and manage connections, but they do not decode binlog event streams. python-mysql-replication is a replication client — it registers as a replica and yields typed row events, but it is not a general query driver. Automation that conflates them ends up polling SHOW BINARY LOGS in a loop when it wanted event-level change data, or trying to parse raw binlog bytes by hand when a maintained streamer already does it. This reference maps each library to the archiving and recovery tasks it actually fits, so the detection, transport, and replay components of Point-in-Time Recovery Workflow Automation and Automated Binlog Archiving to Object Storage each use the right tool.

Visual Overview #

Core Concept & Prerequisites #

Binlog work in Python splits into two distinct jobs. Control-plane work — enumerating segments with SHOW BINARY LOGS, reading gtid_executed, seeding gtid_purged, driving a recovery target — is ordinary SQL, and any DB-API driver does it. Data-plane work — receiving the actual sequence of insert/update/delete row events as they are logged — requires speaking the replication protocol, which only python-mysql-replication does among these three. The archiving pipeline is almost entirely control-plane (it moves whole files, not events), while change-data or fine-grained replay tooling is data-plane.

You need Python 3.10+ and, depending on the job: mysql-connector-python (Oracle’s official DB-API driver, C-accelerated, with a built-in pool), PyMySQL (a pure-Python DB-API driver, dependency-light), or python-mysql-replication (the BinLogStreamReader that registers as a replica and yields decoded events). All three are already referenced across this site’s examples. Row-event decoding requires binlog_format=ROW upstream, per ROW vs STATEMENT vs MIXED Formats, and a dedicated replication-privileged account per Security & Access Frameworks.

Production-Grade Python Implementation #

The pattern that scales is to use each library for its role in the same system. A mysql-connector-python pool handles control-plane queries in the archiver’s detector; python-mysql-replication handles data-plane streaming only where event-level granularity is actually required.

# libref/roles.py — Python 3.10+
from __future__ import annotations

import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import DeleteRowsEvent, UpdateRowsEvent, WriteRowsEvent

# Control plane: a pooled SQL driver for metadata and orchestration.
POOL = MySQLConnectionPool(pool_name="ctl", pool_size=4, autocommit=True)


def executed_gtid_set() -> str:
    conn = POOL.get_connection()
    try:
        cur = conn.cursor()
        cur.execute("SELECT @@GLOBAL.gtid_executed")   # MySQL 8.0.22+
        return cur.fetchone()[0]
    finally:
        conn.close()


# Data plane: a replication client for decoded row events (only where needed).
def stream_changes(conn_settings: dict, server_id: int):
    stream = BinLogStreamReader(
        connection_settings=conn_settings,
        server_id=server_id,                 # must be unique among replicas
        only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent],
        blocking=True,
        resume_stream=True,
    )
    try:
        for event in stream:
            for row in event.rows:
                yield (event.schema, event.table, type(event).__name__, row)
    finally:
        stream.close()

PyMySQL slots in wherever mysql-connector-python would, when a pure-Python dependency is preferable (constrained build environments, or when the C extension is unavailable); it lacks a built-in pool, so pair it with an external pooler. The concrete streaming patterns, offset resumption, and schema-change handling for BinLogStreamReader are developed in Streaming Binlog Events with BinLogStreamReader, and the head-to-head selection guidance is in mysql-connector-python vs PyMySQL vs python-mysql-replication for Binlog Parsing.

Configuration Reference #

Validation & Verification Gates #

• Control-plane code uses a pooled SQL driver, not a replication client, for SHOW/GTID queries.
• Event-streaming code uses python-mysql-replication, not hand-rolled byte parsing.
• The streaming account holds REPLICATION SLAVE and REPLICATION CLIENT, nothing more.
• Each BinLogStreamReader uses a server_id unique among all replicas and readers.
• binlog_format=ROW is asserted before any row-event decoding is attempted.
• A PyMySQL control path is paired with an external pool if it runs under concurrency.

Error Handling & Failure Modes #

Could not find first log file name in binary log index file from BinLogStreamReader means the requested start position or GTID has already been purged from the primary — the reader must resume from an archived position instead, which is why event streaming and file archiving are complementary, not interchangeable.

A duplicate server_id causes the primary to drop one of the connections with a replica-registration error; every reader and replica needs a distinct id.

Trying to decode events with a query driver simply returns rows from a SELECT, never binlog events — the classic symptom of using mysql-connector-python where python-mysql-replication was needed. Conversely, calling arbitrary DML through BinLogStreamReader is impossible; it is read-only over the replication stream.

ERROR 1227 (access denied for REPLICATION SLAVE) on the streaming account means the grant is missing; the control-plane drivers do not need it, so the failure appears only when data-plane code runs.

Observability & Alerting #

For control-plane drivers, monitor pool saturation and query latency on the detector’s metadata queries. For the replication client, the critical metric is reader lag — the delta between the primary’s newest GTID and the reader’s last processed event — plus a counter of schema-change events, which are the usual cause of a reader stall. Alert when a BinLogStreamReader stops advancing, since a silently stalled event stream looks identical to an idle one. These map onto the same archiving-lag and recovery-readiness signals tracked in Async Processing & Queue Management and RTO/RPO Recovery Drills.

Frequently Asked Questions #

Related #

• mysql-connector-python vs PyMySQL vs python-mysql-replication for Binlog Parsing — the head-to-head selection guide.
• Streaming Binlog Events with BinLogStreamReader — resumable event streaming and schema-change handling.
• mysqlbinlog Replay Scripting — when the CLI is the right interface instead of a Python parser.
• ROW vs STATEMENT vs MIXED Formats — why row-based logging is a prerequisite for event decoding.

Back to Point-in-Time Recovery Workflow Automation.