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.

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:

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=http://www.openlogic.com/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=http://www.openlogic.com/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.

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 -p<em>secr3t_pass</em>. 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.

Copyright © 2013 IDG Communications, Inc.

Merging MySQL Tables With phpMyAdmin

Nathan McGinty started writing in 1995. He has a Bachelor of Science in communications from the University of Texas at Austin and a Master of Arts in international journalism from City University, London. He has worked in the technology industry for more than 20 years, in positions ranging from tech support to marketing.

Exam Schedule

The following schedule has been designed and approved with the expectation that students must plan to take up to two examinations per day. Questions about this schedule or policy should be directed to successcenter@calvin.edu. (See below regarding classes that meet four days per week and common exam times for some classes.)

Exams for subjects which have meetings in both the Monday/Wednesday/Friday (MWF) and Tuesday/Thursday (TT) sequences should be scheduled according to the sequence in which they have the greater number of times. If a class meets an equal number of times in each sequence, the examination should be scheduled according to the sequence which shows an earlier date or time in the examination schedule.

i.e., for MTWF or MWTHF courses, refer to the MWF examination time. For MTWTH of MTTHF courses, find both the MWF exam time and the TT exam time—your exam is scheduled for whichever date/time is earlier.

All sections of Accounting 203 and 204 have a common exam on Saturday, December 10, 9:00 a.m. All sections of Mathematics 171, 172, and 271 have a common exam on Saturday, December 10, 9:00 a.m.

All sections of Mathematics 270 will have a combined final exam, date and time TBA.