MySQL Router 8.2 Read/Write Splitting with InnoDB ReplicaSet

We have been waiting for the MySQL read/write splitting feature, and it is finally available.

At scale, reads are distributed among replicas, which normally requires application‑level management to direct writes to a source and reads to other nodes.

In MySQL 8.2, MySQL Router can recognize read and write traffic and route writes to the primary instance (or async source in an InnoDB Cluster) and reads to secondary instances or replicas.

To illustrate this, I deployed the simplest architecture: a MySQL InnoDB ReplicaSet.

MySQL InnoDB ReplicaSet

This consists of a source instance and one (or more) asynchronous replicas:

This is the status of the ReplicaSet object in MySQL Shell:

Starting MySQL Router 8.2

Let’s bootstrap MySQL Router:

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

We can also see the router in the MySQL Shell ReplicaSet object:

Connect to MySQL using the read/write port (6450):

By default, read operations connect to a replica (or non‑primary instance), while starting a transaction forces a connection to the source (primary); this switch happens automatically within the same connection and requires no port changes.

We can also see the difference when using read‑only transactions:

The generated R/W split settings appear in the MySQL Router 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 define the instance type to access in a session with the command ROUTER SET access_mode= :

Conclusion

MySQL Router 8.2 supports read/write splitting. This is a valuable feature for optimizing database performance and scalability without any changes to the application.

This configuration directs all read traffic to read‑only instances and all write traffic to read/write instances.

The feature not only enhances overall user experience but also simplifies database management and deployment.

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

Enjoy MySQL—no more excuses for not distributing workload to replicas!