Use MySQLProxy to solve MySQL master-slave synchronization latency

Source: Internet
Author: User
The master-slave synchronization mechanism of MySQL is very convenient to meet the application requirements of high concurrent reading, and brings great convenience to Web development. However, this method has a major drawback: MySQL's synchronization mechanism relies on Slave to actively send requests to the Master to obtain data. In addition, due to server load and network congestion, number between Master and Slave

The master-slave synchronization mechanism of MySQL is very convenient to meet the application requirements of high concurrent reading, and brings great convenience to Web development. However, this method has a major drawback: MySQL's synchronization mechanism relies on Slave to actively send requests to the Master to obtain data. In addition, due to server load loss and network congestion, number between Master and Slave

The master-slave synchronization mechanism of MySQL is very convenient to meet the application requirements of high concurrent reading, and brings great convenience to Web development. However, this method has a major drawback: MySQL's synchronization mechanism relies on Slave to actively send requests to the Master to obtain data. In addition, due to server load loss and network congestion, the data synchronization latency between the Master and Slave is completely unguaranteed. Within 1 second, it may take several seconds, dozens of seconds, or even longer.

Due to data delay, when the application updates data on the Master and needs to read data from the database immediately, at this time, if the application retrieves data from the Slave (this is also the common practice of Web development), it may fail to read the expected data, causing program running exceptions.

There are multiple ways to solve this problem, such as the simplest way to force sleep for several seconds after all insert and update operations. This is a very rude method. For small and medium systems that do not have very high update operations, this method can basically solve the problem.

Another method is to store updated data in the local memory (or centralized cache). If you need to directly read data after writing data, read from the local memory. The disadvantage of this method is that it greatly increases the complexity of the application, and the reliability cannot be completely guaranteed.

Using MySQL Proxy can easily solve this problem. MySQL Proxy is a Proxy program between the MySQL Client and the MySQL Server. It can monitor and modify the requests sent to the Client. From the Client perspective, there is no difference between accessing the Server through Proxy and directly accessing the Server. For existing programs, you only need to replace the IP address and port number of the directly accessed Server with the IP address and port number of the Proxy.

The working principle of MySQL Proxy is also relatively simple. During Proxy startup, you can specify the lua script required by the Proxy, and implement six methods in advance in the lua script:

Connect_server () // called when receiving Client connection requests

Read_handshake ()//

Read_auth () // called when the Client's authentication information is read

Read_auth_result () // called when reading the authentication result

Read_query () // called when reading the Client's query request

Read_query_result () // called when reading query results

When the Proxy receives a Client request, different methods are called at different stages of the request. In this way, the Proxy user can freely implement these six methods according to their own business needs.

By adding code to read_query (), we can extract whether the current request is insert, update or select, then send the insert and update requests to the Master, and send the select requests to the Slave, this solves the problem of read/write splitting.

After the read/write splitting is solved, how does one solve the synchronization latency?

The method is to add an auto-increment table to the Master, which contains only one field. When the Master receives any data update request, this trigger is triggered to update records in the auto-increment table. As shown in:

Because Count_table is also involved in the Master-Slave synchronization of Mysq, the Update on the Master will also be synchronized to the Slave. When the Client reads data through the Proxy, the Proxy can first send a query request to the Count_table table of the Master and Slave tables. When the data of the two tables is the same, the Proxy can determine that the data status of the Master and Slave is the same, and then send the select request to the Slave server. Otherwise, the select request is sent to the Master. As shown in:

Through this method, the synchronization latency of MySQL is uncontrollable. The reason why the query is "perfect" is that this scheme doubles the query request and puts extra pressure on the Master and Slave. However, because the Proxy and the real Mysql Server are connected through a connection pool, the extra pressure is acceptable.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.