1：master：192.168.0.17
slave:192.168.0.18
mysql-proxy:192.168.0.21
2:主从复制安装就不叙述了 .
3:下载mysql-proxy：http://dev.mysql.com/downloads/mysql-proxy/
tar xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
centos7上是在网易源上下载的：http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
yum install -y lua
mv mysql-proxr....... /usr/local/mysql-proxy
useradd -r mysql-proxy
4:vim /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/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
<---------------------------------------------------------
chmod +x /etc/init.d/mysql-proxy
chkconfig --add mysql-proxy
5:vim /etc/sysconfig/mysql-proxy
--------------------------------------------------------->
# 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
--plugins=proxy
--plugins=admin
--proxy-backend-addresses=192.168.0.17:3306
--proxy-read-only-backend-addresses=192.168.0.18:3306
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
#--daemon：以守护进程模式启动mysql-proxy
#--proxy-backend-addresses：后端可读写的mysql服务器的地址和端口
#--proxy-read-only-backend-addresses：后端只读mysql服务器的地址和端口
#--proxy-lua-script：完成mysql代理功能的Lua脚本
6:vim /usr/local/mysql-proxy/share/doc/mysql-proxy/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
7:vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
--------------------------------------------------------------------------->
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //默认为4
max_idle_connections = 1, //默认为8
is_debug = false
}
end
//mysql-proxy会检测客户端连接，当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。
8:service mysql-proxy start
ss -tnlp
9:在mysql-proxy服务器上
yum -y install mysql （安装mysql客户端）
mysql -uadmin -padmin -h192.168.0.21 --port=4041
---------------------------------------------------------------------------->
读写分离测试：
10：在master机器上
GRANT ALL ON *.* TO 'jason'@'192.168.0.%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;
分别在两台机器上安装tcpdump准备抓包
master：tcpdump -i eth0 -nn -XX ip dst 192.168.0.17 and tcp dst port 3306
slave：tcpdump -i eth0 -nn -XX ip dst 192.168.0.18 and tcp dst port 3306
11：mysql-proxy上进行数据库操作
mysql -ujason -p123456 -h192.168.0.21
------------------------------------------------->
CREATE DATABASE hello1;
USE mysql;
SELECT * FROM user; //可以用额外的主机多执行几次
在master上的抓包信息：
在slave上的抓包信息：
12：查看状态，在proxy上操作，可以看到状态全部为up：
mysql -uadmin -padmin -h192.168.0.21 --port=4041
13：问题
有时候只读机器会启动不起来，总是unknown的状态，这时候需要把slave stop下；进行读写分离测试后，之后在slave start。
编辑回复