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

MySQL InnoDB ReplicaSet

The new MySQL read/write splitting feature is finally available. In a large‑scale deployment you normally distribute reads across replicas while writes go to the primary, but the application must manage this routing. MySQL 8.2 introduces MySQL Router support that automatically distinguishes read and write traffic and routes it accordingly.

To illustrate the feature, a minimal architecture is deployed: a single InnoDB ReplicaSet consisting of one source instance and one (or more) asynchronous replicas.

The ReplicaSet object status can be inspected from MySQL Shell, showing the source and replica instances.

Start MySQL Router 8.2

Next, configure and launch MySQL Router to enable read/write split.

The Router appears as a member of the ReplicaSet in MySQL Shell.

Clients connect to the Router using the read/write port (6450). By default, read operations are sent to a replica, while write operations (including transactions) are sent to the source instance without changing the connection parameters.

When a read‑only transaction is used, the Router still routes reads to replicas, demonstrating the split behavior.

The generated Router configuration file contains the read/write split settings:

[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

During a session you can explicitly set the instance type with the ROUTER SET access_mode= command.

Conclusion

MySQL Router 8.2 fully supports read/write splitting, allowing all read traffic to be automatically directed to read‑only instances and all write traffic to the primary instance. This capability improves database performance and scalability without requiring any changes to application code, simplifying deployment and management.

The read/write instances correspond to the primary (source) instance, while the read‑only instances are the replicas within an InnoDB Cluster ReplicaSet or its auxiliary nodes.