MySQL read-write separation scheme-MYSQL proxy environment deployment record

MySQL read/write separation can be implemented using MySQL proxy and amoeba, in fact, can also use mysql-mmm to realize the automatic switching of read and write separation. MySQL Proxy has a powerful function is to achieve "read and write Separation", the basic principle is to let the main database processing write aspects of transactions, let from the library processing select query; Amoeba for MySQL is a good middleware software, can also be read-write separation, load balancing and other functions . The following emphasis is on MySQL Proxy:

The MySQL proxy is located between the client application and the MySQL server, enabling it to function by truncating, altering, and forwarding communication between the client and the back-end database. The proxy server is to deal with the TCP/IP protocol, and to understand the working mechanism of MySQL Proxy, also clear the MySQL client and server communication protocol, MySQL Protocol includes authentication and query two basic processes: ................................. The authentication process includes: 1) The client initiates a connection request to the server 2) the server sends a handshake message to the client 3) the client sends the authentication request to the server 4) the server sends the authentication result to the client ..... If the authentication is passed, then the query process is entered: 1) The client initiates a query request to the server 2) the server returns query results to the client of course, this is a rough description, and the packages sent in each procedure are in a fixed format. What MySQL Proxy does is to intervene in the various processes of the protocol. First, the MySQL proxy accepts the client request as the server, analyzes the requests according to the configuration, then forwards the client's identity to the corresponding back-end database server, then accepts the server's information and returns it to the client. So MySQL proxy needs to implement both the client and server protocols. Because you want to parse the SQL statement sent by the client, you also need to include a SQL parser. It can be said that MySQL proxy is equivalent to a lightweight MySQL, in fact, the MySQL Proxy admin server can accept SQL to query the status information. MySQL proxy controls the mechanism of connection forwarding through Lua scripting. The main functions are all in line with the MySQL protocol process, which can be seen from the function name: Connect_server () Read_handshake () Read_auth () Read_auth_result () Read_ Query () Read_query_result () as to why the Lua scripting language is used, presumably because the MySQL proxy uses the wormhole storage engine relationship, the wormhole storage engine is interesting and the data storage format is a LUA script.

Schematic diagram of Mysql proxy

By the way, the working topology diagram of MySQL proxy is attached

Deploy MySQL proxy to read and write separation and improve the operation record of concurrent load

0) Machine Environment

IP address              role         host name 182.48.115.237     master      master-node182.48.115.236     slave1      slave-node1182.48.115.238     slave2      slave-node2182.48.115.233     proxy       Proxy-node four machines both shut down the firewall and SELinux bind the hosts setting (four machines to operate) [[email protected] ~]# vim/etc/hosts ... 182.48.115.237   master-node182.48.115.236   slave-node1182.48.115.238   slave-node2182.48.115.233   Proxy-node

1) MySQL master-slave replication deployment (this case is a master two from the schema)

MySQL installation reference: Http://www.cnblogs.com/kevingrace/p/6109679.htmlmysql master-Slave deployment reference: http://www.cnblogs.com/kevingrace/p/6256603.html

2) Install the Mysql-proxy on the proxy machine

MySQL read-write separation scheme-MYSQL proxy environment deployment record