Open Source Software Technical Articles

Want the Best of the Wazi Blogs Delivered Directly to your Inbox?

Subscribe to Wazi by Email

Your email:

Connect with Us!

Current Articles | RSS Feed RSS Feed

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.




This work is licensed under a Creative Commons Attribution 3.0 Unported License
Creative Commons License.

Comments

Great posts. Exactly what I was looking for. Just curious, are you currently using this in production? I noticed that in the MySQL documentation it states: 
 
MySQL Proxy is currently an Alpha release and should not be used within production environments.
Posted @ Monday, February 11, 2013 11:06 AM by Brett Millett
I am personally not using it in production but there are some quite big sites using it. If you are looking for a fully enterprise-ready solution, you can check similar commercial products such as GreenSQL.
Posted @ Monday, February 11, 2013 11:16 AM by Anatoli
Currently I'm just looking at a two-server, master-slave replication where read/writes are separated for Wordpress. Where this seems, by far, the easiest option to implement, I think I might run it through its paces. My goals are high availability and load balancing. Also Looking at HA-proxy (but it seems to be much more complex at forst glance.) I prefer an all open solution (and the budget is relatively low.) Thanks again!
Posted @ Monday, February 11, 2013 11:29 AM by Brett Millett
Really helpful
Posted @ Thursday, April 11, 2013 10:14 PM by jadd
Your instructions works great. But when I type in SELECT * FROM backends; to check the status of my backend servers, they all have "Unknown" State. Does this mean that my mysql-proxy is having problem communicating with the backend servers?
Posted @ Tuesday, April 23, 2013 1:06 PM by Mike Wong
@Brett Millett: If you're just using wordpress, then I would suggest the HyperDB module instead: 
 
http://wordpress.org/extend/plugins/hyperdb/ 
 
It's not "alpha" software and it's pretty simple to configure.
Posted @ Sunday, May 12, 2013 12:55 AM by Ajay
The EPEL repository version of MySQL Proxy is 0.8.2, however the latest version is 0.8.3. 
 
Anyone know where best to install 0.8.3 for CentOS 6.x from? 
 
Thanks, 
Chris
Posted @ Tuesday, July 16, 2013 4:12 AM by Chris Rosillo
Will this proxy handle failovers? For instance, if the Master goes down, will it start writing to the Slave?
Posted @ Monday, July 29, 2013 9:42 AM by Justin Foley
The nodes does not go to state 'UP' which was default unknown, and proxy goes down after few requests,I think it dont work..
Posted @ Wednesday, October 02, 2013 4:52 AM by dileep
very well written and helping, just was wondering if I configure two servers with --proxy-backend-addresses option then rw-splitting.lua will be able to distribute the load accordingly? 
 
both servers are set as master to master replication..
Posted @ Tuesday, February 11, 2014 2:34 AM by Irfan
This handbag above printed in total of 34 cute cartoon dog pattern, and http://facrygoo.org.uk each has a name. Set on the dogs must be on the Marc Jacobs handbags are well, at least I know, "Alfred," and "Daisy" is mark their two hounds, is his own or other made-up to ancient Rome, a word appeared on the handbags are "Marc Jacobs?" the dog. (and, listed in September, about 600 RMB). In Marc ugly doll series, the dog also became protagonist, Miss Marc with her three puppies out of the door, the face of positive and negative of bag is the most lovely just also corresponds to the ugly baby and Fake Iwc Watches puppy dogs from positive and negative images. If you like the dog, so this is your most should have handbags, back with your dog out of the door, it will be a street most tide and attractive girl.
Posted @ Tuesday, October 07, 2014 11:44 PM by vuitton
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics