Using MySQL 8.2 Read/Write Splitting with MySQL Connector/Python

1 Architecture

To use the Python program we rely on an InnoDB Cluster. The article shows how to query the cluster status and list routers using MySQL Shell.

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",
                "status": "ONLINE",
                "version": "8.2.0"
            },
            "127.0.0.1:3320": {
                "address": "127.0.0.1:3320",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "status": "ONLINE",
                "version": "8.2.0"
            },
            "127.0.0.1:3330": {
                "address": "127.0.0.1:3330",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "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"
        }
    }
}

2 MySQL Connector/Python

The Python script uses MySQL‑Connector/Python 8.2.0 to connect to the router port (6450). The initial test prints the role and port of the node it reaches.

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()

Running the script initially connects to the primary instance because autocommit is disabled. Enabling autocommit fixes the routing.

cnx.autocommit = True

After setting autocommit, repeated executions show connections to the secondary instances (ports 3320 and 3330).

3 Query Attributes

MySQL Router allows forcing read/write routing via the router.access_mode attribute. Adding the attribute before executing a query can direct the request to the primary.

cursor.add_attribute("router.access_mode", "read_write")

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

4 Testing DML Statements

A table t1 is created and rows are inserted using the router. The script inserts three rows and then reads them back, demonstrating that writes go to the primary while reads are served by the secondary nodes.

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;

Insertion script (simplified):

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()

Reading back the rows confirms that the port_read column reflects the secondary node that served the read.

5 Transactions

The article shows four transaction scenarios: autocommit reads, read/write transaction (default), read‑only transaction, and a transaction with multiple inserts followed by a rollback. The output demonstrates which node each operation reaches.

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()
# Autocommit read
cursor.execute("select member_role, @@port port from performance_schema.replication_group_members where member_id=@@server_uuid")
for role, port in cursor:
    print(f"{role} - {port}")
# Transaction read/write
cnx.start_transaction()
cursor.execute("select member_role, @@port port from performance_schema.replication_group_members where member_id=@@server_uuid")
for role, port in cursor:
    print(f"{role} - {port}")
cnx.commit()
# Read‑only transaction
cnx.start_transaction(readonly=True)
cursor.execute("select member_role, @@port port from performance_schema.replication_group_members where member_id=@@server_uuid")
for role, port in cursor:
    print(f"{role} - {port}")
cnx.commit()
# Transaction with inserts and rollback
cnx.start_transaction()
for i in range(3):
    cursor.execute("""insert into t1 values(0, @@port, (
              select member_role
              from performance_schema.replication_group_members
              where member_id=@@server_uuid), now())""")
cnx.rollback()
cursor.close()
cnx.close()

The results show that reads in autocommit go to a secondary, the explicit transaction goes to the primary, a read‑only transaction reaches a secondary, and rollbacks do not produce errors.

6 Conclusion

Integrating MySQL Connector/Python with MySQL 8.2's read/write splitting in an InnoDB Cluster is straightforward, enabling developers to direct queries to the appropriate node and manage transactions effectively.

References

Read/write splitting: https://blogs.oracle.com/mysql/post/mysql-82-transparent-readwrite-splitting

Connector/Python documentation: https://dev.mysql.com/doc/connector-python/en/

autocommit property: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-autocommit.html