Analysis on the working mechanism of MySQL Proxy and Amoeba

The MySQL proxy is between the client application and the MySQL server, and realizes its function by truncating, altering and forwarding communication between the client and the back-end database, which is the same as the basic idea of a network proxy server such as WinGate . 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 certification process includes:

1. Client initiates a connection request to the server
2. The server sends handshake information to the client
3. Client sends authentication request to server
4. Server sends authentication results to client

If authentication is passed, enter the query process:

1. Client initiates a query request to the server
2. The server returns query results to the client

Of course, this is only a rough description, each process sent the package is a fixed format, want to learn more about MySQL protocol classmate, you can go here to see. 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 for why the LUA scripting language is used, I think this is because the MySQL proxy uses the wormhole storage engine relationship, the wormhole storage engine is very interesting, the data storage format is a LUA script, really creative.

Schematic diagram:

Amoeba is a distributed Database intermediate agent layer software similar to MySQL proxy, which is an open source Java project developed by Chen Si. Its main functions include reading and writing separation, vertical sub-Library, horizontal sub-library, etc., after testing, found that its function and stability are very good, if you need to structure a distributed database environment, the adoption of amoeba is a good solution. Currently amoeba includes for Aladdin,for MySQL and for Oracle three versions, this article focuses on a read-write separation implementation for MySQL version. In fact, vertical and horizontal segmentation of the architecture is not small, change a few configurations can be easily implemented.

is a distributed system architecture using Amoeba's read-write separation technology combined with MySQL's Master-slave replication:

Amoeba is in between the application and the database, playing an intermediary role, the application passed over the SQL statement after analysis, the written statement to the Master library execution, the read statement is routed to the slave library execution (of course, can also go to master read, this completely look at the configuration). The amoeba implements simple load balancing (with polling algorithm) and failover. If more than one read library is configured, any one read library will be down without causing the whole system to fail, amoeba can automatically route the read request to the other available libraries, and of course, the write or single point depends on the master database, which needs to be switched through the database. or horizontally split technology to improve the usability of the Master library.

Amoeba can start multiple on different machines, and do the same configuration to scale horizontally to share the pressure and improve availability, can be installed amoeba and MySQL on the same machine, can be installed on different machines, amoeba itself does not do the data cache, so for memory consumption is very small, Mainly CPU usage. For the application, the figure of three amoeba is three identical MySQL database, connect any one of them is possible, so need to have a load balance and failover mechanism on the application side, need to connect the database from three randomly pick one, If any other failure occurs, it can be automatically failover to the remaining available machines. MySQL's JDBC driver has provided such load balancing and failover functionality since the Connector-j 3.17 release, so the rest is simple for the application, and without making too many changes to build a highly available MySQL distributed database environment, why not?

Analysis on the working mechanism of MySQL Proxy and Amoeba