Advertisment

Streamlining Queries to MySQL

author-image
PCQ Bureau
New Update

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.

Advertisment

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.

Direct Hit!

Applies To: MySQL users on Linux



USP: Tool to monitor and modify client
queries to MySQL server



Primary Link: http://dev.mysql.com/
downloads/mysql-proxy



Google Keywords:MySQL Proxy

Advertisment

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.
Advertisment

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.

Advertisment

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:

Advertisment

#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'.

Advertisment

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

Advertisment

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.

Advertisment