How MySQL Router 8.2 Enables Seamless Read/Write Splitting with InnoDB ReplicaSet

MySQL InnoDB ReplicaSet

A ReplicaSet consists of a single source (primary) instance and one or more asynchronous replica instances. The primary handles all write operations, while replicas serve read requests, enabling horizontal scaling of read workloads.

The MySQL Shell shows the status of the ReplicaSet object, confirming the presence of the source and its replicas.

Start MySQL Router 8.2

MySQL Router 8.2 can detect read and write traffic and route it accordingly. When a read operation is issued, Router forwards the request to a replica; when a write or a transaction is started, Router directs the request to the primary source.

The Router appears as a member of the ReplicaSet object in the MySQL Shell, confirming that it has been registered.

Connecting to MySQL via the read/write port (6450) demonstrates the routing behavior: a simple SELECT hits a replica, while starting a transaction automatically switches to the primary.

Read‑only transactions show the same routing logic, confirming that Router respects transaction boundaries.

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

At runtime you can override the target instance type for a session using the ROUTER SET access_mode= command, allowing explicit control over whether reads go to primary or replica.

Conclusion

MySQL Router 8.2 fully supports read/write splitting for InnoDB ReplicaSet deployments. This feature improves database performance and scalability by automatically directing read traffic to read‑only replicas and write traffic to the primary instance, all without requiring any changes to the application code.

The approach simplifies database management, enhances user experience, and provides a clear separation between read‑only and read/write workloads.