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:

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
make install

You should get the following message during the execution:

Libraries have been installed in:

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/'

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/ /usr/lib64/
mv /usr/lib64/ /usr/lib64/

ln -s /usr/local/lib/ /usr/lib64/
ln -s /usr/local/lib/ /usr/lib64/

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:

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:

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= \
proxy-read-only-backend-addresses= \

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/
ln -s /opt/mysql-proxy/lib/
ln -s /opt/mysql-proxy/lib/
ln -s /opt/mysql-proxy/lib/
ln -s /opt/mysql-proxy/lib/

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 | | unknown | rw   | NULL |                 0 |
|           2 | | 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.