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:

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

Bootstrap MySQL Router 8.2

Let’s configure (bootstrap) MySQL Router:

mysqlrouter --bootstrap root@127.0.0.1:3306 --user mysqlrouter

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

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

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:

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

[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 also have the possibility to define in your session which type of instance you want to reach using the command ROUTER SET access_mode=:

Conclusion

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 !