Using MySQL Proxy for Server Profiling and Intercepting queries

Today I tried out an MySQL Proxy(alpha, 0.7.2), which is closer to SQL profiler in the sense of monitoring all connections and queries on the server. All we need is for the client to point to the port of the proxy (default is 4040) rather than mysql server (which is mostly 3306) directly.

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for a wide variety of uses, including load balancing; failover; query analysis; query filtering and modification; and many more
MySQL Proxy is compatible with MySQL 5.0.x or later.

>> Links
Download. http://dev.mysql.com/downloads/mysql-proxy/index.html
Documentation :
http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html
http://forge.mysql.com/wiki/MySQL_Proxy

Supported Platforms
MySQL Proxy is currently available as a pre-compiled binary for the following platforms:

• Linux (including RedHat, Fedora, Debian, SuSE) and derivatives.


• Mac OS X


• FreeBSD


• IBM AIX


• Sun Solaris


• Microsoft Windows (including Microsoft Windows XP, and Microsoft Windows Server 2003)
  
  You must have the .NET Framework 1.1 or higher installed

MySQL Proxy is provided as a standalone, statically linked binary. You do not need to have MySQL or Lua installed (Lua is the embedded scripting language for proxy scripts)

Installing MySQL Proxy

Simply download the binary package and extract to a desired location on your machine.
For more details on available downloads and installation instructions refer to http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy-install.html

MySQL Proxy Scipting

LUA is the scripting language for mySQL proxy. You can generate .lua script files to manipulate and work with queries and results that are passed to or retrieved from MySQL server. The primary interaction between MySQL Proxy and the server is provided by defining one or more functions through an Lua script.
There are a number of supported functions, according to different events and operations in the communication sequence between a client and one or more backend MySQL servers. Some of the functions are

• connect_server() — this function is called each time a connection is made to MySQL Proxy from a client. You can use this function during load-balancing to intercept the original connection and decide which server the client should ultimately be attached to.


• read_handshake() — this function is called when the initial handshake information is returned by the server. You can capture the handshake information returned and provide additional checks before the authorization exchange takes place.


• read_auth() — this function is called when the authorization packet (user name, password, default database) are submitted by the client to the server for authentication.


• read_auth_result() — this function is called when the server returns an authorization packet to the client indicating whether the authorization succeeded.


• read_query() — this function is called each time a query is sent by the client to the server. You can use this to edit and manipulate the original query, including adding new queries before and after the original statement. You can also use this function to return information directly to the client, bypassing the server, which can be useful to filter unwanted queries or queries that exceed known limits.


• read_query_result() — this function is called each time a result is returned from the server, providing you have manually injected queries into the query queue. If you have not explicitly inject queries within the read_query() function then this function is not triggered. You can use this to edit the result set, or to remove or filter the result sets generated from additional queries

You will get some sample .lua scripts under {install location}/share

Below is a sample script example to get the query time for each injected query we send to server (save it to .lua file extension to use it)

-- * start-time: when we call proxy.queries:append()
-- * end-time: when we received the full result-set
-- @param packet the mysql-packet sent by the client
-- @return
-- * proxy.PROXY_SEND_QUERY to send the queries from the proxy.queries queue
function read_query( packet )
if packet:byte() == proxy.COM_QUERY then
print("we got a normal query: " .. packet:sub(2))
proxy.queries:append(1, packet )
return proxy.PROXY_SEND_QUERY
end
end
-- read_query_result() is called when we receive a query result
-- from the server
-- inj.query_time is the query-time in micro-seconds
-- @return
-- * nothing or proxy.PROXY_SEND_RESULT to pass the result-set to the client
function read_query_result(inj)
print("qt|rt: " .. (inj.query_time / 1000) .. "|" .. (inj.response_time / 1000) ..
"ms: " .. inj.query:sub(2))
end

For more details on scripting refer to http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy-scripting.html

Starting With MySQL Proxy

The default port of MySQL Proxy is 4040. You can start using MySQL Proxy in following simple steps

1. Browse to the installation directory of MySQL Proxy
2. Have the .lua scripts in place at some accessible location
3. Through command prompt run the proxy
a. Peeking into available functions
> mysql-proxy.exe –help
{Lists all the available options}
b. Starting the proxy
> mysql-proxy.exe --proxy-backend-addresses=localhost --proxy-lua- script=scripts/test.lua --log-level=debug --log-file=log/results.log
c. To start using connect to the MySQL Server on Proxy’s port i.e 4040
> mysql -u root -h localhost -P 4040 –p
Enter Password:
mysql> use mysql;

Voila as you query you would see the results in your log file

Some excerpts from the results.log fiel
we got a normal query: select @@version_comment limit 1
qt|rt: 0|0ms: select @@version_comment limit 1
we got a normal query: SELECT DATABASE()
qt|rt: 0|0ms: SELECT DATABASE()
we got a normal query: SELECT DATABASE()
qt|rt: 0|0ms: SELECT DATABASE()
we got a normal query: select * from users limit 1
qt|rt: 0|0ms: select * from users limit 1
qt means query time until first bytes. rt means the query time until the last bytes.

You can also connect to MySQL server via this proxy from your applications too, have in place right set of .lua scripts to have all the monitoring at your doorsteps.
For example an excerpt from the persistence.xml of my JPA application



Warning: MySQL Proxy is currently an Alpha release and should not be used within production environments

Happy Querying!