mysql读写分离
环境是在先前配置mysql主从复制的2台机器的基础上（IP分别是10.10.10.13为主服务器和10.10.10.14为从服务器），启用一台10.10.10.12的机器安装mysql-proxy进行读写分离实践。
mysql-proxy是一个MySQL的代理服务器，用户的请求先发向mysql-proxy，然后mysql-proxy对用户的数据包进行分析，从下一层的mysql 数据库中选择一台数据库，将用户的请求包交给mysql处理。
一 ,MySQL-Proxy调度服务器上
检查系统所需软件包
yum -y install gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*
1，编译安装lua
# wget http://www.lua.org/ftp/lua-5.1.5.tar.gz
--2016-07-28 07:20:50-- http://www.lua.org/ftp/lua-5.1.5.tar.gz
Resolving www.lua.org... 148.251.24.173, 2a01:4f8:201:620f::2001
Connecting to www.lua.org|148.251.24.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 221213 (216K) [application/gzip]
Saving to: ua-5.1.5.tar.gz
100%[===========================================================================>] 221,213 30.7K/s in 7.0s
2016-07-28 07:21:01 (30.7 KB/s) - ua-5.1.5.tar.gzsaved [221213/221213]
2，下载完后发现系统居然已经安装了lua了。
#rpm -qa |grep lua
lua-5.1.4-4.1.e16.i686
#
3，那就开始安装配置MySQL-Proxy
# wget http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz
--2016-07-28 07:50:05-- http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz
Resolving mirrors.sohu.com... 221.236.12.140
Connecting to mirrors.sohu.com|221.236.12.140|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 11825715 (11M) [application/octet-stream]
Saving to: ysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz
100%[===========================================================================>] 11,825,715 562K/s in 21s
2016-07-28 07:50:26 (556 KB/s) - ysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gzsaved [11825715/11825715]
#tar zxvf mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz -C /opt/
#mv /opt/mysql-proxy-0.8.4-linux-rhel5-x86-32bit /opt/mysql-proxy
#vi 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/bin
PROXY_PATH=/opt/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=" --proxy-read-only-backend-addresses=10.10.10.14:3306 --proxy-backend-addresses=10.10.10.13:3306 --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"
PROXY_PID=/opt/mysql-proxy/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: "
$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=warning --log-
file=/opt/mysql-proxy/log/mysql-proxy.log
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
5，设置执行环境
#cp mysql-proxy /opt/mysql-proxy/init.d/
#chmod +x /opt/mysql-proxy/init.d/mysql-proxy
#mkdir /opt/mysql-proxy/run
#mkdir /opt/mysql-proxy/log
#mkdir /opt/mysql-proxy/scripts
6，配置并使用rw-splitting.lua读写分离脚本
[root@hd2 mysql-proxy]# find ./ -name *split*
./share/doc/mysql-proxy/rw-splitting.lua
[root@hd2 mysql-proxy]# cp ./share/doc/mysql-proxy/rw-splitting.lua /opt/mysql-proxy/scripts
[root@hd2 mysql-proxy]# vim /opt/mysql-proxy/scripts/rw-splitting.lua
修改读写分离脚本rw-splitting.lua，修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 1,
is_debug = false
}
end
7，修改完成后，启动mysql-proxy
[root@hd2 mysql-proxy]# /opt/mysql-proxy/init.d/mysql-proxy start
Starting mysql-proxy:
[root@hd2 mysql-proxy]# more log/mysql-proxy.log
2016-07-28 08:31:37: (critical) plugin proxy 0.8.4 started
[root@hd2 mysql-proxy]# ps aux |grep pro
mysql 7879 0.0 0.2 3912 1256 ? S 08:31 0:00 /opt/mysql-proxy/libexec/mysql-proxy --proxy-read-only-backend-addresses=10.10.10.14:3306 --proxy-backend-addresses=10.10.10.13:3306 --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua --daemon --pid-file=/opt/mysql-proxy/run/mysql-proxy.pid --user=mysql --log-level=warning --log-file=/opt/mysql-proxy/log/mysql-proxy.log
root 7886 0.0 0.1 4356 732 pts/2 S+ 08:32 0:00 grep pro
[root@hd2 mysql-proxy]#
二，准备测试读写分离效果
1，主服务器上创建用于读写分离的数据库连接用户
mysql> GRANT ALL ON *.* TO 'myproxy'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fuck;
+------+------+
| id | name |
+------+------+
| 1 | abcd |
| 9 | fst |
+------+------+
2 rows in set (0.00 sec)
mysql>
2，由于我们配置了主从复制功能，因此从数据库服务器10.10.10.14上已经同步了此操作
3，为了测试读写分离的效果，暂时关闭MySQL主从复制功能
登陆从数据库服务器10.10.10.14，通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p'aaaaaa'
关闭Slave同步进程
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
4，在mysql-proxy上（10.10.10.12）连接主服务器测试
# mysql -uproxy1 -p'password' -h10.10.10.13
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.73-log Source distribution
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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use fuck;
Database changed
mysql> insert into fuck values(009,'fst');
Query OK, 1 row affected (0.00 sec)
mysql> select * from fuck;
+------+------+
| id | name |
+------+------+
| 1 | abcd |
| 9 | fst |
+------+------+
2 rows in set (0.01 sec)
mysql>
5，从服务器此时情况
mysql> select * from fuck;
+------+-------+
| id | name |
+------+-------+
| 1 | abcd |
+------+-------+
1 rows in set (0.00 sec)
mysql>
说明没有发生任何改变，没有新记录存在，已经实现了MySQL读写分离，目前所有的写操作都全部在Master主服务器上。
6，客户端连接测试
先看看mysql-proxy服务端口
# netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 7879/mysql-proxy
再在客户端上连接（客户端需有mysql的client端，没有就yum -y install mysql安装一下，很快的）
# mysql -uproxy1 -ppassword -h 10.10.10.12 --port 4040
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.73-log Source distribution
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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> use fuck;
Database changed
mysql> select * from fuck;
+------+------+
| id | name |
+------+------+
| 1 | abcd |
| 9 | fst |
+------+------+
2 rows in set (0.00 sec)
从服务器上看见的内容
# tcpdump -i eth1 port 3306
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth1, link-type EN10MB (Ethernet), capture size 65535 bytes
00:20:46.163384 IP 10.10.10.12.45070 > 10.10.10.13.mysql: Flags [P.], seq 46:69, ack 265, win 658, options [nop,nop,TS val 61366259 ecr 94221439], length 23
00:20:46.163622 IP 10.10.10.13.mysql > 10.10.10.12.45070: Flags [P.], seq 265:397, ack 69, win 453, options [nop,nop,TS val 94222286 ecr 61366259], length 132
00:20:46.163668 IP 10.10.10.12.45070 > 10.10.10.13.mysql: Flags [.], ack 397, win 691, options [nop,nop,TS val 61366260 ecr 94222286], length 0
00:20:46.707339 IP 10.10.10.12.45070 > 10.10.10.13.mysql: Flags [P.], seq 69:92, ack 397, win 691, options [nop,nop,TS val 61366803 ecr 94222286], length 23
00:20:46.707639 IP 10.10.10.13.mysql > 10.10.10.12.45070: Flags [P.], seq 397:529, ack 92, win 453, options [nop,nop,TS val 94222830 ecr 61366803], length 132
00:20:46.707724 IP 10.10.10.12.45070 > 10.10.10.13.mysql: Flags [.], ack 529, win 725, options [nop,nop,TS val 61366804 ecr 94222830], length 0
^C
12 packets captured
13 packets received by filter
0 packets dropped by kernel
