MySQL Blog Archive
For the latest blogs go to
MySQL 8.2 – transparent read/write splitting

We’ve been all waiting for it! It’s now available! Read/Write Splitting in MySQL !!

At scale, we distribute reads between replica(s), but this has to be managed somehow in the application: pointing writes somewhere and reads somewhere else.

With MySQL 8.2, MySQL Router is now able to identify reads and writes and route them to Primary Instances in the case of an InnoDB Cluster, or to an asynchronous replication source for the writes and to secondary instances or replicas for the reads.

To illustrate this, I’ve deployed the easiest architecture: MySQL InnoDB ReplicaSet.

MySQL InnoDB ReplicaSet

This is just a replication source instance and one (or more) asynchronous replica:

MySQL InnoDB ReplicaSet

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

MySQL InnoDB ReplicaSet Status

Bootstrap MySQL Router 8.2

Let’s configure (bootstrap) MySQL Router:

MySQL Router Bootstrap

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

ReplicaSet's Router List

Connecting to MySQL using the Read/Write Port (6450):

Testing Read/Write Splitting

We can see that by default we reach the replica (secondary) if we do a read, but if we start a transaction, we reach the replication source (primary) without changing the port and using the same connection.

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

Read Only Transaction Example

And we can see in the MySQL Router’s configuration file the generated settings for the R/W splitting:


You also have the possibility to define in your session which type of instance you want to reach using the command ROUTER SET access_mode=:

Session changes


In conclusion, MySQL Router 8.2 supports Read-Write splitting. This is a valuable feature for optimizing database performance and scalability without having to make any changes in the application.

This configuration enables you to 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 primaries or sources. Read-only instances are replicas (InnoDB Cluster secondaries, ReplicaSet secondaries or secondary instances in a Replica Cluster.

Enjoy MySQL and no more excuse to not spread the workload to replicas !