Install MySQL Proxy On CentOS

This tutorial explains how you can install MySQL Proxy on a CentOS 5 (x86_64). MySQL Proxy is a simple program that sits between your client and MySQL server that can monitor, analyze and transform their communication. Its flexibility allows an unlimited number of uses; common include: load balancing, failover; query analysis, filtering and application modification, and many others.

At a minimum Centos 5 final x86_64 install:

yum
install gcc.x86_64 libevent.x86_64 libevent-devel.x86_64
readline.x86_64 readline-devel.x86_64 ncurses.x86_64
ncurses-devel.x86_64 glib2.x86_64 glib2-devel.x86_64

cd /usr/local/src/

wget http://www.lua.org/ftp/lua-5.1.3.tar.gz

tar zxvf lua-5.1.3.tar.gz

cd lua-5.1.3

make linux

make install

wget
http://dev.mysql.com/get/Downloads/MySQL-Cluster-6.2/mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23.tar.gz/\
from/http://www.mirrorservice.org/sites/ftp.mysql.com/

tar xzvf mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23.tar.gz

ln -s mysql-5.1.23-ndb-6.2.15-linux-x86_64-glibc23 mysql

PATH=$PATH:/usr/local/mysql/bin

export PATH

Edit your .profile to make this permanent:

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
       . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:/usr/local/mysql/bin:$HOME/bin

export PATH
unset USERNAME

wget
http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.6.1.tar.gz/from/http://www.mirrorservice.org/sites/ftp.mysql.com/

tar zxvf mysql-proxy-0.6.1.tar.gz

cd mysql-proxy-0.6.1

./configure LDFLAGS="-lm -ldl" LUA_CFLAGS="-I/usr/local/include/" LUA_LIBS=/usr/local/lib/liblua.a

make

make install

Let's create a sample LUA script so you can see some logs.

mkdir /var/log/mysql-proxy/

mkdir -p /usr/local/mysql/lua-scripts/

vi /usr/local/mysql/lua-scripts/simple-log.lua

(see: http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=3

Script modified to get IP and to use proxy.connection.server.thread_id.)

local log_file = '/var/log/mysql-proxy/mysql.log'
local fh = io.open(log_file, "a+")

function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
 local query = string.sub(packet, 2)
 fh:write( string.format("%s %6d -- %s :IP %s :USER: %s\n",
 os.date('%Y-%m-%d %H:%M:%S'),
 proxy.connection.server.thread_id,
 query,
 proxy.connection.client.address,
 proxy.connection.client.username))
fh:flush()
end
end

Now start up your proxy using the variable --proxy-backend-addresses to point the proxy at your servers.

/usr/local/sbin/mysql-proxy
--proxy-lua-script=/usr/local/mysql/lua-scripts/simple-log.lua
--proxy-backend-addresses=192.168.1.33:3306
--proxy-backend-addresses=192.168.1.34:3306 --daemon

192.168.1.33 and 192.168.1.34 are the MySQL nodes that the proxy will be connecting to.

Allow connections for the proxy through your firewall:

### ALLOWED TO CONNECT TO MYSQL PROXY
###
### LOCAL ADMINS
-A INPUT -s SRC-IP -d DST-IP -p tcp -m state --state NEW -m tcp --dport 4040 -j ACCEPT

Where DST-IP is my proxy server and SRC-IP is my local box (client machine).

Now
from your local box (not the mysql-proxy server) try and connect to the
backend databases through the proxy ( user with relevent permissions
must exist in the db).

mysql -u dba_admin -p -h PROXY-SERVER -P 4040

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16 to server version: 5.1.23-ndb-6.2.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| Imap_Forms |
| mysql |
| test |
+--------------------+

4 rows in set (0.01 sec)

mysql> quit

Bye

N.B. The proxy uses the port 4040 instead of 3306.

Test the mysql-proxy admin interface from the mysql-proxy server:

mysql -u root -p -h 127.0.0.1 -P 4041

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.1.20-agent MySQL Enterprise Agent

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from proxy_connections;

+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | server | 10 | |
+------+--------+-------+------+

3 rows in set (0.00 sec)

mysql>quit

bye

Job done! Now read on:

http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html

http://forge.mysql.com/wiki/MySQL_Proxy

http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=1

♦DiggIt! ♦Add to del.icio.us ♦Add to Technorati Faves