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 slaves2) 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.

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:

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 thesetwo blog posts by the author of rw-splitting.lua, Jan Kneschke. For general usage, this O'Reilly article by Giuseppe Maxia is very good.

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.

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 :)

@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.

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.

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.

Great tutorial but i have some questions , ihave my shell script like this and works at first time greatThis proxy is running on a third different machine user provileges are ok on master and slave #!/bin/bashMASTERDB=ip1SLAVE=ip2ROOT_DIR=/usrLUA_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

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 connection2010-10-20 06:26:00: (critical) network-mysqld.c.1188: plugin_call(CON_STATE_READ_QUERY) failed

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

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.

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.