Streamlining Queries to MySQL

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.

The Lua scripting language is used to intercept the user queries and add
profiling to the query. By intercepting user queries, the proxy can insert
additional queries such as the query to know the execution time to the list of
client's queries and can remove the additional results that were returned by the
database server. With Proxy monitoring, filtering or manipulation on queries can
be performed without the user being even aware of them.

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.

From one console, start MySQL Proxy and from the second console, connect to Proxy. When we pass queries having wrong keywords, they get replaced at the Proxy and the query with correct keywords is passed to the main server.

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.