MySQL 8.2 Transparent Read/Write Splitting with MySQL Router

We have been waiting for MySQL's read/write splitting feature, and it is finally available in MySQL 8.2.

At scale, reads are distributed among replicas, but this traditionally required application‑level management to direct writes to the primary and reads to the replicas.

In MySQL 8.2, MySQL Router can automatically recognize read and write operations and route them to the appropriate instance—either the primary of an InnoDB Cluster or the asynchronous source for writes, and to secondary or replica instances for reads.

To illustrate, a minimal architecture was deployed using a MySQL InnoDB ReplicaSet, which consists of a single source instance and one or more asynchronous replicas.

The above image shows the status of the ReplicaSet object in MySQL Shell.

1 MySQL InnoDB ReplicaSet

This is simply a replication source instance and one (or more) asynchronous replicas.

2 Start MySQL Router 8.2

We configure and start MySQL Router as shown below.

The Router also appears in the MySQL Shell ReplicaSet object.

Connecting to MySQL using the read/write port (6450) shows that read operations are automatically directed to a replica, while a transaction start routes to the source (primary) without changing the port.

The difference when using a read‑only transaction is also visible.

The generated read/write splitting configuration appears in MySQL Router's configuration file:

[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 use the command ROUTER SET access_mode= in a session to define which instance type to access.

3 Conclusion

In summary, MySQL Router 8.2 supports transparent read/write splitting, a valuable feature that can improve database performance and scalability without any application changes.

With this configuration, all read traffic is directed to read‑only instances, and all write traffic to the read/write (primary) instance.

This capability enhances overall user experience and simplifies database management and deployment.

The read/write instance is the primary or source instance; the read‑only instances are replicas (InnoDB Cluster ReplicaSet, ReplicaSet secondary instances, or secondary instances in a replica cluster).

References

[1] Read/Write Splitting: https://dev.mysql.com/doc/mysql-router/8.2/en/router-read-write-splitting.html

[2] MySQL 8.2 Release Notes: https://dev.mysql.com/doc/relnotes/mysql/8.2/en/