MySQL Proxy => proxydb + replication

A couple of weeks ago I wrote a lua script to use with the MySQL Proxy that transforms the Proxy into a key=>value lookup dictionary.

But I couldn't just stop there. So I decided to add replication to it :).


The basic idea is that you have one proxy that acts like a master, it can handle all write/read operations. Once it receives a write query, it will send that query to the slave proxy instances, and after the last slave gets the query, the master will return a confirmation to the mysql client.
And of course, you send your read queries to the slave proxy instances.

Show me the code.
It is available on the MySQL Forge. And I'd recommend using Oldag's version of the sandbox, which allows you to easily setup different topologies of MySQL servers and Proxies. (It saved me lots of time)

Complex situation, lots of ins, outs and what have you.
(or how does it work?)

It uses the connection pool feature from the MySQL Proxy that is based on the rw-splitting.lua script.
This means that you will need to start a few different connections to the master proxy instance to get the poll going.
I do this by using this simple command line

$ for i in `seq 1 100` ; do ./proxy1/use -e "exit"; done ;

It also makes usage of mocking a mysql server. The script checks if there is a backend defined (proxy-backend-addresses), if there is one, it will tr to open a connection, otherwise, it is the last node on the chain and it will return an OK packet.

Oh, look at the proto.to_challenge_packet({}) function, which is better than working directly with the protocol.

We then have read_query(), which is pretty easy to understand, I tried to add as many comments as I could to the actual code.
We have different functions for each type of query. I think this is a clean way of doing :).
You will also see that each function that handles different query types has a line like this one:

if proxy.global.last_node == 0 then

This tells the proxy that if it is the last node on the chain, it has to return a resultset

read_query_result()
This was fun to work on. Here is where you send the queries to the slave proxies. The proxy loops over the list of backends and appends the same query. Once it reached the last proxy, it resets the current backend server and returns an OK packet to the mysql client.

This implementation could help you duplicate and reroute your queries to a different mysql server. So that you can send them to a server with different values on the my.cnf file, or any other changes that you'd like to test.

I hope you find it useful and feedback is always welcome.

*I got seq for my mac from this post.