MySQL 8.2 Introduces Built-in Read/Write Splitting via MySQL Router and InnoDB ReplicaSet

MySQL InnoDB ReplicaSet

The article demonstrates the simplest topology for the new feature: a single replication source (primary) and one or more asynchronous replicas. This is represented as a MySQL InnoDB ReplicaSet in MySQL Shell. The ReplicaSet status shows the primary and secondary instances with their roles and replication lag.

Starting MySQL Router 8.2

MySQL Router 8.2 is bootstrapped against the ReplicaSet. The router discovers the topology from the metadata cache and creates a read/write split port (default 6450). The generated configuration section [routing:bootstrap_rw_split] includes:

[routing:bootstrap_rw_split]
bind_address=0.0.0.0
bind_port=6450
destinations=metadata-cache://myreplica/?role=PRIMARY_AND_SECONDARY
routing_strategy=round-robin
protocol=classic
connection_sharing=1
client_ssl_mode=PREFERRED
server_ssl_mode=PREFERRED
access_mode=auto

The key settings are access_mode=auto (enables automatic read/write detection) and destinations pointing to both PRIMARY_AND_SECONDARY roles.

Connection Behavior

When connecting through the read/write port (6450):

Simple SELECT statements are routed to a replica (round-robin among secondaries).

Starting an explicit transaction ( START TRANSACTION) routes the session to the primary, even for reads, to ensure consistency.

Using START TRANSACTION READ ONLY keeps the session on a replica for read-only workloads.

The session variable ROUTER SET access_mode= can override routing per session (values: READ_WRITE, READ_ONLY, AUTO).

Screenshots in the article show MySQL Shell output confirming the active connection target (primary vs secondary) under each scenario.

Conclusion

MySQL Router 8.2's read/write splitting eliminates the need for application-level routing logic. All read traffic can be directed to read-only replicas while writes go to the primary, improving performance and scalability. The feature works with InnoDB Cluster ReplicaSet, ReplicaSet secondaries, or replica cluster secondaries. No application code changes are required; only the connection endpoint changes to the router's read/write port.