试玩oneproxy,做zabbix的读写分离中间件

线上zabbix的数据库压力太大,老大说做下zabbix的读写分离,写主库、查从库。目前zabbix又没在代码层面实现读写分离,老大就让研究下oneproxy,微博上也经常看到 @平民架构 的推广,就兴致勃勃的下载试用了下。总的体验就是这东西还是挺容易上手的,不会让你找不到头绪,不知所以然。

一:安装oneproxy

登录到测试服务器192.168.153.49上,执行:

wget -S http://www.onexsoft.cn/software/oneproxy-rhel6-linux64-v5.8-ga.tar.gz
tar zxvf oneproxy-rhel6-linux64-v5.8-ga.tar.gz
mv oneproxy /usr/local/
/usr/local/oneproxy/oneproxy --defaults-file=/usr/local/oneproxy/proxy.conf

二:配置测试数据库

主库:192.168.153.49
备库:192.168.42.145

分别在主库和备库上执行进行授权,确保在主库192.168.153.49可以连接主库和备库的zabbix库:
grant all privileges on zabbix.* to 'zabbix'@'192.168.153.49' identified by 'zabbix';

三:配置oneproxy

更改 /usr/local/oneproxy/proxy.conf,如下:

[root@localhost oneproxy]# cat proxy.conf
[oneproxy]
keepalive = 1
log-file  = /data0/log/oneproxy/oneproxy.log
pid-file  = /data0/log/oneproxy/oneproxy.pid
lck-file  = /data0/log/oneproxy/oneproxy.lck

admin-address            = 127.0.0.1:4041
proxy-address            = 127.0.0.1:3307
proxy-master-addresses.1 = 192.168.153.49:3306@server1
proxy-slave-addresses    = 192.168.42.145:3306@server1

proxy-user-list          = zabbix/EA533C0350026E84DC33CF61D1BFE29A1E9F66CD@zabbix

proxy-group-security     = server1:0
proxy-group-policy       = server1:read-slave

注意proxy-user-list项中的密码,是按照如下方法生成的,直接填写数据库密码zabbix是不行的,我在这里折腾了好长时间,切记切记:

/usr/local/oneproxy/oneproxy --defaults-file=/usr/local/oneproxy/proxy.conf

[root@localhost oneproxy]# mysql -uadmin -pOneProxy -h127.0.0.1 --port=4041
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27 OneProxy-Admin-5.8.0 (OneXSoft)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> passwd zabbix;
+--------+------------------------------------------+
| TEXT   | PASSWORD                                 |
+--------+------------------------------------------+
| zabbix | EA533C0350026E84DC33CF61D1BFE29A1E9F66CD |
+--------+------------------------------------------+
1 row in set (0.00 sec)

mysql>

四:启动oneproxy,并测试是否满足需要

1.启动,看日志正常,正常连接zabbix主库、从库

[root@localhost oneproxy]# /usr/local/oneproxy/oneproxy --defaults-file=/usr/local/oneproxy/proxy.conf

2015-12-21 11:18:08: (critical) plugin oneproxy 5.8.0 (Nov 10 2015) started
2015-12-21 11:18:08: (critical) valid config checksum = 964137228
2015-12-21 11:18:09: (critical) Ping backend (zabbix@192.168.153.49:3306) success, mark it up!
2015-12-21 11:18:09: (critical) Ping backend (zabbix@192.168.42.145:3306) success, mark it up!

2.登录oneproxy,执行查询发现走的是从库,执行插入语句走的主库,证明配置正常


参考资料:

oneproxy官网:http://www.onexsoft.com/

one proxy部署安装:http://onexsoft.cn/wp-content/uploads/2015/07/%E5%BF%AB%E9%80%9F%E9%83%A8%E7%BD%B2%E4%B8%8E%E5%AE%89%E8%A3%85.pdf

官方文档:http://www.onexsoft.com/dokuwiki/doku.php?id=oneproxy

How to Scale Out the MySQL database with OneProxy?:http://www.onexsoft.com/en/?p=254