Mysql-proxy Database Middleware Architecture

I. Introduction of Mysql-proxy

Mysql-proxy is MySQL's official MySQL middleware service, which provides access to several mysql-client upstream and several mysql-server to the backend.

It uses the MySQL protocol, and any upstream that uses mysql-client can be migrated to mysql-proxy without modifying any code.

The most basic usage of mysql-proxy is to request the intermediate layer of the relay as a request intercept:

Further, Mysql-proxy can analyze and modify the request. Intercepting queries and modifying results is done by writing LUA scripts.

Mysql-proxy allows users to specify LUA scripts to intercept requests, parse and modify requests, and also allow users to specify LUA scripts to modify the server's return results, add some result sets, or remove some result sets.

So, fundamentally, Mysql-proxy is an officially provided framework with good extensibility that can be used to accomplish:

• SQL Interception and Modification
• Performance analysis and monitoring
• Read/write separation
• Request routing
• ...

This framework provides 6 hook points that enable users to dynamically intervene in the communication between the client and the server.

Ii. Mysql-proxy Architecture and principles

As described in the introduction, Mysql-proxy provides users with 6 hook points that enable the user to implement LUA scripts to perform various functions, which are provided as functions that users can implement to do what we expect when different events and different operations occur.

Connect_server ()

The proxy invokes this function when Mysql-client initiates a connection to a proxy. The user can implement this function to do some load balancing things, such as choosing to connect to that mysql-server. Assuming there are multiple mysql-server backend, and the user does not implement this function, Proxy uses the polling (Round-robin) policy by default.

Read_handshake ()

Proxy calls this function when Mysql-server returns "initial handshake information" to proxy. The user can implement this function to do more permission validation work.

Read_auth ()

When the mysql-client sends the authentication message (user_name, password,database) to the proxy, the proxy invokes the function.

Read_auth_result ()

Proxy calls this function when Mysql-server returns the authentication result to proxy.

Read_query ()

After the authentication is completed, the mysql-client will call this function every time the query message is sent to Mysql-server by proxy. If the user wants to intercept the request, it can simulate the Mysql-server direct return, of course, the user can also implement various policies, modify requests, routing requests and other different business logic.

Read_query_result ()

When the authentication is complete, the mysql-server will call this function each time the proxy returns the query result to mysql-client. It is important to note that the Read_query_result () function is not invoked if the user does not display the implement Read_query () function. Users can implement a variety of merge policies here, or modify the result set.

is a trigger schema for each hook function, and the arrow direction indicates the timing of the trigger:

It can be found that the most important two functions are read_query () and Read_query_result (), various SQL rewrite and result set rewrite logic, are implemented in these two functions, more detailed query process such as:

Iii. Typical application of Mysql-proxy

Case ONE: SQL Time statistical analysis

Assume that the original SQL submitted by Mysql-client is:

1. XYZ;

Proxies can be rewritten in Read_query () as:

1. SELECT now ();
2. XYZ;
3. SELECT now ();

This allows the SQL time to be recorded at the application level when the result set is returned to facilitate statistical analysis.

Case TWO: SQL Performance statistics analysis

Assume that the original SQL submitted by Mysql-client is:

1. XYZ;

Proxies can be rewritten in Read_query () as:

1. XYZ;
2. EXPLAIN XYZ;

This allows SQL performance to be logged at the application layer when the result set is returned, for easy statistical analysis.

It should be emphasized that these two cases, because proxy in read_query () when the SQL was rewritten, so in Read_query_result (), Mysql-server actually returned more information than the original request, the proxy must be redundant information removed , and then return to Mysql-client. To say more, you can add a unique ID to pair the request SQL with the returned results.

Case three: Read and write separation

When Mysql-proxy is started, it is possible to configure the backend Mysql-server as the primary server or read-only with parameters without modifying any code:

1. Shell> Mysql-proxy \
2. --proxy-backend-addresses=10.0.1.2:3306 \
3. --proxy-read-only-backend-addresses=10.0.1.3:3306

Note that here the two mysql-server are dominated from the architecture.

Case FOUR: Performance level scaling

When Mysql-proxy is started, multiple back-ends are configured with parameters to achieve horizontal scaling of performance without the need to modify any code:

1. Shell> Mysql-proxy \
2. --proxy-backend-addresses=10.0.1.2:3306 \
3. --proxy-backend-addresses=10.0.1.3:3306

Note that here the two mysql-server are the primary master architecture, and if no special modifications are made, the load balancing policy is round-robin.

Iv. Mysql-proxy Other issues

Question: How much extra overhead does the Lua script introduce?

Answer: Lua quickly, for most applications, the extra overhead is small, and the original package (raw packet) costs around 400 microseconds.

Landlord: This,,, I don't quite believe.

Question: Can mysql-proxy and mysql-server be deployed on a single machine?

Official reply: Proxy can be deployed separately, and MySQL can be deployed on the same machine. Compared to MySQL, Proxy does not account for CPU and memory, and its performance loss can be negligible.

Landlord: This,,, performance loss can be ignored, which I do not believe.

Question: Can proxy handle SSL connection? Proxy does not get and save my plaintext password?

The official website replied: As an intermediary, encryption information cannot be processed. The password is not acquired and cannot be obtained. The MySQL protocol does not allow passwords to be transmitted in plaintext, and is encrypted with ciphertext.

Question: Can I use luasocket in a LUA script, even caching, and even other services?

The official website answers: theoretically can. But, brother, are you sure you want to do this, strongly do not recommend this.

Mysql-proxy Database Middleware Architecture