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, we distribute reads among replicas, which must be managed in the application: writes go to one place and reads to another. In MySQL 8.2, MySQL Router can now recognize read and write traffic and route them to the primary instance (for InnoDB clusters) or to the async replication source for writes, and to secondary instances or replicas for reads.

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

MySQL InnoDB ReplicaSet

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

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

Start MySQL Router 8.2

Let's configure (start) MySQL Router:

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

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

By default, a read operation accesses a replica, while starting a transaction routes to the source (primary) without changing the port, using the same connection.

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

The generated read/write splitting settings can be seen 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 define the instance type to access in a session with the command ROUTER SET access_mode= :

Conclusion

In summary, MySQL Router 8.2 supports read/write splitting. This valuable feature can optimize database performance and scalability without any changes to the application.

With this configuration, you can direct all read traffic to read‑only instances and all write traffic to read/write instances.

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

Read/write instances are primary or source instances. Read‑only instances are replicas (InnoDB Cluster ReplicaSet, ReplicaSet secondary instances, or secondary instances in a replica cluster).

Source: juejin.cn/post/7295304057656918026

Backend Exclusive Technical Group

Build a high‑quality technical community; developers, recruiters, and anyone interested are welcome to join, share referrals, and help each other improve.

Speak civilly, focusing on technical exchange , job referrals , and industry discussion .

Advertisers stay out, do not trust private messages, avoid scams.