mysql-proxy实现读写分离脚本

来源:转载


将以下脚本保存为/etc/init.d/mysql-proxy ,赋予权限

chmod 755 /etc/init.d/mysql-proxy

#!/bin/bash
#
#mysql-proxyThisscriptstartsandstopsthemysql-proxydaemon
#
#chkconfig:-7830
#processname:mysql-proxy
#description:mysql-proxyisaproxydaemonformysql

#Sourcefunctionlibrary.
./etc/rc.d/init.d/functions

prog="/usr/local/mysql-proxy/bin/mysql-proxy"

#Sourcenetworkingconfiguration.
if[-f/etc/sysconfig/network];then
./etc/sysconfig/network
fi

#Checkthatnetworkingisup.
[${NETWORKING}="no"]&&exit0

#Setdefaultmysql-proxyconfiguration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"

#Sourcemysql-proxyconfiguration.
if[-f/etc/sysconfig/mysql-proxy];then
./etc/sysconfig/mysql-proxy
fi

RETVAL=0

start(){
echo-n$"Starting$prog:"
daemon$prog$PROXY_OPTIONS--pid-file=$PROXY_PID--proxy-address="$PROXY_ADDRESS"--user=$PROXY_USER--admin-username="$ADMIN_USER"--admin-lua-script="$ADMIN_LUA_SCRIPT"--admin-password="$ADMIN_PASSWORD"
RETVAL=$?
echo
if[$RETVAL-eq0];then
touch/var/lock/subsys/mysql-proxy
fi
}

stop(){
echo-n$"Stopping$prog:"
killproc-p$PROXY_PID-d3$prog
RETVAL=$?
echo
if[$RETVAL-eq0];then
rm-f/var/lock/subsys/mysql-proxy
rm-f$PROXY_PID
fi
}
#Seehowwewerecalled.
case"$1"in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
ifstatus-p$PROXY_PIDFILE$prog>&/dev/null;then
stop
start
fi
;;
status)
status-p$PROXY_PID$prog
;;
*)
echo"Usage:$0{start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac

exit$RETVAL

2.为服务脚本提供配置文件/etc/sysconfig/mysql-proxy,内容如下所示:

#Optionsformysql-proxy
ADMIN_USER="admin"
ADMIN_PASSWORD="admin"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_ADDRESS=""
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon--log-level=info--log-use-syslog"

#注意最后一行,需要根据实际生产场景进行修改,比如;

PROXY_OPTIONS="--daemon --log-level=info --log-file=/var/log/mysql-proxy.log --plugins=proxy

--plugins=admin --proxy-backend-addresses=192.168.141.101:3306 --proxy-read-only-backend-ad

dresses=192.168.141.102:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy

/rw-splitting.lua"

3.复制如下内容建立admin.lua文件,将其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目录中:

--[[$%BEGINLICENSE%$
Copyright(c)2007,2012,Oracleand/oritsaffiliates.Allrightsreserved.

Thisprogramisfreesoftware;youcanredistributeitand/or
modifyitunderthetermsoftheGNUGeneralPublicLicenseas
publishedbytheFreeSoftwareFoundation;version2ofthe
License.

Thisprogramisdistributedinthehopethatitwillbeuseful,
butWITHOUTANYWARRANTY;withouteventheimpliedwarrantyof
MERCHANTABILITYorFITNESSFORAPARTICULARPURPOSE.Seethe
GNUGeneralPublicLicenseformoredetails.

YoushouldhavereceivedacopyoftheGNUGeneralPublicLicense
alongwiththisprogram;ifnot,writetotheFreeSoftware
Foundation,Inc.,51FranklinSt,FifthFloor,Boston,MA
02110-1301USA

$%ENDLICENSE%$--]]
functionset_error(errmsg)
proxy.response={
type=proxy.MYSQLD_PACKET_ERR,
errmsg=errmsgor"error"
}
end

functionread_query(packet)
ifpacket:byte()~=proxy.COM_QUERYthen
set_error("[admin]weonlyhandletext-basedqueries(COM_QUERY)")
returnproxy.PROXY_SEND_RESULT
end

localquery=packet:sub(2)

localrows={}
localfields={}

ifquery:lower()=="select*frombackends"then
fields={
{name="backend_ndx",
type=proxy.MYSQL_TYPE_LONG},

{name="address",
type=proxy.MYSQL_TYPE_STRING},
{name="state",
type=proxy.MYSQL_TYPE_STRING},
{name="type",
type=proxy.MYSQL_TYPE_STRING},
{name="uuid",
type=proxy.MYSQL_TYPE_STRING},
{name="connected_clients",
type=proxy.MYSQL_TYPE_LONG},
}

fori=1,#proxy.global.backendsdo
localstates={
"unknown",
"up",
"down"
}
localtypes={
"unknown",
"rw",
"ro"
}
localb=proxy.global.backends[i]

rows[#rows+1]={
i,
b.dst.name,--configuredbackendaddress
states[b.state+1],--theC-idispusheddownstartingat0
types[b.type+1],--theC-idispusheddownstartingat0
b.uuid,--theMySQLServer'sUUIDifitismanaged
b.connected_clients--currentlyconnectedclients
}
end
elseifquery:lower()=="select*fromhelp"then
fields={
{name="command",
type=proxy.MYSQL_TYPE_STRING},
{name="description",
type=proxy.MYSQL_TYPE_STRING},
}
rows[#rows+1]={"SELECT*FROMhelp","showsthishelp"}
rows[#rows+1]={"SELECT*FROMbackends","liststhebackendsandtheirstate"}
else
set_error("use'SELECT*FROMhelp'toseethesupportedcommands")
returnproxy.PROXY_SEND_RESULT
end

proxy.response={
type=proxy.MYSQLD_PACKET_OK,
resultset={
fields=fields,
rows=rows
}
}
returnproxy.PROXY_SEND_RESULT
end

分享给朋友:
您可能感兴趣的文章:
随机阅读: