Use MySQL proxy to resolve MySQL master-slave synchronization delay

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

Due to the existence of data latency problems, when an application is updating data on master and then immediately needs to read data from the database, when the application fetches data from the slave (which is also the normal practice of current web development), it is possible that the expected data is not read, causing the program to run abnormally.

There are several ways to solve this problem, such as the simplest way to force sleep for a few seconds after all insert and update. This is a very rude way to solve the problem if the update operation is not very high for small and medium sized systems.

Alternatively, the application stores the updated data in the native memory (or centralized cache) and reads it from the native memory if it needs to be read directly after the write data is completed. The disadvantage of this approach is that it greatly increases the complexity of the application, and that reliability is not fully guaranteed.

The use of MySQL proxy can be a convenient solution to this problem. MySQL Proxy is based on the agent between MySQL client and MySQL server to complete the monitoring and modification of client-issued requests. From a client perspective, there is no difference between accessing the server through proxy and accessing the server directly. For existing programs, simply change the IP address and port number of the server directly accessed to the IP address and port number of the proxy.

MySQL Proxy is also simpler to work with. At proxy startup, you can specify the LUA script to use for proxy, and implement 6 methods in the Lua script:

Call when Connect_server ()///Client connection request is received

Read_handshake ()//

Read_auth ()//Read the client's authentication information by calling

Read_auth_result ()//To read the authentication result is called

Read_query ()//When reading a client query request

Read_query_result ()//When reading query results

When a proxy receives a client request, the different methods above are invoked at different stages of the request. In this way, the proxy user can implement these 6 methods freely to achieve the goal according to their business requirements.

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

After resolving the separation of read and write, how to solve the synchronization delay?

The method is to add a self added table on master, which contains only 1 fields. This trigger is triggered when master receives a request for any data updates, and the trigger updates the records in the self-added table. As shown in the following illustration:

Because Count_table also participates in MYSQ master-slave synchronization, update updates made on master are synchronized to the slave. When the client reads data through proxy, the proxy can send a query request to master and slave's count_table table, and when the two data are identical, the proxy can determine that the data state of master and slave is the same. The select request is then sent to the slave server, or it is sent to master. As shown in the following illustration:

This way, you can compare the results of MySQL synchronization delay uncontrollable problem. The "More Perfect" is because this scheme doubles query requests and poses additional pressure on master and slave. However, because the proxy is connected to the real MySQL server in connection with the pool, the additional pressure is acceptable.