How MySQL 8.2 Router Enables Seamless Read/Write Splitting

Introduction

Oracle recently announced that MySQL 8.2 is generally available and includes native support for read/write splitting. This feature lets applications direct all write traffic to a primary (read‑write) instance while routing read traffic to read‑only replicas, improving performance and scalability.

Frederic Descamps (MySQL Community Manager) explains that the router now can detect read and write traffic and route it to the appropriate InnoDB Cluster primary or to an asynchronous source for writes and to secondary instances for reads.

Peter Zaitsev (Founder, Percona) notes that while the feature is significant, it is unclear what consistency guarantees reads have—whether stale data may be returned and for how long. He also mentions that the default read/write port (6450) connects to replicas for reads and to the source for writes, with transaction behavior affecting routing.

Read/Write Splitting Overview

In large‑scale deployments, applications traditionally split read and write traffic manually. MySQL 8.2’s Router automates this by classifying each query as read or write and sending it to the appropriate backend without requiring application changes.

MySQL InnoDB ReplicaSet Example

The author demonstrates the simplest architecture: a MySQL InnoDB ReplicaSet consisting of one source instance and one or more asynchronous replicas.

The following screenshot shows the ReplicaSet object status in MySQL Shell.

Bootstrapping MySQL Router 8.2

To start the router, run the bootstrap command:

mysqlrouter --bootstrap [email protected]:3306 --user mysqlrouter

The router appears in the MySQL Shell ReplicaSet object.

Connecting to the read/write port (6450) shows that reads go to a replica, while starting a transaction forces the connection to the primary source.

A separate screenshot illustrates the difference when using a read‑only transaction.

Router Configuration for R/W Split

The generated configuration file contains a routing section that implements the 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

During a session you can explicitly set the access mode with the ROUTER SET access_mode= command to force routing to a specific instance type.

Conclusion

MySQL Router 8.2 fully supports read/write splitting, allowing databases to scale and perform better without any code changes in the application. The configuration directs all reads to read‑only instances and all writes to the primary, simplifying deployment and improving user experience.

This setup lets you route all read traffic to replicas and all write traffic to the primary.

The feature enhances overall user experience and streamlines database management.

Read‑write instances are primary or source instances; read‑only instances are replicas (secondary members of an InnoDB Cluster, ReplicaSet, or Replication Cluster).