~jk

I'll be at the OpenSQLCamp 2009 which is part of the FrosCon this weekend and give a talk about drumroll MySQL Proxy.

http://programm.froscon.org/2009/track/OpenSQLCamp/index.de.html has all the sessions incl. my

MySQL Proxy: a MySQL toolbox

Architecture and Concepts of misuse

It will be an extension of my MySQL Conference talk from this year with binlogs, frm-files, ... and what happens if you expose MySQLs core functionality as libraries and wrap it with a scripting layer.

In MySQL Proxy 0.8 we are added a multi-threaded network-subsystem allowing several networks events be processed in parallel. Early benchmarks show that what we have in trunk basicly works.

But the benchmarks weren't as good as we expected. That's the time where you prepare to get dirty.

On the MySQL Proxy channel we get questions from time to time if the authentication can be intercepted and replaced data from a external source.

From now on, you can. For example if you want to get data from a external source (like LDAP) or want to implement roles.

At MySQL Conference in Santa Clara this April the idea was brought up, now we do it:

a MySQL Proxy hackathon this summer in Sun Office in Hamburg, Germany.

We want bring together everyone who is interested in

• hacking on MySQL Proxy,
• sharing ideas how it is used and
• how it should develop.

More at http://forge.mysql.com/wiki/MySQL_Proxy_Hackathon_2009

In my rare spare time work on the http://jan.kneschke.de/2009/5/4/binlog-storage-engine-mysql-proxy-edition and for a few days I was wondering why my index-based JOINs didn't worked. With a index it works, with an index nothing is returned.

We are happy to announce that MySQL Proxy 0.7.1 is available in a source and binary release for many popular platforms.

This release contains a few minor bugfixes and changes in directory layout over the previous 0.7.0 release.

• moved plugins to lib/mysql-proxy/plugins
• moved lua modules to lib/mysql-proxy/lua
• moved libs to lib/

Please report any problems on http://bugs.mysql.com, our Launchpad discussion mailing list at https://launchpad.net/~mysql-proxy-discuss or on IRC: #mysql-proxy on irc.freenode.net.

Please note that the binary for Windows is currently still the old 0.6.1 release and will be updated soon.

A binlog storage engine was talked about many times already. Being able to JOIN against the binlogs, the just SELECT it, search for entries ... is not possible with current SQL commands in the MySQL Server.

As you have read the previous articles here, I wrote a binlog encoder/decoder for MySQL Proxy and wrapped with with a Lua interface. Some years ago I also wrote a scriptable storage engine which can call lua scripts to return data for SELECTs. Putting the two together gives us: a Binlog Storage Engine.

After getting the http://jan.kneschke.de/2009/4/22/druckbetankung stuff working and discussing the idea with the replication team we figured out that my presented approach has a nice hack-value, but otherwise is perhaps a bit too complicated. The same result can be achieved by a more simplified approach:

• turn our input data into a RBR based base64-encoded BINLOG-stmt using binlog
• use the mysql client to push the data into the server

... or how to misuse RBR to fill a MySQL table in the hopefully fastest way.

In my session yesterday I didn't had time to talk about this. The slide-desk covers it and I still wanted to document the idea at least.

I just pushed the code for my replication changes on launchpad:

$ bzr branch lp:~jan-kneschke/mysql-proxy/replication

The presentation should be available ... soon.

The slides are written, uploaded and the code snippets work. I'm ready to present.

One of the topics will be merging binlogs which is what multi-master replication is all about on the low level. A common example is sharding where you have several masters which share the same table-structures, but store the data independently. This is great for scaling out, but tricky if you have to run a query over the full dataset.

... is the title of my session at the MySQL Conf 2009

I'll unveal the full thing at the session at next week Tuesday, but for now let me show you this:

local f = binlog.open("my-binlog", "w")
f:append({       
    server_id = 1,
    type = "INCIDENT_EVENT",
    incident = {
        incident = 42,
        message = "The answer"
    }       
})
f:close()

... gives me:

$ mysqlbinlog my-binlog
...
#090415 12:02:32 server id 1  end_log_pos 350 
# Incident: U‰åƒì‹E‰uø‹‰}ü‹}
                        ‰D‹E‰D$‹F‰$ègö# at 350
...

What ever I did: I can break mysqlbinlog :)

Hint: the above is lua code. A working encoder/decoder for binlogs with a c-library and a lua-wrapper on top.

If you have some ideas what this could be useful for add it as comment to this article and let's see what I can show at the MySQL Conf.

MySQL Proxy 0.7.0 is finally released:

• https://launchpad.net/mysql-proxy/trunk/0.7.0

The full ChangeLog is a bit longer as 0.7.0 was more than a year in the works. To make it short: it is faster, better and more flexible.

Binaries will be release at http://dev.mysql.com/downloads/mysql-proxy/index.html shortly.

0.7.0 is (finally) around the corner. We are only pushing some last minute build-fixes, but otherwise we are done.

If you want to help us getting a good 0.7.0 release out, please test the snapshot RPMs we did build with the help of OpenSuSEs build-service.

At http://software.opensuse.org/search?baseproject=ALL&p=1&q=mysql-proxy-snapshot you will always find the latest snaptshot packages that we build from the latest launchpad code (everything > r640 is current). We have native binaries for Fedora 9 and 10 and OpenSuSE 11.0 and 11.1 right now.

If everything goes well, we should have a 0.7.0 release early next week that builds on all unixes (win32 will come later as we have to add cmake build-files again).

Go test it.

In preparation for the MySQL UC and my session about binlogs I started to implement a first few ideas. In a short coding rush I moved some of the C-code from mysql-binlog-dump.c to the right places in the libraries and wrapped it nicely for Lua.

My overall goal is to make a clean cut between

• a library to work with binlogs
• wrapping with for Lua's use
• a front-end that's making use of all of it