Mysql-proxy installation and filter “SHOW TABLES”

What is Mysql-proxy?

MYSQL Proxy is a simple program that sits between the 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

Reason for mysql-proxy installation   

 I have been maintaining a 150 GB Mysql 5.1 Community Version Database with 1300000 tables. The database consists of all MYISAM tables. So, whenever a “SHOW TABLES” command is executed in the Database the server gets halt. As Mysql 5.1 has no privilege for SHOW TABLES, I used mysql-proxy between the web server and DB server.

Download required software

I downloaded mysql-proxy binary distribution for “Linux – Generic” from dev.mysql.com/downloads/mysql-proxy/ as my server’s operating was Centos 5.

LUA is a scripting language which is required for mysql-proxy scripting. I downloaded the following lua rpms from rpm.pbone.net.

Step 1: Install LUA

rpm -ivh lua50-5.0.3-1.i386.rpm lua50-devel-5.0.3-1.i386.rpm lua50  \               -libs-5.0.3-1.i386.rpm

Step 2: Extract mysql-proxy

cd /root/

tar -zxvf mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit.tar.gz

cp -R mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit /var/lib/

cd /var/lib/

mv mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit mysql-proxy

Step 3: Edit $PATH variable

vi .bash_profile

----------

...

export PATH=$PATH:/var/lib/mysql-proxy/bin

export MYSQLPROXYHOME=/var/lib/mysql-proxy

----------

Step 4: Create mysql-proxy configuration file

cd $MYSQLPROXYHOME


vi mysql-proxy.cnf
-------------------
[mysql-proxy]
admin-address=10.0.0.44:4401
proxy-address=10.0.0.44:4402
proxy-backend-addresses=10.0.0.44:3306
admin-username=admin
admin-password=std123
admin-lua-script=/var/lib/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-lua-script=/var/lib/mysql-proxy/lib/mysql-proxy/lua/block_show_tables.lua
pid-file=/var/lib/mysql-proxy/mysqlproxy.pid
log-file=/var/log/mysql-proxy.log
log-level=message
-------------------

Here all the addresses contain same IP addresses as I installed mysql-proxy and mysql server in the same host.

Step 5: Add scripts to block SHOW TABLES

cd /var/lib/mysql/mysql-proxy/lib/mysql-proxy/lua/

vi block_show_tables.lua

------------------------------------------

...

local tokenizer = require("proxy.tokenizer")

---

-- make_regexp_from_command()

--

-- creates a regular expression for fast scanning of the command

--

-- @param cmd the command to be converted to regexp

--

function make_regexp_from_command(cmd)

local regexp= '^%s*';

for ch in cmd:gmatch('(.)') do

regexp = regexp .. '[' .. ch:upper() .. ch:lower() .. ']'

end

return regexp

end

local SHOW_REGEXP = make_regexp_from_command('show')

local EXECUTE_REGEXP = make_regexp_from_command('execute')

local SELECT_REGEXP = make_regexp_from_command('select')

queries_to_filter = {

{

prefix = SHOW_REGEXP,

keywords = { 'SHOW', 'TABLE', 'STATUS'} ,

},

{

prefix = SHOW_REGEXP,

keywords = { 'SHOW', 'TABLES'} ,

},

{

prefix = SELECT_REGEXP,

keywords = { 'SELECT', 'FROM', 'INFORMATION_SCHEMA', 'TABLES'} ,

},

{

prefix = SELECT_REGEXP,

keywords = { 'SELECT', 'FROM', 'INFORMATION_SCHEMA', 'COLUMNS'} ,

},

{

prefix = SELECT_REGEXP,

keywords = { 'SELECT', 'FROM', 'TABLES'} ,

},

{

prefix = SELECT_REGEXP,

keywords = { 'SELECT', 'FROM', 'COLUMNS'} ,

},

{

prefix = EXECUTE_REGEXP,

keywords = { 'EXECUTE'} ,

},

}

function error_result (msg)

proxy.response = {

type = proxy.MYSQLD_PACKET_ERR,

errmsg = msg,

errcode = 7777,

sqlstate = 'X7777',

}

return proxy.PROXY_SEND_RESULT

end

function read_query( packet )

if (packet:byte() ~= proxy.COM_QUERY) then

return

end

local query = packet:sub(2)

for i,query_to_filter in pairs(queries_to_filter)

do

if query:match(query_to_filter.prefix) then

local full_tokens = tokenizer.tokenize(query)

local tokens = tokenizer.bare_tokens(full_tokens, true)

local found = 0

local requested = #query_to_filter.keywords

for j,keyword in pairs(query_to_filter.keywords) do

for k, token in pairs(tokens) do

if token:upper() == keyword then

found = found + 1

break

end

end

end

if found == requested then -- to be filtered off

local log_file = os.getenv("MYSQLPROXYHOME") .. '/mysql-proxy.log'

print (log_file)

local str = os.date('%Y-%m-%d %H:%M:%S') .. '  : ' .. proxy.connection.client.src.name .. ' executed ' .. table.concat(query_to_filter.keywords,' ')  

local file = io.open(log_file,'a+')

file:write(string.format("%s \n",str))

file:flush()

return error_result('command <' .. table.concat(query_to_filter.keywords,' ') .. '> is not allowed' )

end

end

end

end

------------------------------------------

The above script is downloaded from http://forge.mysql.com/wiki/Lua_Scripts_For_MySQL_Proxy_Examples

Step 6: Set file permission

chown –R mysql:mysql $ MYSQLPROXYHOME

chmod 660 $MYSQLPROXYHOME/mysql-proxy.cnf

cd /var/lib/mysql/mysql-proxy/lib/mysql-proxy/lua/

chmod 644 block_show_tables.lua

ls -lh

-rw-r--r-- 1 mysql mysql 2741 Sep 3 16:52 admin.lua

-rw-r--r-- 1 mysql mysql 2979 Oct 12 17:00 block_show_tables.lua

Step 7: Check if mysql-proxy is starting

/var/lib/mysql-proxy/bin/mysql-proxy --defaults-file=/var/lib/mysql \ -proxy/mysql-proxy.cnf –daemon

pidof -s "$MYSQLPROXYHOME/libexec/mysql-proxy

6411

cat $MYSQLPROXYHOME/mysqlproxy.pid

6411

Step 8: Stop mysql-proxy

kill -9 6411

Step 9: Set mysql-proxy service script at /etc/init.d/

vi /etc/init.d/mysqlproxy

--------------------------

#!/bin/sh

# Source function library.

. /etc/rc.d/init.d/functions

# Find the name of the script

NAME=`basename $0`

if [ ${NAME:0:1} = "S" -o ${NAME:0:1} = "K" ]

then

NAME=${NAME:3}

fi

export MYSQLPROXYHOME=/var/lib/mysql-proxy

export MYSQLPROXYENGINE=/var/lib/mysql-proxy/bin

script_result=0

STARTLOG=$MYSQLPROXYHOME/startup_failure.log

i=0

message_display()

{

ret=$?

if [ $ret -eq 0 ]

then

#success "$DISPLAY"

echo_success

else

#failure "$DISPLAY"

echo_failure

script_result=1

fi

echo

}

start()

{

DISPLAY=$"Starting ${NAME} service: "

echo -n $DISPLAY

pid=`pidof -s "$MYSQLPROXYHOME/libexec/mysql-proxy"`

if [ $pid ] && [ -f "$MYSQLPROXYHOME/mysqlproxy.pid" ]

then

echo "ERROR: mysql-proxy already running at pid $pid " > $STARTLOG

sleep 1

failure "$DISPLAY"

echo

script_result=1

exit 1

fi

su -l mysql -c "$MYSQLPROXYENGINE/mysql-proxy --defaults-file=$MYSQLPROXYHOME/mysql-proxy.cnf --daemon"

sleep 2

pid=`pidof -s "$MYSQLPROXYHOME/libexec/mysql-proxy"`

if [ $pid ] && [ -f "$MYSQLPROXYHOME/mysqlproxy.pid" ]

then

success "$DISPLAY"

touch /var/lock/subsys/${NAME}

head -n 1 "$MYSQLPROXYHOME/mysqlproxy.pid" > "/var/run/mysqlproxy.pid"

echo

script_result=0

else

failure "$DISPLAY"

echo

script_result=1

fi

#message_display;

} #end of start

stop()

{

DISPLAY=$"Stopping ${NAME} service: "

echo -n $DISPLAY

sleep 1

pid=`pidof -s "$MYSQLPROXYHOME/libexec/mysql-proxy"`

if [ $pid ] && [ -f "$MYSQLPROXYHOME/mysqlproxy.pid" ]

then

kill -9 "$pid"

rm -f $MYSQLPROXYHOME/mysqlproxy.pid

rm -f /var/run/mysqlproxy.pid

rm -f "/var/lock/subsys/${NAME}"

success "$DISPLAY"

echo

else

failure "$DISPLAY"

echo

script_result=1

fi

#message_display;

} # end of stop

restart()

{

stop;

start;

} #end of restart

case $1 in

start) start;;

stop) stop;;

restart) restart;;

*)

echo $"Usage: $0 {start|stop|restart}"

exit 1

esac

exit $script_result

----------------------------------------------------------------

Step 10: Start and Stop mysql-proxy with the service script

/etc/init.d/mysqlproxy start

/etc/init.d/mysqlproxy stop

Filter everything that goes to the DB server and enjoy !!!

References



http://www.chriscalender.com/?tag=proxy-functions
http://lua-users.org/wiki/OsLibraryTutorial
http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html