MySQL学习笔记之十：使用mysql-proxy实现MySQL读写分离

TOUVE

　　MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
　　本文使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。对于多节点slave集群，还可以起到负载均衡的效果。
　　一、准备实验环境

　　MySQL的主从复制架构搭建详见http://9124573.blog.51cto.com/9114573/1785454
　　二、安装配置mysql-proxy
　　1、这里下载的mysql-proxy版本为mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
　　# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local
　　# cd /usr/local
　　# ln -sv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy
　　# useradd -r mysql-proxy
[root@node4 ~]# tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/　　
[root@node4 ~]# ln -s /usr/local/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy
　　
[root@node4 ~]# cd /usr/local/mysql-proxy
　　
[root@node4 mysql-proxy]# ls
　　
bin  include  lib  libexec  licenses  share
　　
[root@node4 mysql-proxy]# ls bin
　　
mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
　　
[root@node4 mysql-proxy]# ls share/doc/mysql-proxy/
　　
active-queries.lua       commit-obfuscator.lua  README              tutorial-constants.lua  tutorial-prep-stmts.lua  tutorial-scramble.lua  xtab.lua
　　
active-transactions.lua  commit-obfuscator.msc  ro-balance.lua      tutorial-inject.lua     tutorial-query-time.lua  tutorial-states.lua
　　
admin-sql.lua            COPYING                ro-pooling.lua      tutorial-keepalive.lua  tutorial-resultset.lua   tutorial-tokenize.lua
　　
analyze-query.lua        histogram.lua          rw-splitting.lua    tutorial-monitor.lua    tutorial-rewrite.lua     tutorial-union.lua
　　
auditing.lua             load-multi.lua         tutorial-basic.lua  tutorial-packets.lua    tutorial-routing.lua     tutorial-warnings.lua
　　
[root@node4 mysql-proxy]# useradd -r mysql-proxy
　　2、为mysql-proxy提供SysV服务脚本
　　# vim /etc/rc.d/init.d/mysql-proxy
　　...(内容略）
　　# chmod +x /etc/rc.d/init.d/mysql-proxy
　　# chkconfig --add mysql-proxy
[root@node4 ~]# vim /etc/rc.d/init.d/mysql-proxy　　
...
　　
[root@node4 ~]# chmod +x !$
　　
chmod +x /etc/rc.d/init.d/mysql-proxy
　　
[root@node4 ~]# chkconfig --add mysql-proxy
　　3、为服务脚本提供配置文件/etc/sysconfig/mysql-proxy，内容如下所示：
　　# Options for mysql-proxy
　　ADMIN_USER="admin"   #mysql-proxy的管理账号
　　ADMIN_PASSWORD="admin"
　　ADMIN_ADDRESS=""
　　ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"   #完成mysql-proxy管理功能的脚本
　　PROXY_ADDRESS=""
　　PROXY_USER="mysql-proxy"   #以什么身份运行mysql-proxy
　　PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog"
　　其中最后一行，需要按实际场景进行修改，例如：
　　PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.30.10:3306 --proxy-read-only-backend-addresses=192.168.30.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
　　其中的proxy-backend-addresses选项和proxy-read-only-backend-addresses选项均可重复使用多次，以实现指定多个读写服务器或只读服务器;
　　rw-splitting.lua是实现读写分离功能的脚本文件
[root@node4 ~]# 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.30.10:3306 \
　　
--proxy-read-only-backend-addresses=192.168.30.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
　　4、mysql-proxy命令的配置选项
　　我们也可使用mysql-proxy命令启动服务进程，其配置选项大致可分为帮助选项、管理选项、代理选项及应用程序选项几类：
　　--help
　　--help-admin
　　--help-proxy
　　--help-all   #以上四个选项均用于获取帮助信息；
　　--proxy-address=host:port   #代理服务监听的地址和端口
　　--admin-address=host:port   #管理模块监听的地址和端口，默认为0.0.0.0:4041
　　--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   #进程文件名；
　　5、创建admin.lua文件，将其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目录中（略）
[root@node4 ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua　　
...
　　6、启动mysql-proxy
　　service mysql-proxy start
　　网上都说mysql-proxy默认的代理服务端口是4040，本例中为3306，没有查到原因，有可能是新版本所做的更改；如果想使用其它端口，使用选项--proxy-address修改即可；
[root@node4 ~]# service mysql-proxy start　　
Starting /usr/local/mysql-proxy/bin/mysql-proxy:           [  OK  ]
　　
[root@node4 ~]# netstat -tanp
　　
...
　　
tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      52446/mysql-proxy
　　
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      52446/mysql-proxy
　　7、管理mysql-proxy
　　mysql -u admin -h 192.168.30.14 -P 4041 -p
　　mysql> select * from backends;   #查看后端mysql服务器状态
[root@node5 ~]# mysql -u admin -h 192.168.30.14 -P 4041 -p　　
Enter password:
　　
ERROR 1045 (28000): password doesn't match
　　
[root@node5 ~]# mysql -u admin -h 192.168.30.14 -P 4041 -p
　　
Enter password:
　　
...
　　
mysql> select * from backends;
　　
+-------------+--------------------+-------+------+------+-------------------+
　　
| backend_ndx | address            | state | type | uuid | connected_clients |
　　
+-------------+--------------------+-------+------+------+-------------------+
　　
|           1 | 192.168.30.10:3306 | up    | rw   | NULL |                 0 |
　　
|           2 | 192.168.30.20:3306 | up    | ro   | NULL |                 0 |
　　
+-------------+--------------------+-------+------+------+-------------------+
　　
2 rows in set (0.00 sec)
　　三、测试
　　1、在主库上创建一个用户

　　grant select,insert,create,delete on *.* to proxytest@'192.168.30.%'>　　flush privileges;
　　该操作会同步到从节点，故无需再在从库上创建
　　2、准备抓包
　　# tcpdump -i eth0 -nn -XX ip dst host 192.168.30.10 and tcp dst port 3306
　　# tcpdump -i eth0 -nn -XX ip dst host 192.168.30.20 and tcp dst port 3306
　　3、连接mysql-proxy，进行读、写等操作
　　mysql -u proxytest -h 192.168.30.14 -P 3306 -p
　　经过测试发现：
　　所有写请求都被分发到主库，所有读请求被分发到从库；
　　从库不在线时，读请求被发往主库