Using MySQL Router 8.2 for Read/Write Splitting with InnoDB ReplicaSet

MySQL 8.2 finally provides a built‑in read/write splitting feature that can be leveraged through MySQL Router, allowing applications to direct read traffic to replicas and write traffic to the primary instance automatically.

The simplest setup illustrated is an InnoDB ReplicaSet consisting of a single source instance and one or more asynchronous replicas. This topology is visualized with diagrams and the ReplicaSet status is displayed in MySQL Shell.

To activate the feature, MySQL Router 8.2 is started and configured. The article shows the Router startup process and the resulting Router entry that appears in the ReplicaSet object within MySQL Shell.

When connecting to the Router on the default read/write port 6450 , read operations are routed to a replica while write operations (or transactions that require writes) are routed to the primary source, all without changing the client connection string.

The behavior of read‑only transactions is also demonstrated, highlighting the difference in routing when a transaction is marked as read‑only.

The generated routing configuration can be seen in the Router's configuration file, for example:

[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

Additionally, the article explains how to change the access_mode for a session using the ROUTER SET command, allowing explicit control over which instance type (primary or secondary) is accessed.

In conclusion, MySQL Router 8.2’s read/write splitting is a valuable feature that optimizes database performance and scalability, directing all read traffic to read‑only instances and all write traffic to the primary instance, without requiring any changes to the application code.