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.gzcp -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/binexport 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('(.)') doregexp = regexp .. '[' .. ch:upper() .. ch:lower() .. ']'endreturn regexpendlocal 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_RESULTendfunction read_query( packet )if (packet:byte() ~= proxy.COM_QUERY) thenreturnendlocal query = packet:sub(2)for i,query_to_filter in pairs(queries_to_filter)doif query:match(query_to_filter.prefix) thenlocal full_tokens = tokenizer.tokenize(query)local tokens = tokenizer.bare_tokens(full_tokens, true)local found = 0local requested = #query_to_filter.keywordsfor j,keyword in pairs(query_to_filter.keywords) dofor k, token in pairs(tokens) doif token:upper() == keyword thenfound = found + 1breakendendendif 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' )
endendendend------------------------------------------
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 $ MYSQLPROXYHOMEchmod 660 $MYSQLPROXYHOME/mysql-proxy.cnfcd /var/lib/mysql/mysql-proxy/lib/mysql-proxy/lua/chmod 644 block_show_tables.luals -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-proxy6411cat $MYSQLPROXYHOME/mysqlproxy.pid6411
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 scriptNAME=`basename $0`if [ ${NAME:0:1} = "S" -o ${NAME:0:1} = "K" ]thenNAME=${NAME:3}fiexport MYSQLPROXYHOME=/var/lib/mysql-proxyexport MYSQLPROXYENGINE=/var/lib/mysql-proxy/binscript_result=0STARTLOG=$MYSQLPROXYHOME/startup_failure.logi=0message_display(){ret=$?if [ $ret -eq 0 ]then#success "$DISPLAY"echo_successelse#failure "$DISPLAY"echo_failurescript_result=1fiecho}start(){DISPLAY=$"Starting ${NAME} service: "echo -n $DISPLAYpid=`pidof -s "$MYSQLPROXYHOME/libexec/mysql-proxy"`if [ $pid ] && [ -f "$MYSQLPROXYHOME/mysqlproxy.pid" ]thenecho "ERROR: mysql-proxy already running at pid $pid " > $STARTLOGsleep 1failure "$DISPLAY"echoscript_result=1exit 1fisu -l mysql -c "$MYSQLPROXYENGINE/mysql-proxy --defaults-file=$MYSQLPROXYHOME/mysql-proxy.cnf --daemon"sleep 2pid=`pidof -s "$MYSQLPROXYHOME/libexec/mysql-proxy"`if [ $pid ] && [ -f "$MYSQLPROXYHOME/mysqlproxy.pid" ]thensuccess "$DISPLAY"touch /var/lock/subsys/${NAME}head -n 1 "$MYSQLPROXYHOME/mysqlproxy.pid" > "/var/run/mysqlproxy.pid"echoscript_result=0elsefailure "$DISPLAY"echoscript_result=1fi#message_display;} #end of startstop(){DISPLAY=$"Stopping ${NAME} service: "echo -n $DISPLAYsleep 1pid=`pidof -s "$MYSQLPROXYHOME/libexec/mysql-proxy"`if [ $pid ] && [ -f "$MYSQLPROXYHOME/mysqlproxy.pid" ]thenkill -9 "$pid"rm -f $MYSQLPROXYHOME/mysqlproxy.pidrm -f /var/run/mysqlproxy.pidrm -f "/var/lock/subsys/${NAME}"success "$DISPLAY"echoelsefailure "$DISPLAY"echoscript_result=1fi#message_display;} # end of stoprestart(){stop;start;} #end of restartcase $1 instart) start;;stop) stop;;restart) restart;;*)echo $"Usage: $0 {start|stop|restart}"exit 1esacexit $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