Featured Database Articles

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:

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:

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:

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.