Posted Jan 15, 2009

Intro to MySQL Proxy

By Sean Hull

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

", 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