mysql-proxy本身并不能进行读写分离，要实现读写分离要依赖于lua，所以要先查看是否安装了lua，如果没有安装使用yum install安装上即可

~]# rpm -q lualua-5.1.4-4.1.el6.x86_64

创建系统用户

useradd -r mysql-proxy

解压包创建链接文件

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

导出二进制程序

~]# vim /etc/profile.d/mysql-proxy.sh内容为：export PATH=/usr/loca/mysql-proxy/bin/:$PATH~]# source/etc/profile.d/mysql-proxy.sh

查看mysql-proxy用法

~]# mysql-proxy --help-all#比较重要的选项有以下几个--daemon #让mysql-proxy在后台工作--user=<user>#指定运行mysql-proxy进程的属主--proxy-backend-addresses=<host:port>#后端服务器--proxy-read-only-backend-addresses=<host:port>#只允许读的后端服务器--log-level=(error|warning|info|message|debug) #指定日志级别--log-file=<file> #指定日志文件路径--plugins=<name>#要加载的插件名，插件路径在/usr/local/mysql-proxy/lib/mysql-proxy/plugins，该路径下有一个libadmin.so的插件，可以用来管理后端服务器，还有libproxy.so的插件，启用proxy的功能--keepalive#如果proxy崩溃了，就会尝试去重启proxy--proxy-lua-script=<file> #指定要使用的lua脚本文件，lua脚本路径在/usr/local/mysql-proxy/share/doc/mysql-proxy，该路径下有一个实现读写分离的lua脚本文件rw-splitting.lua--defaults-file #指定配置文件路径

启用mysql-proxy

~]# mysql-proxy --daemon \> --user=mysql-proxy \> --log-level=debug \> --log-file=/var/log/mysql-proxy.log \> --plugins=proxy \> --plugins=admin \> --proxy-backend-addresses=172.25.78.2:3306 \> --proxy-read-only-backend-addresses=172.25.78.3:3306 \> --keepalive=true \> --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua \> --admin-username=admin \> --admin-password=adminpass \> --admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua

]# netstat -tan | grep :40tcp00 0.0.0.0:40400.0.0.0:* LISTENtcp00 0.0.0.0:4041 0.0.0.0:* LISTEN#4041是admin管理接口的端口，4040是mysql-proxy的端口

在代理服务器上（172.25.78.4）连接到管理接口查看有哪些后端服务器

mysql -uadmin -padminpass -h272.25.78.4 --port=4041MySQL [(none)]> SELECT * FROM backends;+-------------+------------------+---------+------+------+-------------------+| backend_ndx | address| state | type | uuid | connected_clients |+-------------+------------------+---------+------+------+-------------------+| 1 | 172.25.78.2:3306 | unknown | rw | NULL | 0 || 2 | 172.25.78.3:3306 | unknown | ro | NULL | 0 |+-------------+------------------+---------+------+------+-------------------+

在master节点和slave节点上添加测试用户

MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE ON *.* TO 'test'@'172.25.78.%' IDENTIFIED BY 'testpass';

在客户端进行测试

~]# mysql -utest -ptestpass -h272.25.78.4 -P4040MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || MYDB || S_SC_C || db_user|| discuz || dvwa || hello|| mysql|| performance_schema || test || testdb |+--------------------+MariaDB [(none)]> USE S_SC_C;MariaDB [S_SC_C]> INSERT INTO S_1(sname,sdept) VALUES ('H','AA');MariaDB [S_SC_C]> SELECT * FROM S_1;+-----+-------+-------+| sid | sname | sdept |+-----+-------+-------+| 1 | HELLO | A || 2 | HE| A || 3 | H | AA|+-----+-------+-------+

在master上查看

MariaDB [S_SC_C]> SELECT * FROM S_1;+-----+-------+-------+| sid | sname | sdept |+-----+-------+-------+| 1 | HELLO | A || 2 | HE| A || 3 | H | AA|+-----+-------+-------+

MariaDB [S_SC_C]> SELECT * FROM S_1;+-----+-------+-------+| sid | sname | sdept |+-----+-------+-------+| 1 | HELLO | A || 2 | HE| A || 3 | H | AA|+-----+-------+-------+