Neofreko

mysql-proxy, being developed by MySQL AB itself, is very powerful tool to have. It can intercept raw mysql packets from authentication to query and query result. Creating a read/write load balancer has never been easier. Instead of filtering query (as seen in sqlrelay), we can use mysql-proxy to defines which back-end should be used for reading (–proxy-read-only-backend-addresses) or writing. No need to mess with each query executed. However, I miss regex in query filter or router of sql-relay. I wonder if we can use regex in Lua so we don’t have to do substr thingy in read_query() hook.

Powered by ScribeFire.

I am not yet going to have the vs tag. I just want to do a simple comparison here. Installation and a quick glimpse on both tool.

I have been astonished by the query filter/route feature in sqlrelay. I’ve setup a simple simulation in my box using two databases. And I have been happy about how I can direct my query based on some lines of regex. Working with mysql client directly and sqlrsh tool was totally smooth. However, making it works with LAMP(hp) was totally PITA. The mysql drop-in library can’t help here. Casting LD_PRELOAD spell before starting httpd was futile. Maybe it has something to do with mod_php or something else. I haven’t dig deeper. There’s only one option left — as working directly with sqlrelay-PHP API is no-option — that is using the available PEAR::DB driver for sqlrelay. That would be scwheet if only Drupal doesn’t start dropping PEAR::DB support in 4.6. Gah.

Okay, it’s now time for mysqlproxy to shine. There’s no ready library for CentOS/RHEL 5. Compiling from source. Requires Lua. Must compile Lua to get the recent 5.1.3. `make linux install` is failed on doc section. Commented out doc section. make linux install passed. but no lua.pc in any pkgconfig directory, ./configure on mysqlproxy failed. Copying lua.pc manually doesn’t work with configure. Manually use pkg-config –libs and –cflags to supply LUA_CFLAGS and LUA_LIBS for ./configure. make failed on dlopen stuff. Browsed Google. Google said: ./configure LDFLAG=’-ldl’. make && make install is OK.

Run mysqlproxy. Fired up mysql -u root -P 4041. show databases;. Errrrr. Nanni .. Nanni … Stuck. Bump. Will see you later tomorrow.




Update:


On one end:

toni@toni:~/public_html$ mysql -u root -P 4040
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0 to server version: 5.0.0

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>show databases;
ERROR:
Failed to get a cursor id.
 A network error may have ocurred. Success
mysql>

And on the other end:

toni@toni:~/Devs/mysql-proxy-0.6.1$ sudo /usr/local/sbin/mysql-proxy
file network-mysqld-proto.c: line 210 (network_mysqld_proto_get_int_len): assertion failed: (*_off < packet->len)
Aborted

Update #2:


mysql-proxy works swell today. Dunno, maybe I needed to restart mysql yesterday. mysql-proxy fives a more granular filter feature compared to sqlrelay simple regex. However, it must be paid by learning Lua.



Another tips, always use explicit mysql –host=localhost when you want to use mysql-proxy proxy feature. Otherwise your connection will likely goes via local socks, which is sucks. Eg: mysql -P 4040, this one will not connect to mysqld instance via port 4040, alas (apparently) it will use socket and ignores the port. Crazy param bastard took me forever to figure out.

Powered by ScribeFire.