Using MySQL Connector/Python with MySQL 8.2 Read/Write Splitting and InnoDB Cluster

MySQL 8.2 introduced read/write splitting, and this article shows how to use it with MySQL‑Connector/Python.

Architecture

We use an InnoDB Cluster for the Python program.

Cluster status query example (MySQL Shell):

<code>JS > cluster.status()
{
    "clusterName": "fred",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "127.0.0.1:3310",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "127.0.0.1:3310": {
                "address": "127.0.0.1:3310",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.2.0"
            },
            "127.0.0.1:3320": {
                "address": "127.0.0.1:3320",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.2.0"
            },
            "127.0.0.1:3330": {
                "address": "127.0.0.1:3330",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.2.0"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "127.0.0.1:3310"
}
JS > cluster.listRouters()
{
    "clusterName": "fred",
    "routers": {
        "dynabook::system": {
            "hostname": "dynabook",
            "lastCheckIn": "2023-11-09 17:57:59",
            "roPort": "6447",
            "roXPort": "6449",
            "rwPort": "6446",
            "rwSplitPort": "6450",
            "rwXPort": "6448",
            "version": "8.2.0"
        }
    }
}
</code>

MySQL Connector/Python

Python program uses MySQL‑Connector/Python 8.2.0. Example initialization script:

<code>import mysql.connector

cnx = mysql.connector.connect(user='python',
                              password='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450')
cursor = cnx.cursor()

query = """select member_role, @@port port
            from performance_schema.replication_group_members
            where member_id=@@server_uuid"""
for (role, port) in cursor:
    print("{} - {}".format(role, port))

cursor.close()
cnx.close()
</code>

Running the script prints the primary node:

<code>$ python test_router.py
PRIMARY - 3310
</code>

Because autocommit is disabled by default, the read/write splitting feature does not work until autocommit is enabled.

Enable autocommit

Add the following line before line 8:

<code>cnx.autocommit = True
</code>

After enabling autocommit, the script routes reads to secondary nodes:

<code>$ python test_router.py
SECONDARY - 3320
$ python test_router.py
SECONDARY - 3330
</code>

Query attributes

MySQL Router supports the router.access_mode attribute to force read/write routing. Add before cursor.execute(query) :

<code>cursor.add_attribute("router.access_mode", "read_write")
</code>

Running the script now forces the query to the primary:

<code>$ python test_router.py
PRIMARY - 3310
</code>

Valid values for router.access_mode are auto , read_only , and read_write .

Testing DML statements

Create a test table and insert rows using the read/write ports:

<code>CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `port` int DEFAULT NULL,
  `role` varchar(15) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
</code>

Python script inserts three rows and then reads them back from all nodes:

<code>import mysql.connector

cnx = mysql.connector.connect(user='python',
                              password='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450',
                              database='test')
cnx.autocommit = True
cursor = cnx.cursor()

for i in range(3):
    query = """insert into t1 values(0, @@port, (
          select member_role
            from performance_schema.replication_group_members
            where member_id=@@server_uuid), now())"""
    cursor.execute(query)

cursor.close()
cnx.close()

for i in range(3):
    cnx = mysql.connector.connect(user='python',
                              password='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450',
                              database='test')
    cnx.autocommit = True
    cursor = cnx.cursor()
    query = """select *, @@port port_read from t1"""
    cursor.execute(query)
    for (id, port, role, timestamp, port_read) in cursor:
        print("{} : {}, {}, {} : read from {}".format(id,
                                             port,
                                             role,
                                             timestamp,
                                             port_read))
    cursor.close()
    cnx.close()
</code>

The output shows writes go to the primary (3310) and reads are served by the secondary nodes (3320, 3330).

Transactions

A script demonstrates reads in autocommit, reads inside a transaction, read‑only transactions, and a transaction with multiple inserts followed by a rollback.

<code>import mysql.connector

cnx = mysql.connector.connect(user='python',
                              password='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450',
                              database='test')
cnx.autocommit = True
cursor = cnx.cursor()
# ... (queries omitted for brevity)
cnx.start_transaction()
# insert statements
cnx.rollback()
cursor.close()
cnx.close()
</code>

Running the script shows that the first operation reaches a secondary, the transaction reaches the primary, and the read‑only transaction reaches a secondary. The rollback discards the inserted rows without error.

Conclusion

Using MySQL Connector/Python with MySQL 8.2 read/write splitting in an InnoDB Cluster is straightforward, and the examples demonstrate how to enable autocommit, control routing with query attributes, and work with transactions.