Mysql-Proxy: install and configure in CentOS7


The load balancing of the mysql requests is really cool thing if you have multiple MySQL servers with master-slave replication configured. You can distribute selects between all slave servers and deliver inserts/updates to the master server only. This will slightly improve the performance of your DB cluster.

Screenshot from 2014-10-14 08:00:38

You can use mysql-proxy for this. I couldn’t find much documentation about this in the internet, that is why had to read the native documents. The following tutorial will allow you to install and configure mysql-proxy on CentOS7 with a simple “copy&paste”.

First of all we are going to need the latest version of glib

Download the source package 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

Install required packages:

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

Lets’ install glib:

cd glib-2.42.0
./configure
make
make install

You should get the following message during the execution:

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'

This means that the libraries are in the /usr/local/lib/

In order to make system detect these libraries I’ve created a symlinks.
I’d suggest to create the backup copies of the original files first:

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 we can proceed with the installation of mysql-proxy. There s a legend that it used to be available in the epel repository. Unfortunately I couldn’t find it there, so let’s download the rpm package and install it:

wget ftp://195.220.108.108/linux/fedora/linux/development/rawhide/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

Also we are going to need the source package because of the additional script:

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

Copy rw-splitting script to the mysql-proxy folder:

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

Determine the location of the config file:

rpm -qc mysql-proxy

In my case it was /etc/sysconfig/mysql-proxy. Open it in your favourite text editor and set the admin password (ADMIN_PASSWORD) – it can’t be empty.

Make PROXY_OPTIONS match the following:

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 – is the address of master server, that should handle the inserts and updates.
proxy-read-only-backend-addresses – ip addresses of the read-only nodes.
proxy-address – is the ip socket for the incoming mysql connections. It is 4040 by default.

Let’s create 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

Lets’ spin it up:

/etc/init.d/mysql-proxy start

You can check the cluster state in the admin part:

mysql -hlocalhost -P4041 -uadmin -ppassword

The following request will show all backends:

SELECT * FROM backends;

You should receive the following table:

+-------------+--------------------+---------+------+------+-------------------+
| 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 |
+-------------+--------------------+---------+------+------+-------------------+

During the work on this topic I was inspired by this and this article.

Share Button

Leave a Reply

You must be logged in to post a comment.