MySQL Router 8.2 Read/Write Splitting with InnoDB ReplicaSet

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

MySQL InnoDB ReplicaSet

A ReplicaSet consists of a primary source instance and one or more asynchronous replicas.

The following image shows the ReplicaSet object status in MySQL Shell:

Starting MySQL Router 8.2

Let's configure and start MySQL Router:

The Router appears in the ReplicaSet object in MySQL Shell:

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

By default, read operations are routed to replicas, while write operations (or transactions) are routed to the primary source without changing the port.

The configuration generated by MySQL Router for read/write splitting can be seen in its 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 set the access mode for a session with the command ROUTER SET access_mode= to explicitly choose the instance type.

Conclusion

MySQL Router 8.2 supports read/write splitting, allowing you to direct all read traffic to read‑only instances and all write traffic to the primary instance, improving performance and scalability without any application changes.

This feature enhances user experience, simplifies database management, and clarifies that read‑only instances are replicas while write instances are the primary source.

Note: The article also contains promotional material for ChatGPT services, a paid community, and other unrelated offers.