MySQL Proxy 0.6.0 has been tagged and should hit the mirrors near you pretty soon.

In the last 3 month of development we added a lot more features to the proxy-core which should bring the proxy closer to your needs. As always the proxy is transparent to your application, no requirement for a special language, no special needs for a platform and all customizable for your needs.

The most powerful feature is Read/Write Splitting which allows you to scale a application which is unaware of replication automaticly cross several slaves without changes to your application. Instance Scale Out we say.

The Proxy also became a 1st class citizen in the MySQL world …

In the Enterprise Team we had the need to track how many connections to the MySQL server in parallel. By "used" I mean running queries and not just idling around.

And to make it more complicated we needed the information not just every few seconds (while true; do echo "SHOW PROCESSLIST" | mysql; sleep 1;done) but when it happens.

Proxy to the rescue we now have a script which tracks the global state of all connections going through the proxy and dump them to stdout when their state changes:

2007-08-28 21:39:55
  #connections: 20, #active trx: 3, max(active trx): 4
  [792] (merlin@merlin) (COM_QUERY) "SET autocommit=0" (state=started)
  [794] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)
  [795] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)

• when the query comes in, we add it with its thread-id to the global table
• when the query is done, we track …

In MySQL Proxy learns R/W splitting we did a first implementation on idea of sending non-transactional reads to the slaves and writes and transactions to the master.

While that was mostly a proof of concept it already worked pretty well. In this second round on R/W splitting several issues are taken care of:

• SELECT SQL_CALC_FOUND_ROWS ... + SELECT FOUND_ROWS()
• INSERT ... + SELECT LAST_INSERT_ID()
• default_db not in sync between client and backend

These changes should make Read/Write splitting more robust and make it a drop-in for more users.

Up to now we used the tutorial-keepalive.lua to implement the Read/Write-splitting. As the name says, it is a tutorial for connection keepalive, …

In one of the last commits I added a SQL Tokenizer which understands the basic tokens of (My)SQL:

• Strings
• Literals
• Numbers
• Comments
• Operators

With this basic understanding we can normalize Queries and build statistics over similar queries.

The idea is simple and already implemented in mysqldumpslow:

/* login.php:37 */SELECT * FROM tbl WHERE id = 1243 AND name = "jan"

is turned into

  SELECT * FROM `tbl` WHERE `id` = ? AND `name` = ?

The queries look like prepared statements now and can be used the characterize queries of the same kind.

• comments are removed
• whitespaces are stripped
• literals are quoted
• constants are replaced with ?

Taking the famous world-db and executing some simple queries like:

root@127.0.0.1:4040 …

The idea is as old as Replication is:

How do you know which is the most current slave.

You can use the Master_Binlog_Pos to guess what is most up to date, but which transaction does this binlog position match ?

With the proxy you can add a global transaction ID to your setup, if you let the inject some information into the stream.

The idea is simple and is documented in various places.

Create a MEMORY table which is replicated with a single UNSIGNED BIGINT and increment it at the end of each transaction.

CREATE TABLE trx (
  trx_id BIGINT UNSIGNED NOT NULL
) ENGINE=memory;

INSERT INTO trx VALUES ( 0 );

When ever you commit a transaction UPDATE the trx\_id field:

UPDATE trx SET trx_id = trx_id + 1

Usecases:

• identify which slave is most current and switch to it in case of master failure …

The trunk version of the MySQL Proxy 0.6.0 just learnt about changing backends within running connection. It is now up to lua-script to decide which backend shall be used to send requests too.

We wrote a complete tutorial which covers everything from:

• building and maintaining a connection pool with high and low water marks
• transparent authentication (no extra auth against the proxy)
• deciding on Query Level which backend to use

and implement a transparent read/write splitter which sends all non-transactional Queries to the slaves and the rest to the master.

As the splitting is in the hands of the lua-scripting level you can use the same to implement sharding or other rules to route traffic on statement level.

Connection Pooling

For R/W Splitting …

It has gotten a bit quiet around the MySQL Proxy over the last weeks, but I can assure you it was worth it.

Here in my hands I hold MySQL Proxy 0.5.0. Not just some binaries, no everything with sources. As always it is dual licensed under the GPL and commercial MySQL license. We worked hard to get everything ready for the release: the wiki, the forums, the public SVN trees ...

The proxy can do magic. Put in your ideas and say the magic words and there ... it works. :)

The proxy is fully scripted now which opens up the proxy to your ideas. The possibilities are endless. I can think of at least of the following features:

• load balancing
• fail over handling
• query analysis
• SQL macros
• query rewriting
• .... much more

The proxy started as side project of me and is now evolved into a full MySQL project maintained by the …

Over the last weeks I wrote a mysql-proxy which changes the way you operate with the MySQL Server.

A proxy can operate as Man in the Middle and pass through to network packets to the MySQL Server, but it also change the packets when needed. This opens the several possibilities like a

• pseudo server done
• injection proxy done
• load-balancing proxy done
• connection pool
• XA Transaction Manager
• replication client
• replication filter
• replication off-loader

Some of them are already implemented, some are only ideas for the future.

• http://forge.mysql.com/wiki/MySQL_Proxy has the source and the binaries
• …

The MySQL proxy got some final touches to make it ready for the first release:

• a command-line interface
• some more mode descriptions for the different modes of operation

You can choose between three modes for now:

• pseudo server
• injection proxy

* replication client

For the future we plan:

• connection pooling

* replication filtering and off-loading

The options so far:

$ mysql-proxy --help
  --listen-port=<port>                  port the pseudo mysql-server should listen on in server-mode (default: 4040)
  --server-port=<port>                  port of the remote mysql-server in proxy- and reverse-mode (default: 3306)
  --server-ip=<ip>                      ip-address of the remote mysql-server in proxy- and reverse-mode (default: 127.0.0.1)
  --replication-user=<user>             user-account …