oneproxy简单读写分离

1、在主从复制的基础上大家拿oneproxy实现读写分离

2、注意关闭三端防火墙，也可以开放端口，数据库端口3306，oneproxy连接端口3307，oneproxy管理端口4041

3、当配置同步之后需要在master主机创建test库并添加oneproxy的连接用户
mysql>create database test;
mysql>grant select,delete,insert,update,create on test.* to oneproxy@'%' identified by '123.com';
4、在slave数据库中设置read_only项，read_only=1只读模式
mysql> set global read_only = 1;
5、配置OneProxy中间件
安装oneproxy
wget http://www.onexsoft.com/software/oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz
tar zxf oneproxy-rhel6-linux64-v6.2.0-ga.tar.gz -C /usr/local/
6、在预启动脚本中修改oneproxy目录
 cat demo.sh （红色标记）
#/bin/bash
#
export ONEPROXY_HOME=/usr/local/oneproxy
ulimit -c unlimited

#valgrind --leak-check=full \
  ${ONEPROXY_HOME}/bin/oneproxy --defaults-file=${ONEPROXY_HOME}/conf/proxy.conf
cat oneproxy.service （红色标记）
#!/bin/bash
# chkconfig: - 30 21
# description: OneProxy service.
# Source Function Library
. /etc/init.d/functions

# OneProxy Settings
ONEPROXY_HOME=/usr/local/oneproxy
ONEPROXY_SBIN="${ONEPROXY_HOME}/bin/oneproxy"
ONEPROXY_CONF="${ONEPROXY_HOME}/conf/proxy.conf"
ONEPROXY_PID="${ONEPROXY_HOME}/log/oneproxy.pid"
RETVAL=0
prog="OneProxy"

start() {
        echo -n $"Starting $prog ... "
...........................

获取oneproxy加密后的密文密码
./mysqlpwd 123.com
修改oneproxy主配文件
vim /usr/local/oneproxy/conf/proxy.conf
[oneproxy]
#proxy-license              = A2FF461456A67F28,D2F6A5AD70C9042D
keepalive = 1
event-threads = 4
proxy-group-policy = test:read-slave
log-file = log/oneproxy.log
pid-file = log/oneproxy.pid
lck-file = log/oneproxy.lck
proxy-auto-readonly = 1
proxy-forward-clientip = 1
proxy-trans-debug = 1
#mysql-version = 5.7.18
proxy-master-addresses.1 = 192.168.182.146:3306@test
proxy-slave-addresses.2 = 192.168.182.147:3306@test
proxy-user-list = oneproxy/7FB703DA3682A0CCC20168D44E8A7E92FE676A51@test
proxy-part-template = conf/template.txt
proxy-part-tables.1      = conf/part.txt
proxy-part-tables.2      = conf/part2.txt
proxy-part-tables.3      = conf/cust1.txt
proxy-charset            = utf8_bin
proxy-secure-client = 127.0.0.1
proxy-httpserver = :8080
proxy-httptitle = OneProxy Monitor
启动oneproxy 
 . demo.sh 
 . oneproxy.service start
Starting OneProxy ...                                      [  OK  ]
ss -anpt |grep 3307
LISTEN     0      128          *:3307                     *:*                   users:(("oneproxy",pid=3943,fd=11))
[root@proxy oneproxy]# ss -anpt |grep 4041
LISTEN     0      128          *:4041                     *:*                   users:(("oneproxy",pid=3943,fd=13))
访问oneproxy管理网页

输入http://oneproxy的ip:8080

默认用户名admin，密码OneProxy

标题

验证

使用客户端登录oneproxy连接端口3307
注：先需要去到4041管理端口打开底层数据库权限
mysql -uadmin -pOneProxy -P4041 -h 192.168.182.150
set gaccess test 0 ;
mysql -uoneproxy -p123.com -h 192.168.182.150 -P3307
mysql> create table test.tb (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test.tb values(1);
Query OK, 1 row affected (0.00 sec)
多创建几个
mysql> insert into test.tb values(1) select * from test.tb;
观察管理网页master和slave中query的变化,读负载的增加与写负载的增加，验证成功