Monday, May 25, 2009

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

10 comments:

  1. Hey, this code is really helpful to me! My code does something different (it does access control via proxies instead of database users) but a lot of your code is really good reference material. One question, however -- the code to proto.to_challenge_packet isn't on the MySQL forge site. Can you post that too? It's the hardest part. :) Thanks!

    ReplyDelete
  2. Hi Adrienne,

    If you look at this code snippet:
    http://forge.mysql.com/tools/tool.php?id=199
    You will see I used a cleaner implementation.

    I hope that helps.

    Thanks!

    Diego

    ReplyDelete
  3. Thanks. I hadn't upgraded my version of mysql-proxy since the spring, and I hadn't realized that proto.to_challenge_packet was a built-in function.

    Out of curiosity -- do you know how to put something real in that thread-id field? You can't get the actual connection thread ID in the connect_server function, it's nil until read_auth. Or does it not matter?

    ReplyDelete
  4. connect_server() does not see the thread-id yet, on the example, that function is returning a value, because it is simulating a real connection to a real server.

    So you don;t have to supply one

    ReplyDelete
  5. Hello Diego. Thanks for the wonderful work. I have a question though? How do I connect to the proxy in this configuration?
    When I try mysql-proxy --port=4040 --host=localhost I get an "Access denied .." error

    ReplyDelete
  6. Hi Ebo,

    Instead of using localhost, use 127.0.0.1, otherwise, the mysql client will try to use a socket file to connect, and bypass the proxy.

    Thanks!

    ReplyDelete
  7. Hi Diego,

    I have an interesting one for you. I am trying to make PHP act as a mock mysql server. I've taken your handshake code and transplanted it into PHP but when I send the data to the connecting client (in this case SQLYog), the client immediately disconnects with an error.

    The code I've put together looks like this:

    echo ("Sending handshake..." . "\n");

    $hsCode = "\010"; // Protocol Version
    $hsCode .= "5.0.45-proxy"; // Version name
    $hsCode .= "\000"; // Null Terminator
    $hsCode .= "\255\255\255\255"; // Thread ID
    $hsCode .= "\000\020\000\000";
    $hsCode .= "\000\000\000\000"; // Challenge part 1
    $hsCode .= "\000"; // Filler
    $hsCode .= "\001\130"; // Server capabilities (long pass, 4.1 protocol)
    $hsCode .= "\008"; // Charset
    $hsCode .= "\002\000"; // Status

    // Now replicate the \000 another 26 times
    for ($k = 1; $k <= 26; $k++)
    {

    $hsCode .= "\000";

    }

    echo ("Data out: " . $hsCode . " with length: . " . strlen($hsCode));
    socket_write($conn, $hsCode, strlen($hsCode));

    Any idea why this wouldn't work? Are you aware of anything I'm missing from the handshake data?

    Many thanks!

    ReplyDelete
  8. Hi,

    While I don;t really know exactly how the MySQL protocol works, I do think there is more than just the handshake to start a new connection.

    Did you have a chance to read this page

    http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol

    If not, you can try asking someone on the #mysql-dev channel on freenode.

    Thanks

    -Diego

    ReplyDelete
  9. Is there a way to do the following?

    I have Software X and Y on several Clients.

    MySQL-Servers:
    A - directly accessible from clients.
    B - behind Firewall, not accessible from clients, but accessible from A.

    Software X should access Databases on MySQL-Server A.

    Software Y should also call Server A, but MySQL Proxy on Server A determines if specific database Y is called, and redirects to Server B - every other call goes to localhost.

    kind regards,
    James

    ReplyDelete

Vote on Planet MySQL