Streamlining Queries to MySQL

BANGALORE, INDIA: We have been witnessing a phenomenal increase in the use of proxies in computer networks in recent times. Just to refresh, a proxy (a computer system or an application) is used to service the requests of clients by forwarding them to the server.

It not only provides users an access to the server but also monitors and analyses the client-server communication. For MySQL database users, there's a tool named MySQL Proxy which also functions in the same manner and provides additional functionality.

MySQL Proxy is a tool that sits between the MySQL clients and server, and monitors the client's queries to the server for modification and filtering. It also provides server failover support, load balancing and much more. It is a part of the MySQL Enterprise Tools project and is available as a binary package for Linux, Mac OS and Solaris platforms right now. The binary for Windows is expected anytime soon.

About MySQL Proxy

MySQL Proxy is a lightweight binary application that stands between MySQL clients and MySQL server. Using their credentials, the clients connect to the Proxy instead of server, while the Proxy acts as the redirector for client's requests to the server. MySQL Proxy doesn't just perform the pass-through of client's requests to the MySQL server, but is also capable of monitoring, analyzing and even altering the communication between the client and the server. The Proxy is shipped with embedded Lua interpreters.

Getting started

MySQL Proxy is available in pre-compiled binaries that can be used for installation on a supporting platform. For this article we shall be using Linux derivative Fedora 9 OS as the platform. But Proxy also needs the Lua scripting engine, and the current binary package version for MySQL Proxy contains the Lua interpreter.

It supports MySQL 5.0.1 version and above, so it becomes a pre-requisite to have MySQL 5.0.x version of database server installed on your Fedora 9 machine. Instead of installing Proxy using a binary package, we used 'Yum' to download and install MySQL Proxy. The command for installing Proxy is as follows:

#yum install MySQL-proxy

As, Proxy also needs Lua interpreter; yum will install all dependencies that are needed for MySQL Proxy. Once the download and installation is complete, we can start using the Proxy for MySQL database server.

The syntax for MySQL Proxy is as follows:

#mysql-proxy –-proxy-lua-script=

Where is the Lua script file that is passed along to monitor and modify query transactions between the server and clients. To test the connection, start the MySQL database server and from the same console start the proxy server by passing the command 'mysql-proxy,' and again from another console connect to the proxy server using port 4040 instead of 3306 (default) as follows:

#mysql --host=127.0.0.1 --port=4040 --user=USERNAME --password

When we pass any query from this console, it connects to the proxy at port 4040 and then is redirected to the MySQL server listening at port 3306.

Scripting for query modification

MySQL Proxy uses Lua scripts to intercept the communication between clients and the server. Many a times it happens that a user misspells a keyword and because of that the system throws an error. So, the user has to rewrite the whole query again. A proxy server can be passed with a Lua script that looks for such commonly misspelt words and corrects them. It then directs the query to the database server. The user would not even be aware of the typo error in his query and will still get the desired result. The following Lua script when passed to the proxy server will catch the client's queries for common typos and replace them with correct ones. For instance, keywords like 'CREATE' being typed as 'CRAETE' or 'SELECT' being typed as 'SLECT'. The following Lua script checks for such typing errors in the queries. Open a text editor and save the script as 'demoScript.lua'.

function read_query( packet )

if string.byte(packet) == proxy.COM_QUERY then

local query = string.sub(packet, 2)

print ("Query Received " .. query)

local replacing = false

-- matches "CRAETE" as first word of the query

if string.match(string.upper(query), '^%s*CRAETE') then

query = string.gsub(query,'^%s*%w+', 'CREATE')

replacing = true

-- matches "SLECT" as first word of the query

elseif string.match(string.upper(query), '^%s*SLECT') then   

query = string.gsub(query,'^%s*%w+', 'SELECT')

replacing = true

end

if (replacing) then

print("Error Replaced with " .. query )

proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )

return proxy.PROXY_SEND_QUERY

end

end

end

Now start the proxy server and also pass the demoScript along with it as follows:

Mysql-proxy –proxy-lua-script=demoScript.lua

From a separate MySQL client console window, connect to the MySQL Proxy and then create a table with a wrongly spelt word. For instance, 'CREATE' as 'CRAETE.' The code would be as follows:

#mysql –u USERNAME –p PASSWORD –h 127.0.0.1 –P 4040

MySQL> CRAETE table demo (uid int);

Even when we pass an error in the query, the query is caught for errors at the Proxy and after corrections to the query it's directed to the main MySQL server.  

In conclusion

Thus, by use of such scripts we can create filters or auto-correction for typed errors so that the user gets his query answered even if he had passed an erroneous query.

This was just a simple demo on how queries get modified; the possible uses of the MySQL Proxy are many more, including load balancing and query injections. More can be learnt about them from the MySQL Proxy documentation online.