Use MySQL to transfer read-write requests to master-slave server.
Install MySQL Proxy
MySQL Proxy binary plate is very convenient, download after decompression is used.
The extracted directory is: $mysql-proxy_installed_dir (here/usr/local/mysql-proxy) |_ bin |_ include |_ Lib |_ share
1. Create a configuration file for Mysql-proxy. As in $mysql-proxy_installed_dir create file mysql-proxy.cnf, the content is as follows: Text code
- [Mysql-proxy]
- Admin-address = localhost:4041
- Admin-username = MyTest
- Admin-password = 123456
- Admin-lua-script =/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
- proxy-backend-addresses=192.168.1.241:3306
Note: I don't see Admin.lua in Windows, I haven't tried the admin function yet. The important thing is the proxy-backend-addresses configuration, the example above indicates that the request to MySQL proxy will be forwarded to Port 3306 of the MySQL server 192.168.1.241. Linux under Mysql-proxy.cnf 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 & Default boot Port 4040.
Two use MySQL to solve master-slave 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 and cause 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 ()//Receive client connection request when called * Read_handshake ()//* Read_auth ()//Read client authentication information Si Cho Use * Read_auth_result ()//Read the authentication result when called * read_query ()//Read the client query request when called * 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: 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: 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.
Finish!
MySQL Proxy Use
