MySQLProxy is a piece of software which sits around pretending to be a MySQL server, and relaying your requests to a real server or servers. Sounds horribly tedious, right?
Well, there's a bit more to it than that. It has Lua, one of those scripting languages which people delight in embedding everywhere, embedded in it. You can write Lua functions which are called when a query is received, for instance, and allow you to send back a response, make modifications to the query, make additional queries, at all. This makes it incredibly powerful.
How so, you might ask? Well, first of all, here is a terribly simple little function which simply adds a custom command to MySQL.
function read_query(packet)
if string.byte(packet) == proxy.COM_QUERY then
local query = string.sub(packet, 2)
if string.match(string.upper(query), '^s*SHOW MAGSDATA') then
proxy.response.type = proxy.MYSQLD_PACKET_OK
proxy.response.resultset = {
fields = {
{
type = proxy.MYSQL_TYPE_STRING,
name = "Inventions",
},
{ type = proxy.MYSQL_TYPE_LONG, name = "Execution count" }
},
rows = {
{
"Soft ice cream!",
abc
},
{
"Poll tax",
abc
}
}
}
abc = abc + 1
return proxy.PROXY_SEND_RESULT
end
end
end
From the user's point of view, the server behaves exactly like the underlying server. Except, of course, when they type SHOW MAGSDATA;. If they do that, then it will never get to the server; instead, it will give back a result-set containing a few things that Margaret Thatcher kinda-sorta-invented, and a count of how many times the query has been executed on a given connection.
Okay, how is that useful? Well, okay, it isn't, but it does show a bit of what you can do with the proxy; you can detect and parse interesting queries (the package actually includes a query tokenizer and parser, to make this easier) and send back data of your own. Fine, fine, but why would you want to do that?
Well, imagine yourself in these situtations.
You are a network administrator for a hosting company. The horrible, disgusting, putrid users insist on writing their own amateurish PHP scripts which access your database. Amongst other things, they like to select the entire contents of a table, then use the first couple of results and discard the remainder. This works fine on their home machine with ten rows, but not so well on your machine with a million.
Solution: use MySQL Proxy to add in a large 'LIMIT' constraint to queries which don't already have one.
Your company has recently purchased the Margaret-tron 6000 instant messaging system, which stores every message sent in an inbox table under the recipient's user id. This is fine with ten users, but less fine when thousands of messages are being sent every second. You don't really care if messages are lost when the computer goes down.
Solution: use MySQL Proxy to hijack requests to the table, putting the messages in Memcached or similar.
Emboldened by the success of their last purchase, your company purchases the Margaret-tron 17000.6 enterprise content relations fax-enabled transglobal management system, with optional coffee-machine control plugin. Once more, it worked fine for ten people, but now the poor database machine is groaning under the load. You want to partition the database, but (of course) the software doesn't support that.
Solution: Intercept requests, using MySQL Proxy. Send queries going to the 'blogs' table to server1. Send queries going to the 'cappuccino' table to server2. And so forth.
You discover that the biometric access system, bless its little heart, keeps full retina scans for all employees on a database server, and, when an employee attempts to enter, pulls the megabyte-sized file across the network, updates it, and sends it back. Database is dying, requests are taking weeks, etc. The previous trick won't work, because there is only one table.
Solution: Intercept the request. If the userID mod <number of servers> is 0, send to server 0. If 1, send to server 1. Etc.
The device which accurately tracks user movement throughout the building likes to send updates of employee positions, for each employee, every half a second. These are immediately shoved into the database. Of course, it would be much faster if they were inserted in batches, but you don't have access to the application source to change this.
Solution: Collect INSERTs in the proxy, not allowing them to go through to the database. When you accumulate X INSERTs, send them as one multi-value INSERT.
Your third-party forum thing needs to be integrated into afore-mentioned Margaret-tron kitchen sink software. Of course, they use different login systems, user profiles, et al.
Solution: Rewrite requests from forum to login system, requests to get profile data, and so on.
Client app likes reading 200 values out of a configuration table of 200 values, one by one. (Wordpress used to do this.)
Solution: Read them once, then send them back from memory when asked for.
Web development team needs to access Big Scary Important Machine, but is uncomfortable with things like web services or CORBA or carrier pigeons or whatever (or even the required goat sacrifices have not appeased the PHP CORBA module).
Solution: Give them a nice, friendly MySQL-like interface to it.
You need to test that your new application responds to things like slow query responses and obscure MySQL errors (666, computer possessed by Satan and so forth). These don't often occur naturally, and are difficult to provoke.
Solution: Fake them with the proxy.
Your transactions occasionally deadlock, and you don't want to add retry code to the five million places in your application where it is required.
Solution: Add retry code in the proxy layer.
Obviously, some of these circumstances are at least a little contrived, and most would require a certain amount of caution and knowledge of the applications involved, but hopefully you can see how this could be so useful.
The most obvious use case seems, to me, to be tacking a capability or optimisation onto an application which you either can't change or don't want to change. But really, the possibilities are endless, and I look forward to playing around with it.
By the way, as far as speed goes, it doesn't seem to introduce any significant slowdown running on a G4 iBook, though I haven't done proper load testing.
