Use MySQL proxy to resolve MySQL master-slave synchronization delay

MySQL's master-slave synchronization mechanism is very convenient to solve the high concurrent read application requirements, the development of the web has brought great convenience. However, there is a big flaw in this way is that the synchronization mechanism of MySQL relies on the slave initiative to master request to obtain data, and due to server load, network congestion and other reasons, master and slave between the data synchronization delay is completely not guaranteed. Short in 1 seconds, the length is a few seconds, a few 10 seconds or even longer is possible.

due to the existence of data latency issues, when an application updates data on master and then immediately needs to read data from the database, if the application fetches data from slave (which is also the general practice of current web development), it is possible to read data that is not expected to occur. Causes the program to run abnormally.

There are several ways to solve this problem, such as simply forcing sleep for a few seconds after all inserts and update. This is a very rude way, and for small and medium-sized systems where updates are not very high, this approach will basically solve the problem.

Another way is for the application to keep the updated data in its native memory (or in a centralized cache) and read from native memory if it needs to read the data directly after the write data is complete. The disadvantage of this approach is that the complexity of the application is greatly increased, and the reliability is not fully guaranteed.

the use of MySQL proxy can be very convenient to solve this problem. MySQL Proxy is based on the agent between MySQL client and MySQL server, can complete the client request monitoring, modification. From the client point of view, there is no difference between accessing the server through a proxy and accessing the server directly. For the existing program, as long as the direct access to the server's IP address and port number to proxy IP address and port number can be.

MySQL Proxy works more easily. At proxy startup, you can specify the LUA script that proxy needs to use, and implement 6 methods in the Lua script beforehand:

* Connect_server ()//call when receiving a connection request from the client
* Read_handshake ()//
* Read_auth ()//call when reading the authentication information of the client
* Read_auth_result ()//call when the authentication result is read
* Read_query ()//call when reading the client's query request
* Read_query_result ()//call when reading query results

When a proxy receives a client request, the different methods above are called at different stages of the request. In this way, proxy users can freely implement these 6 methods to achieve their goals according to their own business needs.

by adding code to Read_query (), we can intercept whether the current request is an INSERT, update, or SELECT, then send the INSERT and update request to master and send the select request to Slave. This solves the problem of read and write separation.

after resolving the read-write separation, how to solve the synchronization delay?

by adding a self-increment table to master, this table contains only 1 fields. When Master receives a request for any data updates, it triggers the trigger, which updates the records in the self-increment table. As shown in the following:

Mysql_proxy_write

because Count_table also participates in MYSQ's master-slave synchronization, update updates made on master are also synchronized to slave. When the client through proxy for data read, proxy can first send query request to master and slave count_table table, when the data of both, proxy can be determined that master and slave data state is consistent, The select request is then sent to the slave server, otherwise it is sent to master. As shown in the following:

Mysql_proxy_read

In this way, it is possible to compare the results of the MySQL synchronization delay to an uncontrolled problem. The reason is "perfect" because this scheme doubles the query request, which poses additional pressure on master and slave. However, because proxy is connected to the real MySQL server using a connection pool, additional stress is acceptable.

Use MySQL proxy to resolve MySQL master-slave synchronization delay