Enable MySQL Read/Write Splitting with Router 8.2 and InnoDB ReplicaSet

Background

MySQL 8.2 finally provides built‑in read/write splitting, eliminating the need for application‑level routing logic. The new MySQL Router can detect the type of operation and direct reads to secondary instances while sending writes to the primary source (or InnoDB Cluster primary).

Demo Architecture: InnoDB ReplicaSet

The demonstration uses the simplest possible setup—a MySQL InnoDB ReplicaSet consisting of one source instance and one or more asynchronous replicas.

The ReplicaSet status can be inspected via MySQL Shell, which shows the source and its replicas.

Starting MySQL Router 8.2

After deploying the ReplicaSet, start MySQL Router. The Router appears as an object in the MySQL Shell ReplicaSet view.

Connect to the Router using the read/write port (6450). By default, read operations are routed to a replica, while write operations (including the start of a transaction) are sent to the source.

When a read‑only transaction is used, the Router continues to send queries to the replica, demonstrating true read‑only routing.

Router Configuration

[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 access_mode can be overridden per session using the ROUTER SET access_mode= command, allowing explicit selection of primary or secondary instances.

Conclusion

MySQL Router 8.2 fully supports read/write splitting for InnoDB ReplicaSet deployments. By configuring a single Router instance, all read traffic is automatically directed to read‑only replicas while write traffic goes to the primary source, improving performance and scalability without any changes to the application code.