实验拓扑图：
实验环境说明：MySQL主从已经部署完成，并且授权mysql-proxy主机数据库权限
系统 centos 6.4 _ x86_64
192.168.0.101 mysql-proxy
192.168.0.102 mysql-master
192.168.0.103 mysql-slave
部署开始：
检查依赖包
[root@shell tools]# rpm -q lualua-5.1.4-4.1.el6.x86_64
2.下载通用的压缩包 mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz 直接解压使用
useradd -r mysql-proxywget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gztar zxf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz -C /applicationln -s /application/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit /application/mysql-proxy
3.环境变量配置
echo "export PATH=/application/mysql-proxy/bin:$PATH" >> /etc/profilesource /etc/profile
4.启动mysql-proxy
[root@mysql-proxy ~]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy --proxy-backend-addresses="192.168.0.102:3306" --proxy-read-only-backend-addresses="192.168.0.103:3306" --proxy-lua-script="/application/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
5.检查启动结果
[root@mysql-proxy lib]# netstat -nltpActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 971/sshd tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1274/mysql-proxy tcp 0 0 :::22
# 此时只能实现简单的读写分离
# 添加启动脚本和管理查询模块
6.为mysql-proxy服务脚本提供配置文件/etc/sysconfig/mysql-proxy
# Options for mysql-proxy ADMIN_USER="admin"ADMIN_PASSWORD="admin"ADMIN_ADDRESS=""#ADMIN_ADDRESS="0.0.0.0:4040"ADMIN_LUA_SCRIPT="/application/mysql-proxy/share/doc/mysql-proxy/admin.lua"PROXY_ADDRESS=""PROXY_USER="mysql-proxy"#PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog"
7.编辑mysql-proxy依赖的admin.lua脚本
cat >> /application/mysql-proxy/share/doc/mysql-proxy/admin.lua <
8. 编辑mysql-proxy启动脚本
cat >> /etc/init.d/mysql-proxy#!/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/functionsprog="/application/mysql-proxy/bin/mysql-proxy"# Source networking configuration.if [ -f /etc/sysconfig/network ]; then . /etc/sysconfig/networkfi# Check that networking is up.[ ${NETWORKING} = "no" ] && exit 0# Set default mysql-proxy configuration.ADMIN_USER="admin"ADMIN_PASSWD="admin"ADMIN_LUA_SCRIPT="/application/mysql-proxy/share/doc/mysql-proxy/admin.lua"PROXY_OPTIONS="--daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.0.102:3306 --proxy-read-only-backend-addresses=192.168.0.103:3306"PROXY_PID=/var/run/mysql-proxy.pidPROXY_USER="mysql-proxy"PROXY_LUA="/application/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"# Source mysql-proxy configuration.if [ -f /etc/sysconfig/mysql-proxy ]; then . /etc/sysconfig/mysql-proxyfiRETVAL=0start() { 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 ;;esacexit $RETVALEOF chmod 700 /etc/init.d/mysql-proxy
9. 重启mysql-proxy
/etc/init.d/mysql-proxy restart
10.检查启动结果
[root@mysql-proxy application]# netstat -nltpoActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 971/sshd tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1396/mysql-proxy tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 1396/mysql-proxy tcp 0 0 :::22 :::* LISTEN 971/sshd
说明： 4040 是mysql-proxy 本身监听的端口
4040 是mysql-proxy 管理模块监听的端口
11.此时登陆管理模块，查看后端状态
mysql -uroot -P 4040 -p -h192.168.0.101 -e "select user,host from mysql.user"mysql> select * from backends;+-------------+--------------------+---------+------+------+-------------------+| backend_ndx | address | state | type | uuid | connected_clients |+-------------+--------------------+---------+------+------+-------------------+| 1 | 192.168.0.102:3306 | unknown | rw | NULL | 0 || 2 | 192.168.0.103:3306 | unknown | ro | NULL | 0 |+-------------+--------------------+---------+------+------+-------------------+2 rows in set (0.00 sec)
#由于此时没有进行任何操作，所以主从状态都是unknown
12.执行查询操作，查看主从状态变化
mysql -uroot -P 4040 -p -h192.168.0.101 -e "show databases;"mysql> select * from backends;+-------------+--------------------+---------+------+------+-------------------+| backend_ndx | address | state | type | uuid | connected_clients |+-------------+--------------------+---------+------+------+-------------------+| 1 | 192.168.0.102:3306 | up | rw | NULL | 0 || 2 | 192.168.0.103:3306 | unknown | ro | NULL | 0 |+-------------+--------------------+---------+------+------+-------------------+2 rows in set (0.00 sec)
# 由于主是支持读写的，所以发现主状态变为了up是正常现象，要想观看到从的状态，需要多次查询
mysql -uroot -P 4040 -p -h192.168.0.101 -e "select user,host from mysql.user"mysql> select * from backends;+-------------+--------------------+-------+------+------+-------------------+| backend_ndx | address | state | type | uuid | connected_clients |+-------------+--------------------+-------+------+------+-------------------+| 1 | 192.168.0.102:3306 | up | rw | NULL | 0 || 2 | 192.168.0.103:3306 | up | ro | NULL | 0 |+-------------+--------------------+-------+------+------+-------------------+2 rows in set (0.00 sec)
#此时发现主从状态全部变成了up状态