Wednesday, January 23, 2013

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:

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:

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.