Friday, October 15, 2010

Mysql-proxy installation and filter “SHOW TABLES”

0 comments
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


Leave a Reply

Labels