OneProxy6.1.3 读写分离配置

oneproxy基于一主一从的MySQL 读写分离配置步骤

环境准备：
a. 搭建好MySQL 主从复制环境
b. 下载并安装好oneproxy，并能正常启动

oneproxy读写分离配置
1.MySQL Master上建立三个用户
a)metadb用户，创建oneproxy schema的库和表。
create database oneproxy;
GRANT SELECT, INSERT, UPDATE, DELETE,CREATE ON oneproxy.* TO ‘metadb’@’127.0.0.1’ IDENTIFIED BY ‘123456’;

b)repadmin 主从复制监控用户
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘repadmin’@’%’ IDENTIFIED BY ‘123456’;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON test.* TO ‘repadmin’@’%’ ;

c)访问mysql的业务用户
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON test.* TO ‘test’@’%’ IDENTIFIED BY ‘123456’;

2.用oneproxy带的mysqlpwd对密码进行加密
/oneproxy/bin/mysqlpwd 123456
9D7E55EAF8912CCBF32069443FAC452794F8941B

/oneproxy/bin/mysqlpwd 123
956C97523BADD23B6091B09E332A77B5CBDBBEEF

3.在/oneproxy/conf/proxy.conf中添加如下配置
metadb-username = metadb
metadb-password = 9D7E55EAF8912CCBF32069443FAC452794F8941B
metadb-address = 172.25.18.94:3316

proxy-user-list = oneuser/956C97523BADD23B6091B09E332A77B5CBDBBEEF@test
proxy-user-group = server1:test/9D7E55EAF8912CCBF32069443FAC452794F8941B@test

repadmin-username = repadmin
repadmin-password = 9D7E55EAF8912CCBF32069443FAC452794F8941B

proxy-master-addresses = 172.25.18.94:3316@server1
proxy-slave-addresses = 172.25.1814:3316@server1
proxy-secure-client = 172.25.18.94
proxy-group-security = server1:4
proxy-group-policy = server1:read_slave
proxy-group-slavedelay = server1:2
proxy-replication-check = 1
proxy-force-master = t2

4.重启oneproxy

5.验证读写分离
可根据返回的server_id判断请求转发到master还是slave上。
也可以打开general_log ,观察sql执行情况。

正常情况begin;select @@server_id;commit; 转发到mastr上；
select @@server_id ；转发到slave上；

mysql -utest -p123 -h172.25.18 -P3307 -e “begin;select @@server_id;commit;”

mysql -utest -p123 -h172.25.18 -P3307 -e “select @@server_id;”