一、简述
二、对MariaDB做主从复制
关于如何对MariaDB做主从复制，请移步本人博客http://wangfeng7399.blog.51cto.com/3518031/1394509
三、安装
1.可以通过rpm安装，其会提供配置文件及服务脚本，但是没有读写分享脚本
2.通过编译安装
①、源码安装时，MySQL proxy的依赖关系：
libevent 1.x or higher (1.3b or later is preferred).
lua 5.1.x or higher.
glib2 2.6.0 or higher.
pkg-config.
libtool 1.5 or higher.
MySQL 5.0.x or higher developer files.
②、下载源码包，编译安装
|
1
2
3
4
5
6
7
8
9
|
# tar zxf mysql-proxy-
0.8
.
2
.tar.gz
# cd mysql-proxy-
0.8
.
2
# ./configure
# make
# make check
如果管理员有密码，上面的步骤则需要使用如下格式进行：
# MYSQL_PASSWORD=root_pwd make check
# make install
默认情况下， mysql-proxy安装在/usr/local/sbin/mysql-proxy，而Lua示例脚本安装在/usr/local/share目录中。
|
3.通过通用二进制格式安装
①、下载解压。这里的系统平台为rhel6.5 64位系统
|
1
2
3
|
[root@httpweb ~]# wget http:
//mirror.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
[root@httpweb ~]# tar xf mysql-proxy-
0.8
.
4
-linux-el6-x86-64bit.tar.gz
[root@httpweb ~]# mv mysql-proxy-
0.8
.
4
-linux-el6-x86-64bit /usr/local/mysql-proxy
|
②、添加代理用户
|
1
|
[root@httpweb mysql-proxy]# useradd mysql-proxy
|
③、为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/init.d/mysql-proxy，给予执行权限，而后添加至服务列表
|
1
2
3
|
[root@httpweb mysql-proxy]# vi /etc/init.d/mysql-proxy
[root@httpweb mysql-proxy]# chmod +x /etc/init.d/mysql-proxy
[root@httpweb mysql-proxy]# chkconfig --add mysql-proxy
|
④、为服务脚本提供配置文件/etc/sysconfig/mysql-proxy
|
1
2
3
4
5
6
7
8
9
|
#Options
for
mysql-proxy
ADMIN_USER=
"wangfeng7399"
ADMIN_PASSWORD=
"wangfeng7399"
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 --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.200:3306 --proxy-read-only-backend-addresses=192.168.1.202:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
其中的proxy-backend-addresses选项和proxy-read-only-backend-addresses选项均可重复使用多次，以实现指定多个读写服务器或只读服务器。
|
⑤、mysql-proxy的配置选项
mysql-proxy的配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类，下面一起去介绍它们。
--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 ———— 进程文件名；
⑥、提供admin.lua文件，将其保存至/usr/local/mysql-proxy/share/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
|
⑦、测试
管理功能测试 
可以看到4041端口和3306端口以及处于监听状态
我们在主服务器上授予201数据库写的权限
|
1
2
|
MariaDB [(none)]> grant all on *.* to
'wangfeng7399'
@
'192.168.1.201'
identified by
'wangfeng7399'
;
MariaDB [(none)]> flush privileges;
|
|
1
2
|
[root@httpweb mysql-proxy]# mysql -uwangfeng7399 -pwangfeng7399 -h192.
168.1
.
201
--port=
3306
我们可以看到我们在mysql-proxy的可以登录数据库了
|
查看是否配置成功
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[root@httpweb ~]# mysql -uwangfeng7399 -pwangfeng7399 -h192.
168.1
.
201
--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
Copyright (c)
2000
,
2013
, Oracle and/or its affiliates. All rights reserved.
Oracle
is
a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
mysql> select * from backends;
+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
|
1
|
192.168
.
1.200
:
3306
| up | rw | NULL |
0
|
|
2
|
192.168
.
1.202
:
3306
| up | ro | NULL |
0
|
+-------------+--------------------+---------+------+------+-------------------+
|
我们的程序就可以实现真正意义上的读写分离了，大功告成，由于本人水平有限，请各位大神多多批评指正
本文转自wangfeng7399 51CTO博客，原文链接：http://blog.51cto.com/wangfeng7399/1395039，如需转载请自行联系原作者
