基於mysql-proxy實現讀寫分離的啟動指令碼

標籤：基於mysql-proxy實現讀寫分離

#!/bin/bash##chkconfig: 2345 94 06#. /etc/rc.d/init.d/functions#載入指令碼設定檔，提供變數值if [ -e /usr/mysql-proxy.conf ] ; then. /usr/mysql-proxy.conffi#查看是否安裝mysql-proxy軟體if ! `cd /usr/local/mysql-proxy > /dev/null` ; thentar xf /root/$VER -C /usr/local/ln -s /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit /usr/local/mysql-proxyfistart() {/usr/local/mysql-proxy/bin/mysql-proxy --daemon --log-level=$LOGLEVEL --log-file="$LOGFILE" --plugins=‘proxy‘ --proxy-backend-addresses="$MASTERADDR" --proxy-read-only-backend-addresses="$SLAVEADDR" --proxy-lua-script=‘$PROXYLUA‘ --plugins=‘admin‘ --admin-username="$ADMINUSER" --admin-password="$PASSWD" --admin-lua-script="$ADMINLUA"}stop() {killall mysql-proxy}#查看代理運行狀態時，需保證本機裝有mysql用戶端程式status() {mysql -u$ADMINUSER -h$PROXYADDR -p$PASSWD --port=$ADMINPORT -e"select * from backends"}test() {if `netstat -ntpl | grep ‘mysql-proxy‘ > /dev/null` ; thenSTAT=OKelseSTAT=FAULTfi}case $1 instart)starttestecho "Start mysql-proxy...[$STAT]";;stop)stopecho "stop mysql-proxy...[OK]";;restart)stopecho "stop mysql-proxy...[OK]"starttestecho "Start mysql-proxy...[$STAT]";;status)status;;*|help)echo "Usage:$0 {start|stop|restart|status|help]";;esac

需要載入的設定檔，提供變數值

VER="mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz"

LOGLEVEL="info"

LOGFILE="/var/log/mysql-proxy.log"

MASTERADDR="192.168.1.51:3306"

SLAVEADDR="192.168.1.53:3306"

PROXYLUA="/usr/local/mysql-proxy/share/doc/mysql-proxy/rm-splitting.lua"

ADMINUSER="admin"

PASSWD="admin"

ADMINLUA="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXYADDR=‘192.168.1.54‘

PROXYPORT=4040

ADMINPORT=4041

admin.lua指令碼

function set_error(errmsg)

proxy.response = {

type = proxy.MYSQLD_PACKET_ERR,

errmsg = errmsg or "error"

}

end

 

function read_query(packet)

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

set_error("[admin] we only handle text-based queries (COM_QUERY)")

return proxy.PROXY_SEND_RESULT

end

 

local query = packet:sub(2)

 

local rows = { }

local fields = { }

 

if query:lower() == "select * from backends" 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 },

}

 

for i = 1, #proxy.global.backends do

local states = {

"unknown",

"up",

"down"

}

local types = {

"unknown",

"rw",

"ro"

}

local b = proxy.global.backends[i]

 

rows[#rows + 1] = {

i,

b.dst.name,          -- configured backend address

states[b.state + 1], -- the C-id is pushed down starting at 0

types[b.type + 1],   -- the C-id is pushed down starting at 0

b.uuid,              -- the MySQL Server‘s UUID if it is managed

b.connected_clients  -- currently connected clients

}

end

elseif query:lower() == "select * from help" then

fields = {

{ name = "command",

  type = proxy.MYSQL_TYPE_STRING },

{ name = "description",

  type = proxy.MYSQL_TYPE_STRING },

}

rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }

rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }

else

set_error("use ‘SELECT * FROM help‘ to see the supported commands")

return proxy.PROXY_SEND_RESULT

end

 

proxy.response = {

type = proxy.MYSQLD_PACKET_OK,

resultset = {

fields = fields,

rows = rows

}

}

return proxy.PROXY_SEND_RESULT

end

基於mysql-proxy實現讀寫分離的啟動指令碼