Simple database load balancing with MySQL Proxy

http://www.openlogic.com/wazi/bid/259864/Simple-database-load-balancing-with-MySQL-Proxy


MySQL Proxy transparently passes information between a client and a MySQL server. The proxy can audit the information flow in both directions and change it if necessary, which could be useful for protecting the MySQL server from malicious queries or for altering the information clients receive without actually making changes to the database. The proxy can also do load balancing between MySQL servers, and perform flow optimization by directing SELECT statements to read-only slave servers, which enhances MySQL scalability by allowing you to add more servers for read operations.
In many Linux package managers the MySQL Proxy package can be found under the name mysql-proxy. In CentOS the package is available from the EPEL repository. EPEL provides many additional packages that are not available from the main CentOS repository. If you don't have the EPEL repository installed, in CentOS 6 you can install it with the command rpm -ivh http://ftp-stud.hs-esslingen.de/pub/epel/6/i386/epel-release-6-8.noarch.rpm. Once you've added the EPEL repository, you can install MySQL Proxy with the command yum install mysql-proxy, then make sure it starts and stops automatically along with the system by running the command chkconfig mysql-proxy on.

Configuration

Unfortunately, MySQL Proxy and its CentOS package are not well documented. It requires some ingenuity to configure it and get started. Here are some tips to aid you.
The configuration file for MySQL Proxy is /etc/sysconfig/mysql-proxy, as you can confirm with the command rpm -qc mysql-proxy, where the argument q stands for query and c for configuration files. You can always use this command on CentOS when you are not sure about the configuration files of a package.
Inside the /etc/sysconfig/mysql-proxy file you can set the following options:

• ADMIN_USER – the user for the proxy's admin interface. You can leave the default admin user.
• ADMIN_PASSWORD – the password for the admin user in clear text. Change the default password for better security.
• ADMIN_LUA_SCRIPT – the admin script in the Lua programming language. Without this script the admin interface cannot work. You can leave the default value.
• PROXY_USER – the system user under which the proxy will work. By default it is mysql-proxy, and it's safe to leave it as is.
• PROXY_OPTIONS – proxy options such as logging level, plugins, and Lua scripts to be loaded.

The most important configuration directive is the PROXY_OPTIONS. A good example for it looks like:
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.102:3306 --proxy-read-only-backend-addresses=192.168.1.105:3306 --proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua"
With these settings, logging is set to the info level (--log-level=info) through the system's syslog (--log-use-syslog), which means all system messages from the proxy go to the file /var/log/messages.
Two plugins are to be used – proxy (--plugins=proxy), which provides the core proxy functionality, and admin (--plugins=admin), which gives users an admin interface with useful information about the back-end servers, as we will see later.
The backend servers are specified – one read/write (--proxy-backend-addresses=192.168.1.102:3306) and one only for reading, meaning only SELECT statements (--proxy-read-only-backend-addresses=192.168.1.105:3306). The read-only servers should be replicated from the master read/write server. You can specify more read and write servers according to your MySQL replication design, and all queries will be evenly distributed using a round-robin algorithm. This is useful for load balancing and failover because the proxy will not forward queries to a failed server.
The last setting is a Lua script for splitting queries into reads and writes (--proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua). This is one of the most useful features of the MySQL Proxy. It allows offloading the master MySQL servers and forwarding SELECT statements to optimized-for-reads slave servers.
This Lua script by default is not included in the EPEL package. To acquire it, you have to download the official MySQL Proxy package. From the download options choose the generic Linux archive, which is currently called mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz. Once you extract this package you can find the rw-splitting.lua script in the newly extracted directory mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/. (Say that three times fast.) Copy the script from there to /usr/lib/mysql-proxy/lua/proxy/ on the proxy server.
That newly created directory contains many other example Lua scripts that you can play with and use even without fully understanding the Lua language. In the case of most scripts, their names suggest their purpose. For example, the auditing.lua script is used for auditing, and tutorial-query-time.lua gives you the time of queries.

Monitoring

Once you complete the setup you can start MySQL Proxy with the command mysql proxy start on CentOS. In the /var/log/messages file you should see output indicating a successful start, such as:

Jan 14 21:54:08 server2 mysql-proxy: 2013-01-14 21:54:08: (message) mysql-proxy 0.8.2 started
Jan 14 21:54:08 server2 mysql-proxy: 2013-01-14 21:54:08: (message) proxy listening on port :4040
Jan 14 21:54:08 server2 mysql-proxy: 2013-01-14 21:54:08: (message) added read/write backend: 192.168.1.102:3306
Jan 14 21:54:08 server2 mysql-proxy: 2013-01-14 21:54:08: (message) added read-only backend: 192.168.1.105:3306

To test the proxy you need to set up MySQL replication first. Once you have replication working you can import a sample database, such as the
After you've had some activity through the proxy you can check its status and begin monitoring. To do this, use the admin interface, which is accessible by a MySQL client on the server's port 4041. If your MySQL Proxy has an IP address of 192.168.1.201, for example, you can connect to its admin interface with the command mysql --host=192.168.1.201 --port=4041 -u admin -psecr3t_pass. The admin login ID and password are the ones specified in /etc/sysconfig/mysql-proxy.
The admin interface is simple and usually (depending on the Lua admin script) allows only the command SELECT * FROM backends;. On a properly working MySQL Proxy this command should give output such as:

+-------------+--------------------+-------+------+------+-------------------+
| backend_ndx | address            | state | type | uuid | connected_clients |
+-------------+--------------------+-------+------+------+-------------------+
|           1 | 192.168.1.102:3306 | up    | rw   | NULL |                 0 |
|           2 | 192.168.1.105:3306 | up    | ro   | NULL |                 0 |
+-------------+--------------------+-------+------+------+-------------------+

The above table shows the addresses of the servers, their state, type – read/write (rw) or read-only (ro) – uuid, and number of connected clients.
You can also play with the rest of the Lua scripts included in the official archive. To test a new script, just copy it to the /usr/lib/mysql-proxy/lua/proxy/ directory on the MySQL Proxy server and include it in the PROXY_OPTIONS directive.
MySQL Proxy is a simple yet powerful utility. Even though it provides some challenges today in terms of scanty documentation and sketchy ease of use, it is under continuous development and shows constant improvement.