mysql代理中间件oneproxy系列之oneproxy实现读写分离

mysql读写分离一般有两种方式,一种是通过客户端来决定读连接哪台数据库服务器,写连接哪台数据库服务器,这种方式一种是不能完全避免,另外是不符合软件开发的扩展性要求。第二种,是通过mysql中间件来实现,比如mysql官方的mysql-proxy,还有本文中讲的oneproxy。

一、原理

二、实验准备

1、服务器说明
IP 端口 说明 其他
192.168.0.100 3307 代理服务器 安装oneproxy
192.168.0.101 3306 后端服务器1 安装mysql
192.168.0.102 3306 后端服务器2 安装mysql
2、其他说明
  • 所有的MySQL服务器最好是相同的版本
  • 在代理服务器上已经安装oneproxy(如果没有安装,请参考oneproxy基本操作和配置参数说明这一节)

三、实现过程

1、修改oneproxy的配置

配置主服务器,从服务器地址,以及从服务器是读服务

  1. proxy-master-addresses.1=192.168.0.101:3306@user1
  2. # 指定主服务器的地址,格式为IP:PORT@组名
  3. proxy-slave-addresses.1=192.168.0.102:3306@user1
  4. # 指定从服务器的地址
  5. proxy-user-list=proxyadmin/4841E43E59A4337D61C5D14291D737EAA1A38B4E@user_db
  6. # 指定连接后端数据库的用户名,密码和数据库
  7. proxy-group-policy=user1:read-slave
  8. # 指定从服务器组的策略(这里指定为从服务器是读服务)

注:

  • proxy-auto-readonly=1这一行一定注释掉,不然没法在oneproxy机器上执行任何sql操作
  • 后端服务器都应该有proxyadmin这个用户,并且密码是oneproxy,可以根据自己需要设定,(配置文件中的密码是通过oneproxy管理端的passwd指令生成的),另外必须将user_db这个数据库的权限授予给proxyadmin这个用户
  • 两台后端服务器可以正常提供服务
2、重启oneproxy服务
  1. service oneproxy restart

如果对oneproxy操作不太熟悉的朋友可以参考[mysql代理中间件oneproxy系列之oneproxy基本操作和配置参数说明]这篇文章

3、为了可以更加明显的查看读写分离的效果,我们可以暂时关闭从服务器上的主从同步,而且分别在主、从服务器上插入一条不同的数据
(1)在从服务器(192.168.0.102)上操作:
  1. stop slave;
  2. use user_db;
  3. insert into m_user_info(account,password) values("withec", md5("withec123"));
  4. select * from m_user_info;

(2)在主服务器(192.168.0.101)上操作
  1. use user_db;
  2. insert into m_user_info(account,password) values("shixinke", md5("shixinke123")),("lingdianzhan", md5("lingdianzhan123"));
  3. select * from m_user_info;

4、进入oneproxy客户端,执行插入和查询操作
  1. mysql -uproxyadmin -poneproxy -P3307 --protocol=TCP
  2. insert into m_user_info(account,password) values("oneproxy", md5("oneproxy123"));
  3. select * from m_user_info;

为毛只有一条数据?

因为插入是存入到主库了,查询是查询的从库嘛。不信,请到主库和从库查询一下试试??

5、到主库查询数据

果然,现在有三条数据了

6、到从库查询数据

现在还是只有一条数据

四、各种读写分离的主要策略简介

  • master_only:读写都在主服务器上
  • read_failover:在主服务器正常的情况下,读写都在主库上,如果读失败,才到从库上读
  • read-slave:主库执行写操作,从库执行读操作
  • read_other:主服务器负责写,其他节点负责读
  • read_shard:主服务器负责写,其他节点通过一致性hash到其他节点读
  • read_iphash:主服务器负责写,其他节点通过IP的hash到其他节点读
  • read_balance:主服务器负责写,读在主与从之间轮询
  • write_failover:在一台主服务器写,读在所有主与从服务器上轮询
  • write_banlance:写在所有主服务器上轮询,写在所有主与从服务器上轮询
  • write_shard:读写在主和从服务器上通过一致性hash轮询
  • write_iphash:读写在主和从服务器上通过ip的hash轮询
  • big_slave:写和简单的读在主服务器上,复杂查询在从上
  • big_balance:写和简单的读在主服务器上,复杂查询在主和从上轮询

将proxy-group-policy改成相应的策略即可,完整配置:

  1. [oneproxy]
  2. keepalive = 1
  3. event-threads = 4
  4. log-file = log/oneproxy.log
  5. pid-file = log/oneproxy.pid
  6. lck-file = log/oneproxy.lck
  7. #proxy-auto-readonly = 1
  8. proxy-forward-clientip = 1
  9. proxy-trans-debug = 1
  10. proxy-address = :3307
  11. mysql-version = 5.7.16
  12. proxy-master-addresses.1 = 192.168.0.101:3306@user1
  13. proxy-slave-addresses.1= 192.168.0.102:3306@user1
  14. proxy-user-list = proxyadmin/4841E43E59A4337D61C5D14291D737EAA1A38B4E@user_db
  15. proxy-part-template = conf/template.txt
  16. proxy-part-tables.1 = conf/part.txt
  17. proxy-part-tables.2 = conf/part2.txt
  18. proxy-part-tables.3 = conf/cust1.txt
  19. proxy-charset = utf8_general_ci
  20. proxy-group-policy=user1:read-banlance
  21. #proxy-secure-client = 127.0.0.1
  22. #proxy-license = 32C54560E06EFF3E
  23. #proxy-httpserver = :8080
  24. #proxy-httptitle = OneProxy Monitor