Detailed explanation of the basic principles of MySQL read and write separation

This article brings you relevant knowledge about mysql, which mainly organizes issues related to the basic principles of read-write separation, including the working principle of read-write separation, the implementation method of read-write separation, Commonly used middleware for reading and writing separation, etc., let’s take a look at them together. I hope it will be helpful to everyone.

## Recommended learning:

mysql video tutorial

1. Working principle of read-write separation

In some large websites In business scenarios, the amount of concurrency provided by a single database server can no longer meet business needs. In order to meet this situation, data is generally synchronized through master-slave synchronization. On this basis, read-write separation is used to synchronize data. Improve the concurrency and load capabilities of the database.

Generally speaking, query operations on the database in business scenarios are much higher than adding, deleting, and modifying, and read operations have less impact on the database. Therefore, we usually set up one database server as the master server, which is mainly responsible for the tasks of adding, deleting and modifying data, and configure 3-4 database servers as slave servers, which are mainly responsible for data query tasks. The database slave server synchronizes data from the database master server to achieve data consistency.

2. Implementation methods of read-write separation

According to the level of read-write separation implementation, read-write separation is generally implemented in two ways, through the application layer and through the middleware layer.

Implementation through the application layer refers to implementing the data query language and data operation language inside the web page to point to different MySQL main libraries and slave libraries respectively. The diagram of MySQL read-write separation implemented through the application layer is as follows:

The advantage of this is that it reduces the difficulty of deployment, the deployment and installation are ready to use, and the performance is better. The disadvantage is that when the architecture is expanded, To modify the code, it is difficult to implement advanced operations such as automatic database sharding and table sharding, which is not very suitable in some large-scale application scenarios.
Implementation through the middleware layer means that all SQL statements are uniformly pointed to a middleware device at the application layer, and the middleware device points different SQL statements to different database servers for operations. The diagram of realizing read-write separation through the middleware layer is as follows:

The advantage of this is that the architecture design is more flexible, has less impact on business code, has good portability, and can achieve advanced control, but this This kind of architecture requires professional operation and maintenance personnel to maintain the architecture.

3. Commonly used middleware for reading and writing separation

Commonly used middleware programs for reading and writing separation are as follows:

1, cobar Alibaba B2B development The relational distributed system is an early middleware that was later left unmaintained due to the departure of the developer.

2. MyCAT Technology enthusiasts have carried out secondary development on the basis of cobar, solved some problems of cobar, and added some new functions. Currently, the MyCAT community is highly active. , there are also many companies using MyCAT.

3. OneProxy Oneproxy is a commercial paid middleware developed by the Alipay team and is very stable in high concurrency scenarios.

4. Vitess The middleware architecture is complex, and using Vitess requires the use of the API interface provided by it.

5. Kingshard Developed by the 360 ​​team, it supports sub-databases and tables, but its stability is average under high concurrency conditions.

6, MaxScale and MySQL Route Both are MySQL official middleware. Maxscale was developed by Mariadb, and MySQL Route is the middleware currently released by Oracle for the MySQL database.

Recommended learning:

mysql video tutorial

The above is the detailed content of Detailed explanation of the basic principles of MySQL read and write separation. For more information, please follow other related articles on the PHP Chinese website!