Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to realize MySQL read-write separation". In daily operation, I believe many people have doubts about how to realize MySQL read-write separation. Xiaobian consulted all kinds of materials and sorted out simple and easy operation methods. I hope to help you answer the doubts of "how to realize MySQL read-write separation"! Next, please follow the small series to learn together!

With the development of business volume and the growth of data volume, it is difficult for a stand-alone version of the database to meet business needs. The most common performance expansion method of MySQL is "read and write separation," which essentially distributes the access pressure to multiple MySQL nodes, but the storage pressure is not distributed."Separate database and table" can distribute the access and storage pressure, but the implementation difficulty and maintenance cost will increase greatly.

▲ Read-write separation architecture ▲

A master-slave cluster architecture is required, with one master and one slave or one master and multiple slaves;

The master library responds to write requests and the slave library responds to read requests.

The master database synchronizes data to the slave database through replication, and each node stores a copy of business data.

There are two common ways to implement MySQL's "read-write separation" in the industry, client implementation and proxy implementation. The two methods have their own advantages and disadvantages, and need to be selected and implemented in combination with their respective business characteristics, system architecture and operation and maintenance capabilities. Because the introduction of agents will make the system architecture more complex, and the agents themselves have to consider factors such as performance, high availability, stability, etc., small and medium-sized companies generally use client-side methods to achieve read-write separation. Large companies have the resources to invest in database proxy development and maintenance, and the more business they access, the more significant the value.

First of all, the client implementation method, for example, java language can be based on MyBatis and Hibernate simple encapsulation, you can achieve read-write separation, the architecture diagram is as follows:

The proxy implementation method requires the introduction of a database proxy to achieve read-write separation and database management functions. The proxy is compatible with the SQL protocol. For the client, the proxy is the database. The architecture diagram is as follows:

Compare the advantages and disadvantages of the two architectures:

Regardless of the architecture, the implementation logic is not complicated, but there is one detail that needs special attention: MySQL master-slave replication latency.

MySQL replication is asynchronous, and although there are responsive optimizations for different causes of latency, latency is objective and impossible to avoid 100 percent. When there is a large delay in read-only, there will be data inconsistency. The data read is before the submission of the master database, which is called "overdue read." At this time, business problems may occur. Common solutions include:

Core services read and write are directed to the main library, while non-core services use read and write separation. For example, financial services sacrifice scalability in order to ensure data consistency. For example, forum business, post will be late to some inquiries, it will bring a lot of impact will be small, is tolerable;

Read twice, check again after reading failure, it has nothing to do with business logic, only need to re-encapsulate the database access layer, the implementation cost is small. However, in some scenarios, it expands the number of read requests and increases the pressure on the database, such as DDoS attacks, which can easily crush the database.

jud whether there is delay before read that slave library, querying the slave library if there is no delay, and querying the master library if there is delay. MySQL itself provides a monitoring method for determining delay. If GTID is enabled, directly compare Retrieved_Gtid_Set and Executed_Gtid_Set values of slave library. If they are the same, it means there is no delay from slave library. If GTID is not enabled, compare two sets of values. Master_Log_File and Relay_Master_Log_File, Read_Master_Log_Pos and Exec_Master_Log_Pos are exactly the same, which means there is no delay from slave library.

Jingdong Zhilian Cloud RDS-MySQL supports the function of "read-write separation agent." After creating a read-only instance, open the read-write separation agent, and you can configure the address of the read-write agent in the program. The read-write agent realizes that the read request is automatically forwarded to the read-only instance, and the write request is automatically forwarded to the master instance. Currently MySQL 5.6 -8.0, Percona and Mariadb support enabling read-only proxies. For more information, see docs.jdcloud.com/cn/rds/create-readwriteproxy

The following describes the fundamentals and best practices from both the data and control sides of the feature.

When creating a read/write separation agent, users configure parameters such as read-only library delay, health check, and Load Balancer policy to achieve read/write separation according to user business characteristics.

The creation interface of Jingdong Zhilian Cloud "Read-write Separation Agent" is as follows:

Read-only instance latency threshold: Read-only instances are removed from the read-write proxy backend when the latency between the read-only instance and the primary instance exceeds a threshold.

Load Balancer Policy: A Load Balancer policy used by read/write agents to distribute read/write requests. It supports minimum active connections, minimum connections, minimum synchronization delay, and average response delay.

Connection timeout: read-only instance health check method. Read-only instances are removed from the read-write proxy backend after timeout.

Repeat count: read-only instance health check method. After more than the specified number of retries, the read-only instance health check fails and is removed from the read-write proxy backend.

Check Interval: A method of health checking read-only instances, which performs health checks on read-only instances on the read-write proxy backend according to time intervals.

Best Practice: The read-only instance latency threshold is configured based on business characteristics, and the default time is 100s, which is strongly recommended to be greater than the check interval time.

The RDS console allows you to manage the entire lifecycle of the Read-Write Separation Agent, including creating, modifying configuration, deleting, and enabling public network access. In addition to this, the Read-Write Separation Agent is aware of high availability switching for forwarding MySQL instances, modification of configuration flows, and creation and deletion of read-only instances.

High availability switch: when the MySQL forwarded switches from master to slave, the "read/write separation agent" will automatically adjust the write request to be forwarded to the new master database;

Modify the configuration. When the forwarded MySQL master-slave instance or read-only instance triggers the business process of capacity expansion or reduction, the Read-Write Separation Agent will automatically adjust and forward the read-write request to the new instance.

For the creation and deletion of read-only instances, the Read-Write Separation Agent automatically forwards requests to new read-only instances according to the Load Balancer algorithm after users add new read-only instances; after users delete read-only instances, the Read-Write Separation Agent automatically stops forwarding requests to deleted read-only instances.

At this point, the study of "how to realize MySQL read-write separation" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.