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

MySQL InnoDB ReplicaSet

A ReplicaSet consists of a single source (primary) instance and one or more asynchronous replica instances, providing read scalability while keeping data synchronized.

The MySQL Shell displays the status of the ReplicaSet object, confirming the primary and replica roles.

Starting MySQL Router 8.2

Configure and launch MySQL Router to enable read/write split routing. The router is started with the default read/write port 6450.

After starting, the router appears as a member of the ReplicaSet object in MySQL Shell.

Connecting to the router on port 6450 routes read operations to replicas and write operations to the primary. Starting a transaction automatically targets the primary, while read‑only transactions stay on replicas.

Router Configuration for Read/Write Split

[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 generated configuration file shows the read/write split settings. You can also change the access mode for a session 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 feature improves database performance and scalability without requiring any changes to the application code.