Thursday, April 30, 2009

MySQL load balancing and read-write splitting with MySQL Proxy

This is just a quick post which aims to say something positive about MySQL Proxy. I've been reading lots of negative blog and forum posts about it, with people complaining that it doesn't work for them. It works for us, and it works pretty well too. First things first though -- what is MySQL Proxy? Here's what the documentation says:

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.

Two fairly common usage scenarios for MySQL Proxy are:

1) load balancing across MySQL slaves
2) splitting reads and writes so that reads go to the slave DB servers and writes go to the master DB server

Of course, you don't need MySQL Proxy to accomplish these goals. For slave load balancing, you can use a regular load balancer in front of your slaves. For read-write splitting, you can have your application use different DB servers for reads and writes....but that may require significant changes to your application.

If you want to make things faster in terms of read performance by sending reads to a pool of slave DB servers, while still sending writes to a master DB, AND do all this without modifying your application, then MySQL Proxy might be just the ticket for you. Before you go down that path, let me say that if you make heavy use of MySQL prepared statements, you might be out of luck. In my testing, MySQL Proxy did not support prepared statements well.

Here's a short tutorial on using MySQL Proxy:

1) Download the binary package from the download page. I tried to install it from source, but I ran into some mysterious link issues with Lua libraries. If you didn't know already, MySQL Proxy uses Lua as its scripting language for doing the tricks it's capable of doing; not sure why the authors chose Lua, I suspect it's because of its compactness (the binary version of MySQL Proxy includes the Lua interpreter, so you don't need to install Lua separately.)

In my case I downloaded mysql-proxy-0.6.0-linux-rhas3-x86_64.tar.gz, untar-ed it in ROOT_DIR, then created a symlink called mysql-proxy in ROOT_DIR pointing to mysql-proxy-0.6.0-linux-rhas3-x86_64.

The actual binary is in ROOT_DIR/mysql-proxy/sbin and it's called mysql-proxy. You can run it with --help to see what command-line options it takes.

2) Run mysql-proxy and let it do both slave load balancing and read/write splitting. Load balancing is achieved by specifying the command-line switch --proxy-read-only-backend-addresses, while r/w splitting is achieved by specifying on the command line the script , which is in /mysql-proxy/share/mysql-proxy/rw-splitting.lua

Here is a script that I use to run mysql-proxy with the options I need, and in daemon mode. The master DB server is specified with the --proxy-backend-addresses cmdline switch. An important bit in the script is setting LUA_PATH and pointing it to the directory containing the Lua scripts. If you don't do it, the rw-splitting.lua script won't be found, and you won't know about it until you hit mysql-proxy. You'll then see errors around the script not being found.

Note that LUA_PATH is on the same line as the invocation of the mysql-proxy binary.

#!/bin/bash

MASTERDB=10.1.1.1
SLAVEDB01=10.2.1.1
SLAVEDB02=10.3.1.1
SLAVEDB03=10.4.1.1
ROOT_DIR=/usr/local

LUA_PATH="$ROOT_DIR/mysql-proxy/share/mysql-proxy/?.lua" $ROOT_DIR/mysql-proxy/sbin/mysql-proxy \
--daemon \
--proxy-backend-addresses=$MASTERDB:3306 \
--proxy-read-only-backend-addresses=$SLAVEDB01:3306 \
--proxy-read-only-backend-addresses=$SLAVEDB02:3306 \
--proxy-read-only-backend-addresses=$SLAVEDB03:3306 \
--proxy-lua-script=$ROOT_DIR/mysql-proxy/share/mysql-proxy/rw-splitting.lua


3) Now you have mysql-proxy running on its default port 4040 and ready for you to use. To use it, simply point your web application to 127.0.0.1:4040 instead of MASTER_DB_SERVER:3306. You can also connect to mysql-proxy with the regular mysql command-line client by running:

mysql -uroot -p -h127.0.0.1 -P 4040

4) To start mysql-proxy at boot time, here's a very simple init.d script which assumes you saved the script above in /var/scripts/run_mysql_proxy_rw_splitting.sh:


~# cat /etc/init.d/mysql-proxy
#!/bin/bash
#
# mysql-proxy: Start mysql-proxy in daemon mode
#
# Author: OpenX
#
# chkconfig: - 99 01
# description: Start mysql-proxy in daemon mode with r/w splitting
# processname: mysql-proxy


start(){
echo "Starting mysql-proxy..."
/var/scripts/run_mysql_proxy_rw_splitting.sh
}

stop(){
echo "Stopping mysql-proxy..."
killall mysql-proxy
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo "Usage: mysql-proxy {start|stop|restart}"
exit 1
esac

That's about it in a nutshell. There's much more to explore about the capabilities of MySQL Proxy, and I encourage you to read the main page and the articles linked to on that page. In terms of read/write splitting, the most helpful ones are these two blog posts by the author of rw-splitting.lua, Jan Kneschke. For general usage, this O'Reilly article by Giuseppe Maxia is very good.

14 comments:

Anonymous said...

Thanks for this simple to understand blog post.

Easiest introduction to using mysql-proxy I've seen yet!

P.S. The formatting of the bash script seems to be messed up...

The line that has LUA_PATH should have a linefeed after the path. The lua invocation --daemon etc. etc. should come on a fresh line...

david said...

I am interested in the style of your writing to be so interesting to read

danesc said...

Thank you for sharing all this, however, I still don't understand where I set the username and password for the master and slave databases? I have this exact same problem and your post seems to be the solution to my problem.... but then I get stock on the authentication part.

Thank you in advance.

goulah said...

Just wanted to verify, are you using this in a production environment and under heavy load?

Grig Gheorghiu said...

goulah -- yes, we're using this technique in production, under *very* heavy load, and so far we haven't seen any issues with it.

Grig

Unknown said...

Hi Grig,


You have no problem with mysql-proxy 0.6 and the last version of Jan Kneschke's rw lua script ? (lost/closed connexions)

Basically, I'm testing an architecture with mysql-proxy, rw splitting and replication and I had several problems when I used your kind of configuration. This why I have to use mysql-proxy v0.7.1 and a patch for the lua script... but maybe I did something wrong.

However, I'm quite happy to read that mysql-proxy with rw splitting could be used in production under heavy load :)

please forgive my poor english level

Garp said...

@danesec - MySQL Proxy doesn't actually open up connections to the database, it just acts as a transparent go-between for your application to the database.

From an application / script writing perspective, access it exactly as you would a MySQL server, e.g. you do the authentication in the application at connection level.
The only difference is instead of pointing your connection attempt at a server you're pointing it at the proxy.

Haridas N said...

Yes ... me too felt the same thing of easy description about the mysql proxy and lua script.Also I'm very happy with the set of scripts that you are provided , thats helps me to understand the things very easily.

Thank you
Haridas N

zogness said...

What if I had three master MySQL servers and wanted to use mysql-proxy to "dispatch" connections from my nineteen app servers to the appropriate MySQL server.
That way all the app servers would be pointed at a single hostname or IP and that single mysql-proxy server would redirect the requests to whichever MySQL machine contained the database it needed. Each app server has multiple JBoss instances running on it and each JBoss instance has a single database located on a particular MySQL server.

Has anyone ever done this?
I don't know what to call it. It's not load balancing and not clustering either. It would be a way to obviate the need to change config files on each app server when moving databases to different servers.

Anonymous said...

Great tutorial but i have some questions , ihave my shell script like this and works at first time great
This proxy is running on a third different machine user provileges are ok on master and slave
#!/bin/bash
MASTERDB=ip1
SLAVE=ip2
ROOT_DIR=/usr
LUA_PATH="$ROOT_DIR/share/mysql-proxy/?.lua" \
/usr/sbin/mysql-proxy --daemon --proxy-backend-addresses=$MASTERDB:3306 --proxy-read-only-backend-addresses=$SLAVE:3306 --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

when i see ps ax:
22811 ? S 0:00 /usr/sbin/mysql-proxy --daemon --proxy-backend-addresses=209.236.74.99:3306 --proxy-read-only-backend-addresses=216.119.144.114:


so all is fine till i stop the master, i thought slave can take selects on a case of failover but
i had this
:
2010-10-20 06:26:00: (critical) proxy-plugin.c.1129: I have no server backend, closing connection
2010-10-20 06:26:00: (critical) network-mysqld.c.1188: plugin_call(CON_STATE_READ_QUERY) failed

it lost the mastr but i seems dont see the slave why?

Unknown said...

Thank you for that great posting. I got it running on a test system. Unfortunatelly MySQL Proxy seems to have serious trouble with temporary tables. I just can't get it running. Maybe someone has an idea on how to solve it. I have posted a question on stackoverflow:
http://stackoverflow.com/questions/5341159/mysql-proxy-r-w-replication-and-temporary-tables

Anton said...

Did you try some stress tool to verify your proxy load? For example, this is a proxy IP list load results from that site. I think that proxy-ip-list.com is not related with MySQL proxy load balancing but that tool is very good to check your approach.

BTW, great read!

Unknown said...

As MySQL Proxy is no longer available for download and MySQL recommends using Router (https://dev.mysql.com/downloads/router/) instead, anyone have any experience configuring read-write splitting using MySQL Router. If so, please share how all the write queries can be directed to master and all read-only query to slave.

Unknown said...

You can use MaxScale directly from MariaDB website or if you run on AWS you can use SQLSplteer from the Marketplace https://www.youtube.com/embed/IGkEdceW0so

Modifying EC2 security groups via AWS Lambda functions

One task that comes up again and again is adding, removing or updating source CIDR blocks in various security groups in an EC2 infrastructur...