I recently needed to configure mysql failover on some of our test machines. Thanks to Sheeri’s helpful blog entry which provides a simple failover lua script, configuring failover is a simple matter. However, the machines are running centos-5.2 and centos doesn’t provide an rpm for mysql-proxy. This blog entry describes how to build your own.

The latest mysql-proxy (0.6.1) is apparently not backward-compatible with 0.6.0 and earlier. It incorrectly handles the case when one of the backend machines is down. Instead of just marking it as down, it errors out completely. This makes it rather difficult to use it for failover scenarios. People have complained about this for a while. Bugs 34793 and …

Recently I added the first code to support multiple MySQL backends in HSCALE (see svn.hscale.org). As a “side note” I have to thank Giuseppe Maxia for MySQL Sandbox which made multi server testing a bliss!

While coding this I started to feel that writing HSCALE on top of MySQL Proxy is no more as easy and clean as it started out to be. And finally I reached the point where I have to say:

HSCALE (and maybe other advanced multi-server applications) and (current) MySQL Proxy don’t fit very well.

Before I go into details please let me make clear that this is mostly due to the specific nature of MySQL Proxy being a connection and protocol based proxy. MySQL Proxy is great and there are a lot of cool applications that fit perfectly with it.

Aside from some other minor glitches (like …

The presentation about MySQL Proxy at OSCON 2008 is over.

Here are the slides.

Thanks to my co-presenter Ronald Bradford and to all the participants. If you have more questions about the session, please use this blog's comments.

We did it. Designing Scalable Architectures with MySQL Proxy was delivered successfully, with over 150 attendees.
There is a large number of questions that were asked during the session, and you can find them in MySQL Proxy FAQ.
The slides, with the highly entertaining images used by John Loehrer to illustrate his point are also online
Finally, John posted his Connection pooler Lua script in the Forge.
Thanks to John Loehrer for his lively presentation, to Jimmy Guerrero and Rich Taylor for organizing the event, to Jan Kneschke, for answering questions online …

In the meantime, don't forget the incoming webinar on Designing Scalable Architectures with MySQL …

MySQL Proxy is very flexible and can be used for many different use-cases

• Load Balancing
• Failover management
• synchonized Replication
• Caching
• Query Filtering, Rewriting, ...

What shall we implement first, where should our priority ?

Cast you vote at http://dev.mysql.com/tech-resources/quickpolls/

The mysql proxy project has tremendous potential to make mysql administration and usage easier. I decided to throw some load at it to get a feel for how stable and performant it is.

On EC2, I set up 6 “small” images in an example proxy setup:

- One client machine to run sysbench
- One machine to act as a mysql proxy machine, running 0.6.1 (FC4 binary)
- Four identical database servers, running mysql 5.0.45

The database configuration was largely default, with InnoDB configured for 64MB buffer pool (just enough to ensure the sysbench table was entirely in memory), 512MB log files, and 1024 max connections.

mysql-proxy was run with the following command:

mysql-proxy –proxy-backend-addresses=ip-10-251-66-63.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-71-21.ec2.internal:3306 …

In the next proxy release we introduce plugins, we talked about it already in MySQL Proxy: a chassis and a mysql server. Take a look at http://svn.mysql.com/svnpublic/mysql-proxy/trunk/plugins/ and check out:

• proxy
• admin
• debug

each of them sharing the common code that is provided by the chassis.

The debug plugin is a lua shell with a mysql-protocol ... well, just read on ;)

The purpose of the plugin is to be able to introspect the proxy at runtime. If it is loaded you can connect to port 4043 and execute lua code inside the proxy core:

$ mysql --user=root --password=secret --port=4043 --host=192.168.2.113
root@192.168.2.113:4043 [(none)]> return 1;
+------+
| lua …

From time to time we get the question how to split a query into a several smaller queries and unifying the result-set before we send it back to the client.

As the client only expects to get one result-set, we have to merge the result-sets from the server into one, like this:

First we need a storage for the result-set we want to build:

res = { }

Each connection gets its own one. We declare it outside of the functions as we want to share it between the result-sets of the same connection.

As an example let me just duplicate a query and send it to the server twice:

function read_query(packet)
    if packet:byte() ~= proxy.COM_QUERY then return end

    local q = packet:sub(2)

    res = { }

    if q:sub(1, 6):upper() == "SELECT" then
            proxy.queries:append(1, packet)
            proxy.queries:append(2, packet)

            return proxy.PROXY_SEND_QUERY
    end
end

If it isn't a …

On the way to

MySQL Proxy: replicating into memcache

I have another small side project:

• translating Row based log-records into Statement ones.

Our support team was asking for it since a while and it was a nice PoC that I can decode RBR events nicely.

Running MySQL 5.1 (with binlog_format = ROW) I issued:

mysql> INSERT INTO cols_pk VALUES \
     ( 3, "varchar", "char" ), ( 4, NULL, NULL );

... and have let mysql-binlog-dump decode the row-based log-events into SQL statements:

$ ./mysql-binlog-dump \
  --binlog-file=/home/jan/datadir/mysql-bin.000010

-- mysql-binlog-dump.c:256: db = test
BEGIN

-- mysql-binlog-dump.c:220:
CREATE TABLE test.cols_pk (
  field_0 INT NOT NULL,
  field_1 VARCHAR(64) DEFAULT NULL,
  field_2 CHAR(64) DEFAULT NULL
)

-- …