Part1:写在最前
OneProxy平民软件完全自主开发的分布式数据访问层,帮助用户在MySQL/PostgreSQL集群上快速搭建支持分库分表的分布式数据库中间件,也是一款具有SQL白名单(防SQL注入)及IP白名单功能的SQL防火墙软件。采用与MySQL Proxy一致的反向协议输出模式,对应用非常简单和透明易用,让用户畏惧的数据库故障切换(Failover)、读写分离(Read/Write Split)、分库分表(Horizontal Partitioning)等复杂方案变得极其简单可控!基于Libevent机制实现,单个实例可以实现25万的SQL转发能力,用一个OneProxy节点可以带动整个MySQL集群,为业务发展贡献一份力量。
Part2:环境简介
HE1:192.168.1.248 slave1
HE2:192.168.1.249 slave2
HE3:192.168.1.250 Master
HE4:192.168.1.251 Oneproxy
Part1:安装Oneproxy
- [root@HE4 ~]
- # tar xvf oneproxy-rhel5-linux64-v5.8.5-ga.tar.gz
- [root@HE4 oneproxy]
- # ls
- bin conf demo.sh log oneproxy.service README testadmin.sql testautocommit.sql testproxy.sql trantest.sql
Part2:构建主从环境
本文的架构是一主两从,HE3作为Master,HE1,HE2作为Slave。主从的构建不是本文的重点,需要的可移步:
1分钟利用mysqlreplicate快速搭建MySQL主从 http://www.linuxidc.com/Linux/2017-11/148306.htm
Part3:配置Oneproxy
目录中的demo是初次启动脚本,oneproxy.service是启停脚本,在新版的oneproxy中,conf文件夹的proxy.cnf为配置文件
- [root@HE4 oneproxy]
- # cat demo.sh
- #/bin/bash
- #
- export
- ONEPROXY_HOME=
- /root/oneproxy
- # valgrind --leak-check=full \
- ${ONEPROXY_HOME}
- /bin/oneproxy
- --defaults-
- file
- =${ONEPROXY_HOME}
- /conf/proxy
- .conf
我们将demo.sh中的ONEPROXY_HOME变更为解压oneproxy时所在的目录
- [root@HE4 oneproxy]
- # cat oneproxy.service
- #!/bin/bash
- # chkconfig: - 30 21
- # description: OneProxy service.
- # Source Function Library
- .
- /etc/init
- .d
- /functions
- # OneProxy Settings
- ONEPROXY_HOME=
- /root/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 ... "
- daemon $ONEPROXY_SBIN --defaults-
- file
- =$ONEPROXY_CONF
- RETVAL=$?
- echo
- }
- stop() {
- echo
- -n $
- "Stopping $prog ... "
- if
- [ -e ${ONEPROXY_PID} ];
- then
- daemon
- kill
- -INT $(
- cat
- ${ONEPROXY_PID})
- RETVAL=$?
- fi
- echo
- }
- restart(){
- stop
- sleep
- 1
- start
- }
- case
- "$1"
- in
- start)
- start
- ;;
- stop)
- stop
- ;;
- restart)
- restart
- ;;
- *)
- echo
- $
- "Usage: $0 {start|stop|restart}"
- RETVAL=1
- esac
- exit
- $RETVAL
同时,将oneproxy.service中的ONEPROXY_HOME也改为解压时的目录
进入oneproxy中的bin目录,使用mysqlpwd对密码进行加密
[root@HE4 oneproxy]# cd bin/
[root@HE4 bin]# ls
mysqlpwd oneproxy
[root@HE4 bin]# ./mysqlpwd MANAGER
1C6D087BA5D2607A27DECB2F2AFE247E911E877A
编辑proxy.cnf中的内容
- [root@HE4 conf]
- # cat proxy.conf
- [oneproxy]
- keepalive = 1
- event-threads = 4
- proxy-group-policy.2 =
- 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
- proxy-address = :3307
- mysql-version = 5.7.16
- proxy-master-addresses.1 = 192.168.1.250:3306@
- test
- proxy-slave-addresses.2 = 192.168.1.248:3306@
- test
- proxy-slave-addresses.3 = 192.168.1.249:3306@
- test
- proxy-user-list = sys_admin
- /1C6D087BA5D2607A27DECB2F2AFE247E911E877A
- @
- test
- proxy-user-group =
- test
- :sys_admin
- /1C6D087BA5D2607A27DECB2F2AFE247E911E877A
- @
- 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-license = 32C54560E06EFF3E
- proxy-httpserver = :8080
- proxy-httptitle = OneProxy Monitor
Part4:启动Oneproxy
[root@HE4 oneproxy]# ./demo.sh
[root@HE4 oneproxy]# ./oneproxy.service restart
Stopping OneProxy ... [ OK ]
Starting OneProxy ... [ OK ]
Part1:Oneproxy状态校验
浏览器打开192.168.1.251:8080端口能够看到oneproxy的管理页面
这里可以看到主从的各种状态信息。
Part2:负载均衡与读写分离校验
- [root@HE1 ~]
- # mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 -e"select @@hostname;"
- mysql: [Warning] Using a password on the
- command
- line interface can be insecure.
- +------------+
- | @@
- hostname
- |
- +------------+
- | HE1 |
- +------------+
- [root@HE1 ~]
- # mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 -e"select @@hostname;"
- mysql: [Warning] Using a password on the
- command
- line interface can be insecure.
- +------------+
- | @@
- hostname
- |
- +------------+
- | HE2 |
- +------------+
- [root@HE1 ~]
- # mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 -e"select @@hostname;"
- mysql: [Warning] Using a password on the
- command
- line interface can be insecure.
- +------------+
- | @@
- hostname
- |
- +------------+
- | HE1 |
- +------------+
- [root@HE1 ~]
- # mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 -e"select @@hostname;"
- mysql: [Warning] Using a password on the
- command
- line interface can be insecure.
- +------------+
- | @@
- hostname
- |
- +------------+
- | HE2 |
- +------------+
- [root@HE1 ~]
- # mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 -e"select @@hostname;"
- mysql: [Warning] Using a password on the
- command
- line interface can be insecure.
- +------------+
- | @@
- hostname
- |
- +------------+
- | HE1 |
- +------------+
- [root@HE1 ~]
- # mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 -e"begin;select @@hostname;commit;"
- mysql: [Warning] Using a password on the
- command
- line interface can be insecure.
- +------------+
- | @@
- hostname
- |
- +------------+
- | HE3 |
- +------------+
- [root@HE1 ~]
- # mysql -usys_admin -pMANAGER -h192.168.1.251 -P3307 -e"begin;select @@hostname;commit;"
- mysql: [Warning] Using a password on the
- command
- line interface can be insecure.
- +------------+
- | @@
- hostname
- |
- +------------+
- | HE3 |
- +------------+
可以看到HE1,HE2两个slave作为负载均衡没有问题,HE3作为写服务器也没有问题。
——总结——
OneProxy配合MySQL实现读写分离与负载均衡实验构建成功,Oneproxy还具有分库分表功能,今后会进一步研究。
来源: http://www.linuxidc.com/Linux/2017-11/148305.htm