MySQL Proxy - proxydb

What I really like about having Lua and MySQL Proxy together is that it turns out to be very flexible, you can have the proxy do all kinds of things. And the last thing I made the MySQL Proxy do is to act like memcached.

Well, maybe not, but it handles key => value pairs now :P




What does it do?

It handles 5 basic query types:

mysql> INSERT "key" = "value";

Very simple, insert a key => value pair on a proxy.global.db table, if there is already a value for that query, it will overwrite it.

mysql> SELECT "key";

It retrieves the value for the specified key.

mysql> DELETE "key";

Deletes the key => value pair from the lua table.

mysql> SELECT *;

It returns all the key=> value pairs.

mysql> DROP;

It deletes all rows from the table.

Internals.
This time I used the tokenizer to parse the queries, and I'm so glad I did. It is much easier and cleaner that writing regular expressions to match your queries.

I also wrote different functions to handle each statement. Which I call based on the tokens I find.

Looking at the script, you will find different ways of returning results back to the mysql client. Something that took me a while to figure out was, how to return an empty resulset, but with a number of affected rows, like what you get from an INSERT statement.

This is the answer:

function add( key, value )
   -- We add the key value pair to the global table
   proxy.global.db[key] = value
   -- we return a nice "affected rows count"
   proxy.response.type = proxy.MYSQLD_PACKET_OK
   proxy.response.affected_rows = 1
   proxy.response.insert_id = 0
   return proxy.PROXY_SEND_RESULT
end

If you call this function, you need to do so like this:

return add(tokens[i + 1]["text"], tokens[i + 3]["text"])

and not just like this:

add(tokens[i + 1]["text"], tokens[i + 3]["text"])

* Note the "return" there. Otherwise, you will not get the custom resulset on the client.

I also added the connect_server() function, which allows you to run the mysql proxy without any mysql server on the backend.

How do I use it?
Get the code from the MySQL Forge site, and save it as proxydb.lua
Then, start the MySQL Proxy like this:

./bin/mysql-proxy  --proxy-lua-script=/src/mysql-lua-scripts/trunk/scripts/proxydb.lua

You can then connect to the port 4040 using your mysql client and start sending queries. If you use any of the queries I showed before, the proxydb.lua script will handle them.

Numbers?
Yesterday I run some tests and I found that

• Having 3,908,492 entries
• Key length was about 11 characters
• Value length was about 17 characters
• MySQL Proxy process used 1.5GB of RAM

Next?
Who knows, maybe someone will use this script and let me know how it runs on a busy setup :).