MySQL 8.2 – 事务级别的读写分离
We’ve been all waiting for it! It’s now available! Read/Write Splitting in MySQL !!
我们都在等它！现在可以使用了！在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.
使用MySQL 8.2，MySQL Router 现在能够识别读写，并在InnoDB集群中路由到主实例，或将写到异步复制源，并读到辅助实例或副本。
To illustrate this, I’ve deployed the easiest architecture: MySQL InnoDB ReplicaSet.
为了说明这一点，我部署了最简单的体系结构： MySQL InnoDB复制集。
MySQL InnoDB ReplicaSet
MySQL InnoDB复制集
This is just a replication source instance and one (or more) asynchronous replica:
这只是一个复制源实例和一个（或多个）异步副本：
This is the status of the ReplicaSet object in MySQL Shell:
这是MySQL Shell中的复制集对象的状态：
Bootstrap MySQL Router 8.2
Let’s configure (bootstrap) MySQL Router:
让我们配置（引导）MySQL Router：
We can also see the router in the MySQL Shell ReplicaSet object:
我们还可以在MySQLShell复制集对象中看到router：
Connecting to MySQL using the Read/Write Port (6450):
使用读/写端口（6450）连接到MySQL：
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.
我们可以看到，默认情况下，如果进行读取，我们会到达replica (secondary) ，但如果启动事务，我们将不用更改端口并使用相同的连接而将会读到replication source (primary)主库的数据。
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:
我们可以在MySQL路由器的配置文件中看到为R/W分离生成的设置：
[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
[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=:
您还可以在会话中定义使用命令：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.
总之，MySQL路由器8.2支持读写分离。这对于优化数据库性能和可伸缩性是一个有价值的特性，而不必在应用程序中进行任何更改。
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.
读写实例主库。只读实例是从库(InnoDB集群从库、ReplicaSet从库或在副本集群的第二个实例。
Enjoy MySQL and no more excuse to not spread the workload to replicas !
享受MySQL，不再有借口不将工作负载分散到副本上！
以上博文源于Oracle官方博客：https://blogs.oracle.com/mysql/post/mysql-82-transparent-readwrite-splitting
