MySQL Proxy: tape^H^H^Hest recorder

Test-Commander Eric was working on the log-replay task and as he is at my place right now, we have put our heads together to get a working prototype together.

---
-- log queries going throw the proxy on request and
-- provide a commands to work on the logged queries
--
-- * PROXY SET GLOBAL replay.log_queries = (true|false)
--   log queries sequentially (used by SAVE)
-- * PROXY SET GLOBAL replay.fake_mysqld = (true|false)
--   return the logged resultset for known queries
--
-- * QUERYLOG SAVE INTO "filename"
--   dump the queries into a file and reset the log buffer
-- * QUERYLOG LOAD FROM "filename"
--   load logged queries back into the query_log (used by the
--   fake_mysqld)
-- * QUERYLOG SHOW

What is this useful for ? ... hmm ...

The first goal is creating a fake-mysqld allowing us to run proxy tests without have to have a mysqld on the box.

As a proof-of-concept we logged the SHOW GLOBAL STATUS going through the proxy to the mysqld and then replaying it without a mysqld running:

$ cat << EOF | mysql --host=127.0.0.1 --port=4040 
PROXY SET GLOBAL replay.log_queries = true;
SHOW GLOBAL STATUS;
PROXY SET GLOBAL replay.log_queries = false;
QUERYLOG SAVE INTO "show-status.log";
EOF

$ killall mysqld
$ cat << EOF | mysql --host=127.0.0.1 --port=4040 
QUERYLOG LOAD FROM "show-status.log";
PROXY SET GLOBAL replay.fake_mysqld = true;
SHOW GLOBAL STATUS;
PROXY SET GLOBAL replay.fake_mysqld = false;
EOF
Variable_name   Value
Aborted_clients 84
Aborted_connects        0
Binlog_cache_disk_use   0
...

As a full verification we ran the proxy test-suite through the logging proxy, killed the mysqld behind it and ran the test-suite again against our fake mysqld ... and it worked !

Now this is just a start.

1. we need to add more data around the resultset and packet fields to the log
2. we have to log the connect phase to announce the right charset and capabilities

3. we want to add more (similar) use cases as described below:

     -- * QUERYLOG VERIFY FROM "filename"
     --   passthrough queries and verify that the client is sending
     --   the same queries and receives the same resultset
     --   (verifying connectors)
     -- * QUERYLOG REPLAY FROM "filename"
     --   replay the queries against the MySQL backend and
     --   verify that we get the same resultsets and statusflags back
     --   (verifying servers)
     

4. factor the code into some library files.

A short glimpse at the recorded file:

return {
  [1] = {
    ["query_raw"] = "\003SHOW GLOBAL STATUS",
    ["cmd"] = "COM_QUERY",
    ["is_executed"] = false,
    ["resultset"] = {
      ["fields"] = {
        [1] = {
          ["type"] = 253,
          ["name"] = "Variable_name",
          ["table"] = "STATUS",
          ["org_name"] = "Variable_name",
        }
      },
      ["rows"] = {
        [1] = {
          [1] = "Aborted_clients",
          [2] = "84",
        },
      ...

This is plain lua code and human readable way. We can either use the logging here, write it by hand or generate it at runtime with another app. No magic involved. Ok, almost no magic :)

Thanks Eric for the input, I was only the code monkey.