一、可以使用rpm包安装，需要安装epel软件包库，或者直接从官网下载rpm包；
二、安装配置mysql-proxy:
2.1 下载所需要的版本
这里的系统平台为rhel6.4 32位系统，因此就以mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit.tar.gz为例。
|
1
2
3
|
tar
xf mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit.
tar
.gz -C
/usr/local
cd
/usr/local
ln
-sv mysql-proxy-0.8.1-linux-glibc2.3-x86-32bit mysql-proxy
|
添加代理用户
|
1
|
useradd
mysql-proxy
|
2.2 为mysql-proxy提供SysV服务脚本，内容如下所示
|
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
62
63
64
65
66
67
68
69
70
71
72
73
74
|
#!/bin/bash
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
.
/etc/rc
.d
/init
.d
/functions
prog=
"/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if
[ -f
/etc/sysconfig/network
];
then
.
/etc/sysconfig/network
fi
# Check that networking is up.
[ ${NETWORKING} =
"no"
] &&
exit
0
# Set default mysql-proxy configuration.
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"
# Source mysql-proxy configuration.
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 -
eq
0 ];
then
touch
/var/lock/subsys/mysql-proxy
fi
}
stop() {
echo
-n $
"Stopping $prog: "
killproc -p $PROXY_PID -d 3 $prog
RETVAL=$?
echo
if
[ $RETVAL -
eq
0 ];
then
rm
-f
/var/lock/subsys/mysql-proxy
rm
-f $PROXY_PID
fi
}
# See how we were called.
case
"$1"
in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
if
status -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
|
将上述内容保存为/etc/rc.d/init.d/mysql-proxy，给予执行权限，而后加入到服务列表。
|
1
2
|
chmod
+x
/etc/rc
.d
/init
.d
/mysql-proxy
chkconfig --add mysql-proxy
|
2.3 为服务脚本提供配置文件/etc/sysconfig/mysql-proxy，内容如下所示：
|
1
2
3
4
5
6
7
8
|
# Options for mysql-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"
|
其中最后一行，需要按实际场景进行修改，例如：
|
1
|
PROXY_OPTIONS=
"--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.21:3306 --proxy-read-only-backend-addresses=192.168.1.211:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
|
其中的proxy-backend-addresses选项和proxy-read-only-backend-addresses选项均可重复使用多次，以实现指定多个读写服务器或只读服务器。
2.4 mysql-proxy的配置选项
mysql-proxy的配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类，下面一起去介绍它们。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
--help
--help-admin
--help-proxy
--help-all ———— 以上四个选项均用于获取帮助信息；
--proxy-address=host:port ———— 代理服务监听的地址和端口；
--admin-address=host:port ———— 管理模块监听的地址和端口；
--proxy-backend-addresses=host:port ———— 后端mysql服务器的地址和端口；
--proxy-
read
-only-backend-addresses=host:port ———— 后端只读mysql服务器的地址和端口；
--proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本；
--daemon ———— 以守护进程模式启动mysql-proxy；
--keepalive ———— 在mysql-proxy崩溃时尝试重启之；
--log-
file
=
/path/to/log_file_name
———— 日志文件名称；
--log-level=level ———— 日志级别；
--log-use-syslog ———— 基于syslog记录日志；
--plugins=plugin,.. ———— 在mysql-proxy启动时加载的插件；
--user=user_name ———— 运行mysql-proxy进程的用户；
--defaults-
file
=
/path/to/conf_file_name
———— 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识；
--proxy-skip-profiling ———— 禁用profile；
--pid-
file
=
/path/to/pid_file_name
———— 进程文件名；
|
3、复制如下内容建立admin.lua文件，将其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目录中。
|
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
--[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and
/or
its affiliates. All rights reserved.
This program is
free
software; you can redistribute it and
/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed
in
the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License
for
more
details.
You should have received a copy of the GNU General Public License
along with this program;
if
not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA
$%ENDLICENSE%$ --]]
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
|
4、测试
4.1 管理功能测试
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
# mysql -uadmin -padmin -192.168.1.22 --port=4041
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 1
Server version: 5.0.99-agent-admin
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the buffer.
mysql> SELECT * FROM backends;
+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address | state |
type
| uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
| 1 | 192.168.1.21:3306 | up | rw | NULL | 0 |
| 2 | 192.168.1.211:3306 | unknown | ro | NULL | 0 |
+-------------+--------------------+---------+------+------+-------------------+
2 rows
in
set
(0.00 sec)
|
4.2 读写分离测试
|
1
|
mysql -uroot -h192.168.1.22 --port=4040 -p
|
本文转自 wzlinux 51CTO博客，原文链接：http://blog.51cto.com/wzlinux/1614918，如需转载请自行联系原作者
