使用ProxySQL+MHA实现mysql读写分离

一、目的

目前很多大型网站为了实现大量的并发访问,除了在网站实现分布式负载均衡,但是远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器扛,如此多的数据库连接操作,数据库可能会崩溃,如果数据丢失的话,后果不堪设想。这时候,我们会考虑如何减少数据库的连接,一方面采用优秀的代码框架,进行代码的优化。另一方面可以考虑数据层优化,利用MySQL主从配置,实现读写分离,减轻数据库压力。

二、环境

系统均为Red Hat Enterprise Linux Server release 6.5 (Santiago) ProxySQL版本:proxysql-1.4.12-1-centos67.x86_64.rpm

Mysql版本:Server version: 5.7.23 MySQL Community Server (GPL)

ProxySQL主机IP:192.0.2.102

Mysql主库IP:192.0.2.101

Mysql从库IP:192.0.2.102

前提条件: Mysql主从已经配置好了同步

主从配置可参看我上篇文章。

三、配置

① 配置后端MySQL

登入ProxySQL,把MySQL主从的信息添加进去。将主库master也就是做写入的节点放到HG 100中,salve节点做读放到HG 1000。

mysql -uadmin -padmin -h 127.0.0.1 -P 6032

admin@127.0.0.1 [mian] 03:43:53>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.0.2.101',3306,1,1000,10,'readproxysql');

Query OK, 1 row affected (0.02 sec)

admin@127.0.0.1 [mian] 03:45:48>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.0.2.102',3306,1,1000,10,'readproxysql');

Query OK, 1 row affected (0.00 sec)

② 在主库master创建 proxysql监控账号和程序账号:

-- 监控

GRANT USAGE ON *.* TO 'proxysql'@'192.0.2.102' IDENTIFIED BY 'proxysql';

-- 程序

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'sbuser'@'192.0.2.102' identified by'sbpasswd';

创建proxysql用户用于访问数据库

default_hostgroup需要和上面的默认值对应hostgroup_id

insertintomysql_users(username,password,active,default_hostgroup,transaction_persistent)values('sbuser','sbpasswd',1,100,1);

③ 配置监测账号

④ 加载配置和变量

admin@127.0.0.1 [main] 03:55:58>load mysql servers to runtime;

Query OK, 0 rows affected (0.07 sec)

admin@127.0.0.1 [main] 03:56:56>load mysql users to runtime;

Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [main] 03:57:02>load mysql variables to runtime;

Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [main] 03:57:10>save mysql servers to disk;

Query OK, 0 rows affected (0.14 sec)

admin@127.0.0.1 [main] 03:57:18>save mysql users to disk;

Query OK, 0 rows affected (0.04 sec)

admin@127.0.0.1 [main] 03:57:24>save mysql variables to disk;

Query OK, 97 rows affected (0.05 sec)

⑤ 连接数据库

mysql -usbuser -p'sbpasswd' -h192.0.2.102 -P6033

写入测试数据:

从查看各类SQL的执行情况,读写还是走的主库,ProxySQL已经正常启动,但是在stats_mysql_query_digest的hostgroup中发现,读和写全部都是走100这个Master的HG,没有用到从库。主要原因就是ProxySQL的mysql_query_rules路由表没有配置。proxysql是通过自定义sql路由规则就可以实现读写分离的。

⑥ 定义路由规则

如:除select * from tb for update的select全部发送到slave,其他的的语句发送到master。

在proxy上执行如下配置规则

测试如下:

从上面的结果可知,路由规则已经生效,select语句均到从库上执行了。到此所有配置都完成。

四、总结

ProxySQL通过MHA可以实现读写分离,但是这种方式真的就没有问题了吗,如果是一些要求实时性非常高的SQL的话,似乎被路由到了从库就会出现BUG。为了解决这个问题我们可以选择在程序端控制这些参数,ProxySQL只作为一个负载均衡来使用,给ProxySQL创建多个账号,一个读写,一个只读。然后程序去实现读写分离。

ProxySQL是分三层来设计运行的,分别为RUNTIME ,MEMORY ,DISK :

RUNTIME代表的是ProxySQL当前生效的配置,包括 global_variables,mysql_servers,mysql_users,mysql_query_rules。无法直接修改这里的配置,必须要从下一层load进来。

就像在配置的时候我每次都会load,MEMORY 是平时在mysql命令行修改的 main 里头配置,可以认为是SQLite数据库在内存的镜像DISK / CONFIG FILE 持久存储的那份配置,一般在$(DATADIR)/proxysql.db,在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到,完了后,如果要修改监听端口,还是需要在管理命令行里修改,再 save 到硬盘。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181024G0SHUU00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券