What is the principle of MySQL read and write separation?

1. The concept of read-write separation

Read-write separation is implemented based on master-slave replication. In an actual application environment, there must be a lot of read operations. Just like when we buy something on an e-commerce platform, we may only buy one or two after looking at 100 items. Therefore, there are always many more read operations than write update operations. Therefore, our read-write separation configuration based on master-slave replication means that a master database is dedicated to modifying data. When writing, it is written exclusively on the master database. The master database synchronizes data changes to the slave through master-slave replication through binlog. library, then other client query requests will eventually be mapped to the slave library, and we have one main library with two or three slave libraries. The main library is specially used for data updates (write operations), and the slave library The library is specially used for read operations. This way, the pressure of reading and writing can be well distributed. It does not need to be concentrated on the main library. It greatly improves the concurrent processing capabilities of back-end services. In addition, it has high availability capacity. In case of disaster, when the main database fails, the designated slave database can be turned into the main database.

MySQL client communicates with MySQL Server through the API provided by mysql and uses mysql's customized TCP-based data protocol (referred to as the mysql protocol) to access the MySQL Server database. .

If there is only one server (stand-alone environment), all data additions, deletions, modifications and queries are performed on one server. As our services are used by more and more people, the traffic gradually increases. Concurrency capabilities need to be gradually improved, so if we find that database performance has reached a bottleneck, we can perform read-write separation operations to improve background services.

The MySQL main server in the picture is dedicated to write operations. There are two MySQL slave servers connected below to specifically perform read operations. Read requests are forwarded to B and C, and write requests are forwarded. to A.

If we write code directly on the client, write operations such as insert and update are done on A, and read operations such as show and select are done on B and C. This is equivalent to the code and master-slave environment. Strongly bound. This leads to poor stability of the code, because it is strongly related to the environment. When we write code, we must know which machine is the main library responsible for writing operations, and which machine is the slave library responsible for reading operations. The code chooses . At this time, if a certain machine hangs up, the code will not know it and will still forward the request in the original way, and communication problems will occur. Therefore, it is definitely not appropriate to implement the separation of reading and writing in code. So in the actual solution, reading and writing separation needs to rely on the middleware of the database.

2. Introduce the middleware MyCat

In fact, the separation of reading and writing, and sub-database and table sub-reliance need to rely on the database middleware (mycat), which is the role of the proxy server.

The client cannot actually tell whether it is connected to MyCat or MySQL, because the communication follows the MySQL communication protocol. How to communicate with MySQL before, how to communicate with MySQL now? MyCat communicates, so there is no need to distinguish.

Configuring read-write separation on MyCat, we do not need to make any changes to the code on the client. There is no need to distinguish which request is for reading and which is for writing in the code. The code accesses directly The one is MyCat, which parses the request and forwards it to the server responsible for the corresponding operation according to the read and write nature of SQL to achieve read and write separation.

You need to configure the information of the master server and slave servers on MyCat. There are three situations: One master and one slave, one master and multiple slaves, and multiple masters and multiple slaves

One master and multiple slaves scenario: When the writing library (master) hangs up, MyCat can also immediately turn a slave library (slave) directly into a writing library (master), which is equivalent to returning to a writing library (master). For machine processing, because there is no master-slave replication configuration between the slave library and the slave library, we also need to configure the master-slave replication between the slave library that becomes the writing library and other slave libraries.

Multiple masters and multiple slaves:

As you can see in the picture, the MyCat server has two sets of environments. If one of them The main database is down (its corresponding slave database is also unavailable). At this time, MyCat will automatically switch to another environment, because M1 and M2 are also configured as mutual masters and slaves, so M2 can synchronize the data of M1 and provide exactly the same services as the M1 environment, so its high availability and disaster recovery capabilities are very good.

3. MyCat service port and management port

MySQL service port is 3306, MyCat service port is 8066 (this port can also be changed), which is MySQL The Client connects to port 8066. The interface you see when logging in to port 8066 is the same as logging in to port 3306 of MySQL Server. MyCat also has a management port 9066. You can log in to this management port to view all the status of MyCat's work and the connection to the back-end server, as well as the status of the connection to the data source.

The above is the detailed content of What is the principle of MySQL read and write separation?. For more information, please follow other related articles on the PHP Chinese website!