MySQL Proxy and Global Variables

If you haven't checked out the MySQL Proxy yet, it is definitely worth playing with. One of the most useful features is the embedded Lua interpreter that allows a user such as myself to write scripts that modify queries and responses. Upon each connection to the proxy server, the Lua script is loaded and affects only that connection. Unfortunately my project requires logging over multiple connections (hard when each connections has its own instance of the script), but Jan was nice enough to add the PROXY.global feature. Now a single instance of my logging module can be used by multiple connections. So for your viewing pleasure, I am going to show you an example of how to use the globals in case you ever venture into using the proxy's embedded Lua interpreter.

So, to keep it simple, lets say that you want to simply count the number of queries that come through the server. Here is a script that adds one to `query_count` every time a query is intercepted.

local query_count = 0

function read_query( packet )
    if string.byte(packet) == proxy.COM_QUERY then
               query_count = query_count + 1
               print("query #" .. query_count .. ": " .. string.sub(packet, 2))     
       end
end

If we run this and make a few queries from one connection it counts the queries.

# mysql-proxy --proxy-lua-script=example.lua
query #1: select @@version_comment limit 1
query #2: show databases
query #3: SELECT DATABASE()
query #4: show databases
query #5: show tables
query #6: show tables

But if we open two connections simultaneously, because each connection uses its own instance of example.lua there is an obvious counting error.

# mysql-proxy --proxy-lua-script=example.lua
query #1: show databases
query #2: show tables
query #3: show databases
query #1: select @@version_comment limit 1
query #2: SELECT DATABASE()
query #4: SELECT DATABASE()
query #5: show tables
query #3: select * from test.t2


So clearly here we need a global variable to count queries on both connections and it just so happens there is the proxy.global[] table. Here is the script that uses the global table to store `query_count`.

if proxy.global["query_count"] == nil then
    proxy.global["query_count"] = 0
end

function read_query( packet )
    if string.byte(packet) == proxy.COM_QUERY then
        proxy.global["query_count"] = proxy.global["query_count"] + 1
        print("query #" .. proxy.global["query_count"] .. ": " .. string.sub(packet, 2)) 
    end
end

If we open up two connections and make some queries using both connections it counts the total of both scripts, not just for one.

# mysql-proxy --proxy-lua-script=global_example.lua
query #1: select @@version_comment limit 1
query #2: select @@version_comment limit 1
query #3: show databases
query #4: SELECT DATABASE()
query #5: show databases
query #6: show tables
query #7: select * from t1


Hopefully the simplicity of the example doesn't degrade from the usefulness of the global variables when working with the proxy, but I wanted to give a quick demonstration on one of the most time saving features of Lua programming in regards to the MySQL Proxy.

Cheers.