MySQL Proxy - is it a MySQL server or what is it?

There are times when you don't need a MySQL server to handle your queries, this could come handy when you are automating tests, or writing interesting Lua scripts.

Yes, you can use the MySQL Proxy for this as well. And have it pretend to be a MySQL server.
Today we'll focus on a simple implementation. We will handle an initial connection, a SHOW DATABASES query and the exit (QUIT) command.

You can see all the hooks that the proxy handles on this Lua script written by Giuseppe

Connecting to a server.

function packet_auth(fields)
    fields = fields or { }
    return "\010" ..             -- proto version
        (fields.version or "5.0.45-proxy") .. -- version
        "\000" ..             -- term-null
        "\255\255\255\255" .. -- thread-id
        "\000\020\000\000" ..
        "\000\000\000\000" .. -- challenge - part I
        "\000" ..             -- filler
        "\001\130" ..         -- server cap (long pass, 4.1 proto)
        "\008" ..             -- charset
        "\002\000" ..         -- status
        ("\000"):rep(26)   -- filler & challenge - part II
end

This function prepares the raw response that the MySQL Proxy will return to all clients connecting through the proxy port (default 4040)

If you are into protocols and what not, you can see the Client - Server protocol on the forge.

Then you need to use this function

function connect_server()
    -- emulate a server
    proxy.response = {
        type = proxy.MYSQLD_PACKET_RAW,
        packets = {
            packet_auth()
        }
    }
    return proxy.PROXY_SEND_RESULT
end

There, you see how we return a RAW packet (proxy.MYSQLD_PACKET_RAW), most of the time you see a proxy.MYSQLD_PACKET_OK
or proxy.MYSQLD_PACKET_ERR type of result. And then you see that
packets gets the result of the packet_auth() function.

Handle the Query types.

function read_query( packet )

    if (string.byte(packet) == proxy.COM_QUIT)
    or (string.byte(packet) == proxy.COM_INIT_DB) then
      -- This gets called when you disconnect from the server
      -- or when you specify a database on connection time
        proxy.response.type = proxy.MYSQLD_PACKET_OK
        return proxy.PROXY_SEND_RESULT
    elseif string.byte(packet) == proxy.COM_QUERY then
        if packet:sub(2) == "SHOW DATABASES" then
            return show_databases()
        else
            -- Unkown query
            return error_result (
            "I haven't learnt how to handle that query"
            , '101'
            , '!101')
        end
    end
end

If the query type is either a COM_QUIT or COM_INIT_DB, you can just send an OK packet, and that will be fine.

Then we check for COM_QUERY, and once we find a query, we try to match the query text. In this example, we only handle SHOW DATABASES (all upper case), but you can handle any other query, even queries that are not real SQL :) .

If the MySQL Proxy got a SHOW DATABASES query, it will call the show_databases() function and return a resultset of 70 databases.

For all other queries, the proxy will return an error message back to the mysql client.

Send a result back to the client.

function show_databases()
    proxy.response.type = proxy.MYSQLD_PACKET_OK
    proxy.response.resultset = {
        fields = {
            { type = proxy.MYSQL_TYPE_STRING, name = "Database", },
        },
            rows = {}
    }
    -- here we add 70 database names to the resulset
    for i = 1, 70 do
        table.insert(
        proxy.response.resultset.rows,
         {"db_" .. i})
    end
    return proxy.PROXY_SEND_RESULT
end

Here we create the table for proxy.response.resultset, note the empty rows table. And then, we use a for loop to add 70 items that will become 70 rows on the resultset.

Full code.

You can find this and many other lua scripts to use with the MySQL Proxy on the MySQL forge site.

How can I use it?
Because this lua script simulates being a MySQL Server, you do not need to specify a proxy-backend-address value, just start the proxy like this:

$ sbin/mysql-proxy --proxy-lua-script=/path/to/server_mock.lua