MySQL ProxySQL详解

0    64    1

👉 本文共约6097个字,系统预计阅读时间或需23分钟。

简介

image.png
ProxySQL是用C++语言开发的,一个轻量级开源软件,性能和功能满足读写中间件所需的绝大多数功能,其配置数据基于SQLite存储,目前已到v2.0.13版本。

功能方面如下:

  • 最基本的读/写分离,且方式有多种。
  • 可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说,规则很灵活。基于schema和与语句级的规则,可以实现简单的sharding。
    不支持分表,可以分库,但利用规则配置实现分表。
  • 可缓存查询结果。基本ProxySQL的缓存策略实现了基本的缓存功能,绝大多数时候够用。
    1.支持动态加载配置,即一般可以在线修改配置,但有少部分参数还是需要重启来生效。
    2.支持query cache。
    3.支持对query的路由,可以针对某个语句进行分配执行实例。
  • 监控后端节点。ProxySQL可以监控后端节点的多个指标,包括:ProxySQL和后端的心跳信息,后端节点的read-only/read-write,slave和master的数据同步延迟性(replication lag)。

特性方面:

  • 连接池,而且是 multiplexing;
  • 主机和用户的最大连接数限制;
  • 自动下线后端DB;
    - 延迟超过阀值
    - ping 延迟超过阀值
    - 网络不通或宕机
  • 强大的规则路由引擎;
    - 实现读写分离
    - 查询重写
    - sql流量镜像
  • 支持prepared statement;
  • 支持Query Cache;
  • 支持负载均衡,与gelera结合自动failover;
  • 将所有配置保存写入到SQLit表中。
  • 支持动态加载配置,即一般可以在线修改配置,但有少部分参数还是需要重启来生效。
  • 支持query cache。
  • 支持对query的路由。
  • 不支持分表,可以分库,但是利用规则配置实现分表。

ProxySQL地址

官方站点: https://www.proxysql.com/
官方github: https://github.com/sysown/proxysql/wiki
percona ProxySQL手册:https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html

下面基于2.0.10版本所有操作。

安装部署

安装地址:https://github.com/sysown/proxysql/releases
官方说明:https://github.com/sysown/proxysql/wiki
proxysql服务器上安装mysql客户端,用于在本机连接到ProxySQL的管理接口

1.tar.gz安装

卸载:make uninstall

2.rpm包安装

rpm解压即可,会在/usr/local/proxysql 生成文件

3.启动服务

服务命令:systemctl start | stop | restart | statu proxysql

4.配置信息

ProxySQL很少停止或重启,因为绝大多数配置都可以在线修改。
服务启动配置文件

5.参数配置文件

ProxySQL结构

image.png

  • Qurey Processor 用于匹配查询规则并根据规则决定是否缓存查询或者将查询加入黑名单或者重新路由、重写查询或者镜像查询到其他hostgroup。
  • User Auth 为底层后端数据库认证提供了用户凭证。
  • Hostgroup manager – 负责管理发送SQL请求都后端数据库并跟踪SQL请求状态。
  • Connection pool – 负责管理后端数据库连接,连接池中建立的连接被所有的前端应用程序共享。
  • Monitoring – 负责监控后端数据库健康状态主从复制延时并临时下线不正常的数据库实例。

1.启动过程

image.png

  • RUNTIME层
    代表的是ProxySQL当前生效的配置,包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置,必须要从下一层load进来
  • MEMORY层
    是平时在mysql命令行修改的 main 里头配置,可以认为是SQLite数据库在内存的镜像。该层级的配置在main库中以mysql_开头的表以及global_variables表,这些表的数据可以直接修改;
  • DISK|CONFIG FILR层
    持久存储的那份配置,一般在$(DATADIR)/proxysql.db,在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到,完了后,如果要修改监听端口,还是需要在管理命令行里修改,再 save 到硬盘。

注意:
如果找到数据库文件(proxysql.db),ProxySQL 将从 proxysql.db 初始化其内存中配置。因此,磁盘被加载到 MEMORY 中,然后加载到 RUNTIME 中。
如果找不到数据库文件(proxysql.db)且存在配置文件(proxysql.cfg),则解析配置文件并将其内容加载到内存数据库中,然后将其保存在 proxysql.db 中并在加载到 RUNTIME。
请务必注意,如果找到 proxysql.db,则不会解析配置文件。也就是说,在正常启动期间,ProxySQL 仅从持久存储的磁盘数据库初始化其内存配置。

2.数据库结构

ProxySQL自身共有5个 库,分别为3个保存在内存中的库,和三个保存在磁盘的SQLite库。
通过6032管理端口登入后,默认就是main库,所有的配置更改都必须在这个库中进行,disk存档库不会直接受到影响。接下来看下

  • main:内存配置数据库,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容,不能通过dml语句修改,只能修改对应的不以 runtime 开头的(在内存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盘以供下次重启加载。
  • disk:是持久化到硬盘的配置,sqlite数据文件。SQLite3 数据库,默认位置为 $(DATADIR)/proxysql.db,在重新启动时,未保留的内存中配置将丢失。因此,将配置保留在 DISK 中非常重要。(SQLite是一个进程内的库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎)
  • stats:proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
  • monitor:库存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查。
  • stats_history:统计信息历史库

3.核心配置表

image.png
image.png
image.png
image.png
image.png
image.png

4.命令

image.png

5.小结

这些数据库的功能实现了实用化内容:

  • 允许轻松动态更新配置,便于运维管理,与MySQL兼容的管理界面可用于此目的。
  • 允许尽可能多的配置项目动态修改,而不需要重新启动ProxySQL进程
  • 可以毫不费力地回滚无效配置
  • 通过多级配置系统实现的,其中设置从运行时移到内存,并根据需要持久保存到磁盘

ProxySQL读写分离配置

image.png

1.MySQL里创建账号

数据库段创建访问用户,监控用户

2.添加MySQL节点

hostgroup_id, hostname, port 组成一个主键

3.用户配置

配置mysql_users表,将用户添加到该表中。

注意两个字段:
只有active=1的用户才是有效的用户。
transaction_persistent字段,当它的值为1时,表示事务持久化:当某连接使用该用户开启了一个事务后,那么在事务提交/回滚之前,所有的语句都路由到同一个组中,避免语句分散到不同组。建议在创建完用户之后设置为1,避免发生脏读、幻读等现象.

mysql_users表有不少字段,最主要的三个字段为username、password和default_hostgroup:

  • username:前端连接ProxySQL,以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
  • password:用户名对应的密码。可以是明文密码,也可以是hash密码。如果想使用hash密码,可以先在某个MySQL节点上执行
    select password(PASSWORD),然后将加密结果复制到该字段。
  • default_hostgroup:该用户名默认的路由目标。例如,指定root用户的该字段值为10时,则使用root用户发送的SQL语句默认
    情况下将路由到hostgroup_id=10组中的某个节点。

4.监控后端MySQL节点

添加Mysql节点之后,还需要监控这些后端节点。对于后端是主从复制的环境来说,这是必须的,因为ProxySQL需要通过每个节点的read_only值来自动调整,它们是属于读组还是写组。

加载,Monitor模块就会开始监控后端的read_only值,当监控到read_only值后,就会按照read_only的值将某些节点自动移动到读/写组。
例如,此处所有节点都在id=10的写组,slave1和slave2都是slave,它们的read_only=1,这两个节点将会移动到id=20的组。如果一开始这3节点都在id=20的读组,那么移动的将是Master节点,会移动到id=10的写组。

5.配置路由规则

ProxySQL的路由规则非常灵活,可以基于用户、基于schema以及基于每个语句实现路由规则的定制。

验证

6.信息查询

#read_only日志此时也为空(正常来说,新环境配置时,这个只读日志是为空的)

#replication_lag的监控日志为空

#注意:可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常。

#以下是对心跳信息的监控(对ping指标的监控)

查看路由的信息,可查询stats库中的stats_mysql_query_digest表。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

开启ProxySQL的Web统计功能

查看路由的信息,可查询stats库中的stats_mysql_query_digest表。

查看端口和登录web界面的用户名和密码,用户名和密码与stat账户一致:

查看web端口是否正常打开

访问http://192.168.244.130:6080并使用stats:stats登录即可查看一些统计信息。
image.png

scheduler打印状态到日志

编辑脚本和目录

输入scheduler信息

查看日志:

其他

自动回避复制延迟较大的节点
如果服务器将 max_replication_lag 设置为非零值,则 Monitor 模块会定期检查复制延迟。
下图中,当172.16.0.3的复制延迟超过了30秒会自动回避,设置max_replication_lag = 0,代表不检查复制延迟 。

注意:
1.max_replication_lag主要来源Seconds_Behind_Master,该参数判断延迟准确性不高,顾个人建议为参考功能。
2.max_replication_lag 仅适用于从节点。如果服务器未启用复制,则 Monitor 不会执行任何操作。

#强制关闭与假死主库的连接,避免数据被写入的假死的老主库。Admin> update runtime_mysql_servers set status=“HARD_OFFLINE” where hostname=‘192.168.20.31’ and port=‘3306’

#将假死的旧主库提出集群,避免后续数据被写入到老主库Admin> delete from mysql_servers where hostname=‘192.168.20.31’ and port='3306’Admin> load mysql servers to runtimeAdmin> save mysql serbers to disk

MGR配置

image.png

1.MySQL 配置

创建proxysql 监控mysql用户monitor 并赋予权限
sys库的读权限赋给ProxySQL配置的监控MySQL的账户

官方提供监控视图版本是5.7的 ,8.0的如下:
5.7版本参考:https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_group_replication_hostgroups

确认信息如下:

2.ProxySQL配置

添加服务器信息和 用户信息
主负责写、从负责读,当MGR主库切换后,代理自动识别主从。
ProxySQL代理每一个后端MGR集群时,都必须为这个MGR定义写组50、备写组60、读组70、离线组80

查看服务器信息

验证是否正常通信

配置读写分离规则:

验证:

查看路由规则:

总结

1.Proxysql整个使用上面,功能上面基本都能满足需求。
2.还有配置复杂度还是比较低的。
3.资源方面因为自己维护一套连接池,还是流量转发,配置不能太低。
4.性能损耗估计5%~18%以内。
5.autocommit=0相关bug,这样使用的场景 还是比较不多。对于这种问题,等官方修复,或可以先临时插入一张表,作为事务开始的之后前奏 。
image.png
https://github.com/sysown/proxysql/issues/2997

参考

https://www.modb.pro/db/28841

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

3 × 2 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部