mysql-connector-python vs PyMySQL vs python-mysql-replication for Binlog Parsing #

Reach for the wrong one of these three libraries and you either cannot see binlog events at all or you reimplement a replication client by hand. The confusion is understandable — all three connect to MySQL and all three appear in binlog tooling — but they occupy two different planes. mysql-connector-python and PyMySQL are SQL drivers that read metadata (SHOW BINARY LOGS, gtid_executed) and drive recovery targets; python-mysql-replication is a replication client that decodes the event stream. This page is the decision guide: it lays out the axes that actually distinguish them — pooling, packaging, protocol support, privileges — and gives a concrete rule for which to use where in an archiving and recovery pipeline.

Context & Prerequisites #

This is the head-to-head companion to the capability overview in Python Binlog Parsing Library Reference; read that for the control-plane versus data-plane framing this decision rests on. You need Python 3.10+ and MySQL 8.0.22+. Event decoding additionally requires binlog_format=ROW, per ROW vs STATEMENT vs MIXED Formats, and a REPLICATION SLAVE grant scoped per Security & Access Frameworks.

Step-by-Step Implementation #

1. Decide the plane first #

Classify the task before picking a library: is it a SQL query (control plane) or a stream of decoded row events (data plane)?

# Python 3.10+
def plane(task: str) -> str:
    match task:
        case "show_binary_logs" | "read_gtid" | "seed_purged" | "drive_recovery":
            return "control"     # any SQL driver
        case "cdc" | "row_events" | "event_transform":
            return "data"        # python-mysql-replication only
        case _:
            return "control"

PITR relevance: the archiver’s detector and the recovery orchestrator are almost entirely control plane; only change-data or event-level tooling needs the data plane, so most of the pipeline uses a SQL driver.

2. For control work, choose on pooling and packaging #

Both SQL drivers cover the same surface; the differentiators are the built-in pool and the dependency footprint.

# Python 3.10+ — connector: built-in pool, optional C acceleration
from mysql.connector.pooling import MySQLConnectionPool
POOL = MySQLConnectionPool(pool_name="ctl", pool_size=4, autocommit=True)

# Python 3.10+ — PyMySQL: pure-python, dependency-light, bring your own pool
import pymysql
conn = pymysql.connect(host="127.0.0.1", user="ctl", autocommit=True)

PITR relevance: a long-running detector benefits from mysql-connector-python’s built-in pool; a constrained or pure-Python build environment favors PyMySQL with an external pool.

3. For event work, use python-mysql-replication #

Only BinLogStreamReader speaks the replication protocol and yields typed row events.

# Python 3.10+
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent

reader = BinLogStreamReader(
    connection_settings={"host": "127.0.0.1", "user": "repl", "passwd": "..."},
    server_id=1234, only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent],
    resume_stream=True, blocking=True,
)

PITR relevance: event-level access enables custom validation over row images and change-data capture; for replaying a stream back, prefer the mysqlbinlog CLI driven as in mysqlbinlog Replay Scripting.

Configuration Snippet & Reference Table #

Verification Checklist #

• Each task is classified control-plane or data-plane before a library is chosen.
• Control-plane code uses a SQL driver, never BinLogStreamReader, for SHOW/GTID queries.
• A PyMySQL control path under concurrency is paired with an external connection pool.
• Event-streaming code uses python-mysql-replication, not hand-rolled parsing.
• The streaming account has REPLICATION SLAVE and a server_id unique among replicas.
• Replaying a stream back into a target uses the mysqlbinlog CLI, not an event reader.

Gotchas & Version-Specific Caveats #

A query driver never yields binlog events. Using mysql-connector-python where events are needed just returns SELECT rows; the symptom is “I can’t see any changes.” Switch to python-mysql-replication.

PyMySQL has no built-in pool. Under concurrency, pair it with an external pooler or you will exhaust connections; mysql-connector-python includes MySQLConnectionPool.

Duplicate server_id drops the reader. Every BinLogStreamReader needs an id distinct from all replicas and other readers, or the primary terminates one connection.

Event reading is not replaying. python-mysql-replication consumes the stream read-only; it cannot apply changes back. For recovery replay, the CLI’s file handling and stop conditions are the right tool.

Frequently Asked Questions #

Related #

• Python Binlog Parsing Library Reference — the capability overview this guide decides between.
• Streaming Binlog Events with BinLogStreamReader — the data-plane library in practice.
• mysqlbinlog Replay Scripting — why the CLI is the right tool for replaying a stream.

Back to Python Binlog Parsing Library Reference.