Intro to MySQL Proxy

Introduction

A proxy
is a person who performs legal duties in another person’s stead.  In
technology, a web-proxy can provide anonymity, making your web surfing appear
to originate from your proxy’s location.  Proxies are also used to cache
data between a client browser, and the actual web server hosting data.  

Given
all of that, it’s no surprise that the concept of a proxy has made its way into
the database arena.  The MySQL Proxy, by Jan Kneschke sits between your application
and your MySQL database.  As an example, when you fire up your mysql
client, you by default connect to port 3306.  By starting up the
mysql-proxy daemon, and then connecting your client to 4040, all SQL queries
will pass through the proxy first.  As those queries pass in transit, we
can do a lot of useful things.  We might time those queries, or redirect
them somewhere else.  Or we might rewrite them in some way.  Further
on, we’ll discuss the myriad of uses for this technology.

Installing

a. download
binaries

The
first thing you’ll need to do is get hold of a copy of the software.  You
can build from source, but you shouldn’t need to.  Grab a binary distro
for your platform, and fire it up from the installed folder.  We don’t
recommend installing it system-wide, as this software is still in alpha at the
time of this writing.  Here’s the download page:

http://dev.mysql.com/downloads/mysql-proxy/index.html

b.
starting mysql-proxy

To run
the daemon, issue the following command:

$ sbin/mysql-proxy --proxy-lua-script=share/tutorial-basic.lua &

c.
connecting with mysql client

Now fire
up your usual mysql client.  Be sure to specify the port, so you don’t
connect directly to the mysql server itself:

$ mysql --host=localhost --port=4040 -u root -p

What’s
happening here?  Well we’re just playing a fancy game of
man-in-the-middle.  Instead of telling our mysql client to connect to a
mysqld database server, we’re telling it to connect to the proxy instead. 
The proxy in turn will connect to the database server for us.  So all
traffic that our client would have sent to the database server for execution, gets
passed through are proxy instead.  Thus our proxy can play with that
stream of traffic, and manipulate it in various ways.  

Examples

Understanding
and writing lua scripts are the key to customizing the mysql-proxy. 
Although it may be a bit of an obscure programming language, its lightweight
and compact nature make it perfect for a low impact man-in-the-middle
technology like the MySQL-proxy.  The distributions come with a number of
example lua scripts for you to sharpen your teeth on.  Here’s another
example from Giuseppe Maxia which prints out the various hooks you can use in
your scripts.  You can find more from Giuseppe here:

http://forge.mysql.com/tools/tool.php?id=109


— all-hooks.lua
local access_ndx = 0
function read_auth( auth )
print_access (‘inside read_auth ‘)
end
function connect_server()
print_access (‘inside connect_server’)
end
function read_handshake( auth )
print_access (‘inside read_handshake’ )
end
function read_auth_result( auth )
print_access (‘inside read_auth_result’)
end
function disconnect_client()
print_access(‘inside disconnect_client’)
end
function read_query (packet)
print_access(‘inside read_query t’ .. packet:sub(2))
proxy.queries:append(1, packet)
return proxy.PROXY_SEND_QUERY
end
function read_query_result (inj)
print_access(‘inside read_query_result t’ .. inj.query)
end
function print_access(msg)
access_ndx = access_ndx + 1
print( string.format(‘%3d %-30s’,access_ndx,msg))
end

You’ll
also find Giuseppe’s blog is full of useful information:

http://datacharmer.blogspot.com/

Here’s
another from the author of mysql-proxy, Jan Kneschke:  

http://forge.mysql.com/tools/tool.php?id=76

This
logs all queries to a file myquery.log


local log_file = ‘query.log’
local fh = io.open(log_file, “a+”)
function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
local query = string.sub(packet, 2)
fh:write( string.format(“%s %6d — %s n”,
os.date(‘%Y-%m-%d %H:%M:%S’),
proxy.connection[”thread_id”],
query))
fh:flush()
end
end

 You’ll
also find Jan’s blog is full of useful information as well:

http://jan.kneschke.de/

Applications

There
are many applications for this technology, and once you start dabbling, your
mind will surely run wild with ideas.  For instance, the default MySQL
server’s slow query log provides only single second granularity.  If you
want microsecond control, you’ll have to add the Google patches to get
it.  That requires a recompile of the MySQL source, which many
administrators would resist.  Enter the MySQL-Proxy, which comes with a
lua script to do just that.  You can get the subsecond granularity, and
log queries accordingly.   What’s great is that you can install it on a
running system, without any big changes.  Instead of having your app connect
to port 3306, you’ll point it to port 4040 of the proxy.  But what about
without changing any application code at all?  One way would be to run
mysql on a different port, and restart it.  Then run the proxy on port
3306.  However, an even better way is to you Linux’s iptables to reroute
all incoming connections on port 3306.  Take a look at this link for code
snippets to do that:

http://forge.mysql.com/tools/tool.php?id=82

What
about load balancing?  Suppose you want all SQL with the words INSERT,
UPDATE or DELETE to go to your primary server, and then SELECT queries to be
redirected to various slave copies of the production server.  MySQL Proxy
fits the bill here too.  

Conclusion

Although
MySQL Proxy remains in the alpha stage of development, its potential
applications are already building its popularity.  The ability to sit
transparently between an application and its database has tons of applications,
from diagnostics and troubleshooting to high availability and load
balancing.  What’s more, since it sits transparently in the middle, it can
be put into service as quickly and easily as it can be taken out of
service.  Stay tuned as this technology matures it will surely become a
part of your open-source database arsenal of tools and tricks.

»
See All Articles by Columnist Sean Hull

Sean Hull
Sean Hull
Sean Hull is a business and technology consultant, author and speaker. He is the founder and senior consultant at Heavyweight Internet Group in New York and has worked as a database consultant and expert for ten years. He is the author of "Oracle and Open Source" on O'Reilly & Associates and his articles have appeared in Oracle's own OTN website, DBA Zine, ChangeThis.com, and many others. Using techniques, technologies, and perspectives discussed in the book, articles, and seminars, he has taken his career into exciting areas. He has served as Technology Consultant, Project Manager, CEO, and mentor with a background in programming, systems integration & administration, project development and management. He can be reached at [email protected] or visit http://www.iheavy.com for more info about consulting services.

Latest Articles