Enable MySQL Router 8.2 Read/Write Splitting with InnoDB ReplicaSet

MySQL InnoDB ReplicaSet

The new MySQL Router 8.2 feature recognizes read and write operations and routes them to the appropriate instance, supporting InnoDB Cluster or asynchronous replica sources. To illustrate, a minimal architecture is deployed consisting of a single source instance and one or more async replicas.

This diagram shows the ReplicaSet object status in MySQL Shell.

Starting MySQL Router 8.2

Configure and start MySQL Router using the following steps. The router appears in the MySQL Shell ReplicaSet object after initialization.

Connecting via Read/Write Port

Use the read/write port (6450) to connect to MySQL. By default, read operations are directed to replicas, while starting a transaction routes writes to the primary source without changing the port.

When using a read‑only transaction, the routing behavior differs, as shown below.

Router Configuration File

The generated MySQL Router configuration includes 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

You can also define the instance type to access in a session with the command:

ROUTER SET access_mode=

Conclusion

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

Author: 爱可生开源社区 Source: juejin.cn/post/7295304057656918026