基于mysql-proxy实现读写分离的启动脚本

435243

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

#!/bin/bash # #chkconfig: 2345 94 06 # . /etc/rc.d/init.d/functions #加载脚本配置文件，提供变量值 if [ -e /usr/mysql-proxy.conf ] ; then     . /usr/mysql-proxy.conf fi #查看是否安装mysql-proxy软件 if ! `cd /usr/local/mysql-proxy > /dev/null` ; then         tar xf /root/$VER -C /usr/local/         ln -s /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit /usr/local/mysql-proxy     fi start() {     /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` ; then         STAT=OK     else         STAT=FAULT     fi } case $1 in     start)         start         test         echo "Start mysql-proxy...[$STAT]"     ;;     stop)         stop         echo "stop mysql-proxy...[OK]"     ;;     restart)         stop         echo "stop mysql-proxy...[OK]"         start         test         echo "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

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