Mysql-Proxy: installing and configuring on CentOS7

Balancing mysql queries matters if you’re running several mysql servers. If you’ve got master-slave replication set up, it makes sense to spread the load across multiple servers: send update/insert queries to the master, and distribute select queries across two, three, or more servers.

The mysql-proxy utility handles this. Unfortunately there’s not much documentation on it out there. Figured I’d fill that gap.

So first we need the latest glib. I did all this on CentOS7.

Download the source and unpack it:

wget http://ftp.gnome.org/pub/gnome/sources/glib/2.42/glib-2.42.0.tar.xz  
tar xvf glib-2.42.0.tar.xz

If unpacking fails, install xz-utils or just the xz package and try again.

These packages are needed for the build to go right:

yum install lua lua-devel libevent libevent-devel glib2 glib2-devel pkg-config mysqlclient14-devel libffi mysql libffi-devel zlib zlib-devel gcc gettext-devel glibc glibc-devel

Now go into the glib folder and install it:

cd glib-2.42.0  
./configure  
make  
make install

You’ll see a message like this along the way:

Libraries have been installed in:
   /usr/local/lib

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to '/etc/ld.so.conf'

So the libraries ended up in /usr/local/lib/

For the system to see them, I made symlinks after backing up the original files:

mv /usr/lib64/libglib-2.0.so.0 /usr/lib64/libglib-2.0.so.0.bak  
mv /usr/lib64/libglib-2.0.so /usr/lib64/libglib-2.0.so.bak
ln -s /usr/local/lib/libglib-2.0.so.0.4200.0 /usr/lib64/libglib-2.0.so.0  
ln -s /usr/local/lib/libglib-2.0.so.0.4200.0 /usr/lib64/libglib-2.0.so

Now on to installing mysql-proxy. Supposedly it used to be in the Epel repo. Couldn’t find it there though. So download the prebuilt package and install it:

wget ftp://195.220.108.108/linux/fedora/linux/releases/22/Everything/x86_64/os/Packages/m/mysql-proxy-0.8.5-1.fc22.x86_64.rpm  
rpm -ihv mysql-proxy-0.8.5-1.fc22.x86_64.rpm

You’ll also need the original source package for an extra script. Download and unpack it:

wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz  
tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

That magic script, which splits read and write queries, needs to go into the mysql-proxy folder:

cp share/doc/mysql-proxy/rw-splitting.lua /usr/lib64/mysql-proxy/lua/proxy/

Find where the config file lives with:

rpm -qc mysql-proxy

In my case it’s /etc/sysconfig/mysql-proxy. Open it in your favorite editor and set a password for admin (ADMIN_PASSWORD) — it can’t be empty.

Set PROXY_OPTIONS to look like this:

PROXY_OPTIONS=`-log-level=info \  
-proxy-address=:3306 \  
-log-use-syslog \  
-plugins=proxy \  
-plugins=admin \  
-proxy-backend-addresses=192.168.1.143:3306 \  
-proxy-read-only-backend-addresses=192.168.1.132 \  
-proxy-lua-script=/usr/lib64/mysql-proxy/lua/proxy/rw-splitting.lua`

• proxy-backend-addresses — the master server’s address, where insert and update queries go.
• proxy-read-only-backend-addresses — the server address that only gets select queries.
• proxy-address — the ip and port that handle incoming connections. Defaults to 4040

Create the symlinks:

cd /usr/lib64  
ln -s /opt/mysql-proxy/lib/libmysql-chassis.so.0.0.0 libmysql-chassis.so.0  
ln -s /opt/mysql-proxy/lib/libmysql-proxy.so.0.0.0 libmysql-proxy.so.0  
ln -s /opt/mysql-proxy/lib/libmysql-chassis-glibext.so.0.0.0 libmysql-chassis-glibext.so.0  
ln -s /opt/mysql-proxy/lib/libevent-2.0.so.5.1.9 libevent-2.0.so.5  
ln -s /opt/mysql-proxy/lib/libmysql-chassis-timing.so.0.0.0 libmysql-chassis-timing.so.0

Now you can start it:

/etc/init.d/mysql-proxy start

To check the cluster’s state, connect to the admin console:

mysql -hlocalhost -P4041 -uadmin -ppassword

To see all the backends run this query:

SELECT * FROM backends;

You get back a table like this:

+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address            | state   | type | uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
|           1 | 192.168.1.143:3306 | unknown | rw   | NULL |                 0 |
|           2 | 192.168.1.132:3306 | unknown | ro   | NULL |                 0 |
+-------------+--------------------+---------+------+------+-------------------+

Wrote this with inspiration from this articles.