Installing MySQL Proxy On CentOS 5 (FINAL) x86_64

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

On a fresh minium 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