本文共 2777 字,大约阅读时间需要 9 分钟。
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的变化,读负载的增加与写负载的增加,验证成功转载地址:http://azxvb.baihongyu.com/