Use MySQL proxy

Transferred from:

Http://koda.iteye.com/blog/788862http://koda.iteye.com/blog/682547

 

Use MySQL to forward read/write requests to the Master/Slave server.

 

1. Install MySQL proxy

The binary version of MySQL proxy is very convenient and can be used immediately after downloading and decompressing.

The decompressed directory is:
$ Mysql-proxy_installed_dir (here for/usr/local/MySQL-proxy)
| _ Bin
| _ Include
| _ Lib
| _ Share

1. Create a configuration file for MySQL-proxy.
For example, if you create a file mysql-proxy_installed_dir in $ mysql-proxy.cnf, the content is as follows: TextCode

1. [Mysql-proxy]
2. Admin-address = localhost:4041
3. Admin-username = mytest
4. Admin-Password =123456
5. Admin-LUA-script =/usr/local/MySQL-proxy/lib/MySQL-proxy/Lua/admin. Lua
7. Proxy-backend-addresses =192.168.1.241: 3306

Note: I have not found Admin. Lua in windows. I have not tried the Admin Function yet. It is important to configure proxy-backend-addresses. The above example indicates that the request sent to the MySQL proxy will be forwarded to port 3306 of the MySQL server 192.168.1.241.
The mysql-proxy.cnf under Linux needs to be set to 0660 permissions.

2. Start MySQL proxy
/Usr/local/MySQL-proxy/bin/MySQL-proxy -- defaults-file =/usr/local/MySQL-proxy/mysql-proxy.cnf &
Port 4040 is enabled by default.

 

 

2. Use MySQL to solve master-slave latency

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, 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 the data delay problem, when the applicationProgramWhen data is updated on the master and immediately needs to be read from the database, if the application obtains data from the slave (this is also the common practice of web development ), the expected data cannot be read, 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:
 
Mysql_proxy_write

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, 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:
 
Mysql_proxy_read

In this way, 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.

 

 

Complete!