MYSQL-PROXY INStall
先yum安装必须的库，同时解决pkg-config、libtool和Mysql开发库，由于mysql-proxy实际并不需要在本机上运行mysql实例，所以用yum安装。
1.yum -y install gcc gcc-c++ autoconf mysql-devel libtool pkgconfig ncurses ncurses-devel
libevent安装libevent-2.0.13版本，从此处可以下载：
运行脚本：
# tar xvf libevent-2.0.13-stable.tar.gz
# cd libevent-2.0.13-stable
# ./configure
# make && make install
glib2安装glib-2.18.4版本，最新版本安装报错，从此处可以下载：
运行脚本：
# tar xvf glib-2.18.4.tar.gz
# cd glib-2.18.4
# ./configure
# make && make install
lua安装5.1.4版本，安装之前需要先安装readline 6.1，不然会报错缺少头文件：
readline 6.1下载：
lua 5.1.4下载：
运行脚本：
# tar xvf readline-6.1.tar.gz
# cd readline-6.1
# ./configure
# make && make install
#应用ldconfig –v
# ldconfig –v
# lua
# tar xvf lua-5.1.4.tar.gz
# cd lua-5.1.4
# 64位系统，需在CFLAGS里加上-fPIC
# vim src/Makefile
CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS)
# make linux
# make install
# pkg-config 环境变量
# cp etc/lua.pc /usr/local/lib/pkgconfig/
# export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib/pkgconfig
以上操作完成了先决条件的安装，接下来是Mysql Proxy安装，下载完后运行：
# tar xvf mysql-proxy-0.8.2.tar.gz
# cd mysql-proxy-0.8.2
# ./autogen.sh
# cp /usr/bin/libtool ./
# ./configure
# make && make install
接下来：
# cp lib/rw-splitting.lua /usr/local/lib/
# cp lib/admin.lua /usr/local/lib/
到这里MySQL-proxy已基本安装完成，接下来，测试：
echo "ulimit -n 8192" >> /etc/profile
source /etc/profile
1.1 设置说明
Master服务器: 192.168.20.9
Slave服务器: 192.168.192.168.20.10
Proxy服务器: 192.168.20.12
1.2 mysql-proxy选项说明
# mysql-proxy --help-all
管理功能选项:
--admin-address=host:port 指定一个mysqo-proxy的管理端口, 缺省是4041;
--admin-username= username to allow to log in
--admin-password= password to allow to log in
--admin-lua-script= script to execute by the admin plugin
代理功能选项:
-P, --proxy-address= 是mysql-proxy 服务器端的监听端口, 缺省是4040;
-r, --proxy-read-only-backend-addresses= 只读Slave的地址和端口, 缺省为不设置;
-b, --proxy-backend-addresses= 远程Master地址和端口, 可设置多个做failover和load balance, 缺省是127.0.0.1:3306;
--proxy-skip-profiling 关闭查询分析功能, 缺省是打开的;
--proxy-fix-bug-25371 修正 mysql的libmysql版本大于5.1.12的一个#25371号bug;
-s, --proxy-lua-script= 指定一个Lua脚本来控制mysql-proxy的运行和设置, 这个脚本在每次新建连接和脚本发生修改的的时候将重新调用;
其他选项:
--defaults-file=配置文件, 可以把mysql-proxy的参数信息置入一个配置文件里;
--daemon mysql-proxy以守护进程方式运行
--pid-file=file 设置mysql-proxy的存储PID文件的路径
--keepalive try to restart the proxy if it crashed, 保持连接启动进程会有2个, 一号进程用来监视二号进程, 如果二号进程死掉自动重启proxy.
1.3数据库准备工作
1) 安装半同步补丁(建议)
读写分离不能回避的问题之一就是延迟, 可以考虑Google提供的SemiSyncReplication补丁.
2) 给用户授权
在Master/Slave建立一个测试用户, 因为以后客户端发送的SQL都是通过mysql-proxy服务器来转发, 所以要确保可以从mysql-proxy服务器上登录MySQL主从库.
mysql> grant all privileges on *.* to identified by 'u_test' with grant option;
3) 在Master建立测试表
mysql> create table db_test.t_test (col varchar(10));
mysql> insert into db_test.t_test values ('testA');
mysql> select * from db_test.t_test;
+-------+
| col |
+-------+
| testA |
+-------+
1.4 mysql-proxy启动
1) 修改读写分离lua脚本
默认最小4个最大8个以上的客户端连接才会实现读写分离, 现改为最小1个最大2个:
# vi +40 /usr/local/lib/rw-splitting.lua
------------------------------------------------------
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 2,
is_debug = true
}
end
------------------------------------------------------
这是因为mysql-proxy会检测客户端连接, 当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上.
2) 启动mysql-proxy
建议使用配置文件的形式启动, 注意配置文件必须是660权限, 否则无法启动. 如果有多个Slave的话, proxy-read-only-backend-addresses参数可以配置多个以逗号分隔的IP:Port从库列表.
# killall mysql-proxy
# vi /etc/mysql-proxy.cnf
[mysql-proxy]
admin-username=zhaoyf
admin-password=iamzhaoyf
admin-lua-script=/usr/local/lib/admin.lua
proxy-backend-addresses=192.168.20.9:3306
proxy-read-only-backend-addresses=192.168.20.10:3306
proxy-lua-script=/usr/local/lib/rw-splitting.lua
log-file=/var/log/mysql-proxy.log
log-level=debug
daemon=true
keepalive=true
# chmod 660 /etc/mysql-proxy.cnf
# mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
# ps -ef | grep mysql-proxy | grep -v grep
root 1869 1 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root 1870 1869 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
1.5 客户端连接测试
1) 先停止Slave的复制进程
mysql> stop slave;
2) 连接Proxy端口, 插入数据
# mysql -uu_test –pu_test -h192.168.20.12 -P4040 -Ddb_test
mysql> insert into db_test.t_test values ('testB');
mysql> select * from db_test.t_test;
+-------+
| col |
+-------+
| testA |
| testB |
+-------+
3) 多开几个客户端, 连接Proxy端口, 查询数据
# mysql -uu_test –pu_test -h192.168.20.12 -P4040 -Ddb_test
mysql> select * from db_test.t_test;
+-------+
| col |
+-------+
| testA |
+-------+
如果查询不到上步新插入的数据, 说明连接到了Slave, 读写分离成功. 在同一线程再插入数据并验证:
mysql> insert into db_test.t_test values ('testC');
mysql> select * from db_test.t_test;
+-------+
| col |
+-------+
| testA |
+-------+
发现insert操作成功, 但是select不出刚插入的数据, 说明同一线程也读写分离成功. 从日志中可以验证:
[root@localhost ~]# tail /var/log/mysql-proxy.log
2011-08-30 16:25:44: (message) Initiating shutdown, requested from mysql-proxy-cli.c:604
2011-08-30 16:25:44: (message) shutting down normally, exit code is: 0
2011-08-30 16:25:57: (debug) chassis-unix-daemon.c:121: we are the child: 4092
2011-08-30 16:25:57: (message) mysql-proxy 0.8.2 started
2011-08-30 16:25:57: (debug) max open file-descriptors = 1024
2011-08-30 16:25:57: (message) proxy listening on port :4040
2011-08-30 16:25:57: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=4092 alive2011-08-30 16:25:57: (message) added read/write backend: 192.168.20.9:3306
2011-08-30 16:25:57: (message) added read-only backend: 192.168.20.10:3306
2011-08-30 16:25:57: (debug) chassis-unix-daemon.c:157: waiting for 4092
5. 建立mysql-proxy启动脚本
touch /etc/init.d/mysql-proxy
chmod 777 /etc/init.d/mysql-proxy
vi /etc/init.d/mysql-proxy
#!/bin/sh
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql
# Source function library.
. /etc/rc.d/init.d/functions
PROXY_PATH=/usr/local/mysql-proxy/bin
prog="mysql-proxy"
# Source networking configuration.
. /etc/sysconfig/network
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
#PROXY_OPTIONS="--daemon"
PROXY_OPTIONS="--daemon --defaults-file=/etc/mysql-proxy.cnf"
PROXY_PID=/var/run/mysql-proxy.pid
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH
# By default it's all good
RETVAL=0
# See how we were called.
case "$1" in
start)
# Start daemon.
echo -n $"Starting $prog: "
daemon $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --pid-file $PRO
XY_PID
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
;;
stop)
# Stop daemons.
echo -n $"Stopping $prog: "
killproc $prog
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
;;
restart)
$0 stop
sleep 3
$0 start
;;
condrestart)
[ -e /var/lock/subsys/mysql-proxy ] && $0 restart
;;
status)
status mysql-proxy
RETVAL=$?
;;
*)
echo "Usage: $0 {start|stop|restart|status|condrestart}"
RETVAL=1
;;
esac
exit $RETVAL
6. 配置自动启动：
chkconfig --add mysql-proxy
chkconfig mysql-proxy on
