前言
ProxySQL
ProxySQL 是基于 MySQL 的一款开源的中间件的产品,是一个灵活的 MySQL 代理层,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行缓存,支持动态加载(无需重启 ProxySQL 服务),故障切换和一些 SQL 的过滤功能。
GreatSQL MGR
GreatSQL
是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。
GreatSQL
在高可靠方面的主要提升是针对MGR做了大量的改进和提升工作,进一步提升MGR的高可靠等级。包括但不限于以下提升:
-
地理标签
,提升多机房架构数据可靠性。 -
读写节点动态VIP
,高可用切换更便捷。 -
仲裁节点
,用更低的服务器成本实现更高可用。 -
快速单主模式
,在单主模式下更快,性能更高。 -
智能选主
,高可用切换选主机制更合理。 -
全新流控算法
,使得事务更平稳,避免剧烈抖动。 - 优化了节点加入、退出时可能导致性能剧烈抖动的问题。
- 解决磁盘空间爆满时导致MGR集群阻塞的问题。
- 解决了长事务造成无法选主的问题。
- 优化事务认证队列清理算法,规避每60s抖动问题。
- 修复了recover过程中长时间等待的问题。
了解更多详细信息可以前往➥https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/5-enhance/5-2-ha.md
部署环境介绍
部署架构图
GreatSQL MGR集群实现数据库复制功能及高可用。Proxysql对应用程序提供访问,对MGR集群进行读写分离,集群状态检测,实现故障切换。
部署环境配置
部署软件详情
软件名 | 版本号 |
---|---|
GreatSQL | 8.0.32-24 |
ProxySQL | 2.5.4-58 |
部署环境准备
本次采用的是单机多实例的部署方式,如何部署单机多实例可以前往➥https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/6-oper-guide/6-6-multi-instances.md
IP | 端口 | 角色 |
---|---|---|
172.17.139.77 | 3306 | MGR01 |
172.17.139.77 | 3307 | MGR02 |
172.17.139.77 | 6032、6033 | ProxySQL |
$ cat /etc/system-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
$ uname -a
Linux gip 3.10.0-1160.el7.x86_64 #1 SMP Tue Aug 18 14:50:17 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux
GreatSQL配置
#mgr settings
loose-plugin_load_add = 'mysql_clone.so'
loose-plugin_load_add = 'group_replication.so'
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
loose-group_replication_group_seeds = '172.17.139.77:33061,172.17.139.77:33071'
loose-group_replication_start_on_boot = ON
loose-group_replication_bootstrap_group = OFF
loose-group_replication_exit_state_action = READ_ONLY
loose-group_replication_flow_control_mode = "DISABLED"
loose-group_replication_single_primary_mode = ON
loose-group_replication_communication_max_message_size = 10M
loose-group_replication_transaction_size_limit = 3G
loose-group_replication_arbitrator = 0
loose-group_replication_single_primary_fast_mode = 0
loose-group_replication_request_time_threshold = 20000
report_host = "172.17.139.77"
MGR01节点配置如下
[mysqld@mgr01]
datadir=/data/GreatSQL/mgr01
socket=/data/GreatSQL/mgr01/mysql.sock
port=3306
server_id=103306
log-error=/data/GreatSQL/mgr01/error.log
loose-group_replication_local_address= "172.17.139.77:33061"
MGR02节点配置如下
[mysqld@mgr02]
datadir=/data/GreatSQL/mgr02
socket=/data/GreatSQL/mgr02/mysql.sock
port=3307
server_id=103317
log-error=/data/GreatSQL/mgr02/error.log
loose-group_replication_local_address= "172.17.139.77:33071"
搭建MGR集群及ProxySQL
搭建GreatSQL MGR 集群
MGR01实例操作
greatsql> set session sql_log_bin=0;
greatsql> create user 'repl'@'%' identified with mysql_native_password by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
接下来即可启动MGR集群
greatsql> set global group_replication_bootstrap_group=ON;
greatsql> start group_replication;
greatsql> select * from performance_schema.replication_group_membersG
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 2920447e-35bf-11ee-89a5-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
MGR02实例操作
greatsql> set session sql_log_bin=0;
greatsql> create user 'repl'@'%' identified with mysql_native_password by 'GreatSQL@666';
greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
greatsql> set session sql_log_bin=1;
greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';
greatsql> start group_replication;
Query OK, 0 rows affected (5.39 sec)
MGR集群搭建成功
greatsql> select * from performance_schema.replication_group_membersG
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 8f20696c-3b46-11ee-b59e-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 8ff26a55-3b46-11ee-bd70-00163e566da1
MEMBER_HOST: 172.17.139.77
MEMBER_PORT: 3307
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.32
MEMBER_COMMUNICATION_STACK: XCom
在MGR集群上创建ProxySQL所需的账号
# proxysql的监控账户
greatsql> create user 'monitor'@'%' identified with mysql_native_password by 'Monitor@666';
greatsql> grant all privileges on *.* to 'monitor'@'%' with grant option;
# proxysql的对外访问账户
greatsql> create user 'proxysql'@'%' identified with mysql_native_password by 'Proxysql@666';
greatsql> grant all privileges on *.* to 'proxysql'@'%' with grant option;
用户认证的方式需要修改为
mysql_native_password
看看有没有创建成功
greatsql> select User,Host from mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| monitor | % |
| proxysql | % |
| repl | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
安装ProxySQL
ProxySQL文档中有详细的安装教程可以浏览➥https://github.com/sysown/proxysql
RPM方式和yum方式都可以安装的,本文采用RPM方式安装,如果要用yum安装需要更换yum源
$ cat > /etc/yum.repos.d/proxysql.repo
Red Hat 系统要把
$releasever
改为7
接着直接安装即可
$ yum install proxysql
这里要注意一下,如果GreatSQL使用RPM方式安装的,会和ProxySQL需要的依赖冲突!
RPM包下载地址:https://github.com/sysown/proxysql/releases/tag/v2.5.4
所以采用RPM的
--nodeps
选项强制安装rpm -ivh proxysql-2.5.4-1-centos7.x86_64.rpm --nodeps
但是启动
systemctl start proxysql.service
的时候会报错,需要libgnutls.so.28
这时候再安装
yum install -y gnutls
,再次systemctl start proxysql.service
即可启动
启动ProxySQL
$ systemctl start proxysql
# 检查是否启动成功
$ systemctl status proxysql
查看下端口是否开放
$ netstat -anlp | grep proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1009915/proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1009915/proxysql
- 6032 是ProxySQL的管理端口号
- 6033 是对外服务的端口号
ProxySQL的用户名和密码都是默认的admin
配置ProxySQL
管理员登录
$ mysql -uadmin -padmin -h 127.0.0.1 -P 6032
可以看到有一些数据库可用, ProxySQL将SHOW DATABASE
命令转换为SQLite3的等效命令。
greatsql> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
这些数据库作用如下:
-
main
:内存配置数据库 使用此数据库,可以轻松地以自动方式查询和更新Pro服务器托管网xySQL的配置。使用LOAD MYSQL USERS FROM MEMORY和类似命令,存储在此处的配置可以在运行时传播到ProxySQL使用的内存数据结构。 -
disk
:基于磁盘的”main”镜像。 在重新启动时,”main”不会持久存在,并且可以从“磁盘”数据库或配置文件中加载,具体取决于启动标志和磁盘数据库的存在。 -
stats
:包含从代理的内部功能收集的运行时指标。 示例度量标准包括每个查询规则匹配的次数,当前运行的查询等。 -
monitor
:包含与ProxySQL连接的后端服务器相关的监控指标。 示例度量标准包括连接到后端服务器或对其进行ping操作的最短和最长时间。 -
myhgm
:仅在调试版本中启用
此外,使用这两种类型的用户使用这些默认凭据访问管理数据库:
-
user:admin / password:admin – 具有对所有表的读写访问权限
-
user:stats / password:stats – 具有对统计表的只读访问权限。 这用于从ProxySQL中提取指标,而不会暴露太多的数据库
上述的访问凭据,可通过变量admin-admin_credentials
和admin-stats_credentials
进行配置。
更多详细的介绍可以前往”老叶茶馆MySQL-ProxySQL中间件(一)【微信添加】“”老叶茶馆MySQL-ProxySQL中间件(二)【微信添加】“
为配置监控账号
greatsql> set mysql-monitor_username='monitor';
greatsql> set mysql-monitor_password='Monitor@666';
上面这两句是修改变量的方式还可以在main库下面用sql语句方式修改
greatsql>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
greatsql>UPDATE global_variables SET variable_value='Monitor@666' WHERE variable_name='mysql-monitor_password';
配置默认组信息
greatsql> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,writer_is_also_reader)values(10,20,30,40,1,1);
这段SQL语句是用来配置MGR集群的主备和读写分离的,向mysql_group_replication_hostgroups
表插入配置
-
writer_hostgroup
:写入主节点的主机组(必须大于0),这里设置为10 -
backup_writer_hostgroup
:备份写入主节点的主机组,这里是20。 -
reader_hostgroup
:只读节点的主机组,这里是30。 -
offline_hostgroup
:离线节点的主机组,这里是40。 -
active
:是否激活该配置,1表示激活。 -
writer_is_also_reader
:写入主节点是否也可以作为读节点,1表示可以。
配置对外访问用户到写组10内
greatsql> insert into mysql_users(username,password,default_hostgroup)values('proxysql','Proxysql@666',10);
这个 SQL 代码的作用是将一个 MySQL 服务器节点添加到 ProxySQL 的管理中,以便 ProxySQL 可以根据定义的规则和策略来分发连接请求,从而实现负载均衡和高可用性。
配置主节点定义为写组10,从节点定义为只读组30
greatsql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (10,'172.17.139.77',3306,1,3000,10,'mgr01');
greatsql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,'172.17.139.77',3306,2,3000,10,'mgr01');
greatsql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,'172.17.139.77',3307,2,3000,10,'mgr02');
-
hostgroup_id
:指定所属的主机组(Hostgroup),这是 ProxySQL 中用于分组管理的一个概念。在这里,它被设置为 10。 -
hostname
:指定 MySQL 服务器的主机名或 IP 地址,这里是 ‘172.17.139.77’。 -
port
:指定 MySQL 服务器的端口号,这里是 3306。 -
weight
:指定该节点在负载均衡中的权重。权重越高,代表更多的请求会被分配到这个节点。这里设置为 1。 -
max_connections
:指定该节点允许的最大连接数。 -
max_replication_lag
:指定最大的复制延迟(以秒为单位),这是一个连接到主从复制的节点时的配置。 -
comment
:一个可选的注释或描述信息,这里设置为 ‘mgr01’。
这个 SQL 代码的作用是将一个 MySQL 用户添加到 ProxySQL 的管理中,以便 ProxySQL 可以根据定义的用户访问规则和策略来控制用户对数据库的访问,包括路由、负载均衡和故障转移等。
配置读写分离参数,与之相关的有两个表mysql_query_rules
和mysql_query_rules_fast_routing
这里大家可以自行配置
其中表mysql_query_rules_fast_routing
是mysql_query_rules
的扩展,并在以后评估快速路由策略和属性(仅在ProxySQL 1.4.7+中可用)。
greatsql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,^SELECT.*FORUPDATE$,10,1);
greatsql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',30,1);
-
active
:是否启用这个规则,1表示启用,0表示禁用 -
match_pattern
字段就是代表设置规则 -
destination_hostgroup
字段代表默认指定的分组, -
apply
代表真正执行应用规则
在 ProxySQL 中,
rule_id
的排序作用是控制规则的匹配顺序。ProxySQL 在处理查询请求时,会按照rule_id
的升序顺序逐一匹配规则,直到找到第一个匹配的规则为止。一旦找到匹配的规则,ProxySQL 将根据该规则的定义来处理查询请求。这种排序的作用是确保规则按照预期的顺序进行匹配和应用,以实现精确的查询路由、分流和负载均衡。在上述例子中,
select ... for update
规则,确保其rule_id
小于普通的select
规则的rule_id
是为了确保在匹配时先匹配到select ... for update
规则,而不是普通的select
规则。因为
select ... for update
是一种特殊的查询,它在执行时会涉及到锁定操作,可能会影响其他查询的执行。通过让select ... for update
的rule_id
更小,可以确保 ProxySQL 在匹配查询规则时优先考虑匹配这个特殊的规则,从而在处理select ... for update
时能够更精确地应用相应的路由和处理逻辑。
save使内存数据永久存储到磁盘,load使内存数据加载到runtime生效:
greatsql> save mysql users to disk;
greatsql> save mysql servers to disk;
greatsql> save mysql query rules to disk;
greatsql> save mysql variables to disk;
greatsql> save admin variables to disk;
greatsql> load mysql users to runtime;
greatsql> load mysql servers to runtime;
greatsql> load mysql query rules to runtime;
greatsql> load mysql variables to runtime;
greatsql> load admin variables to runtime;
加载完成后,可以使用select * 查询下设置的各表的信息是否有误
验证监控信息 ProxySQL 监控模块的指标都保存在monitor库的log表中
,以下是连接是否正常的监控,对connect指标的监控 ,在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常
greatsql> select * from monitor.mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 172.17.139.77 | 3307 | 1692152694528579 | 2347 | NULL |
| 172.17.139.77 | 3306 | 1692152695231434 | 3131 | NULL |
+---------------+------+------------------+-------------------------+---------------+
#只截取部分
对心跳信息的监控(对ping 指标的监控)
greatsql> select * from mysql_server_ping_log limit 10;
+---------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------+
| 172.17.139.77 | 3306 | 1692152694511424 | 132 | NULL |
| 172.17.139.77 | 3307 | 1692152694511498 | 125 | NULL |
+---------------+------+------------------+----------------------+------------+
#只截取部分
测试读写分离
通过proxysql 连接看看读操作,是否路由给了读组
$ mysql -uproxysql -pProxysql@666 -P 6033 -h 172.17.139.77 -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
| 103317 |
+-------------+
#可以看到已经把路由分配给了读组
测试下写操作
greatsql> begin;
Query OK, 0 rows affected (0.00 sec)
greatsql> select User from mysql.user for update;
+------------------+
| User |
+------------------+
| monitor |
| proxysql |
| repl |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
7 rows in set (0.00 sec)
greatsql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 103306 |
+-------------+
1 row in set (0.00 sec)
#可以看到已经把路由分配给了写组
greatsql> commit;
Query OK, 0 rows affected (0.00 sec)
如果想在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest
greatsql> select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;
+-----------+--------------------+----------+----------------------------------------+------------+
| hostgroup | schemaname | username | digest_text | count_star |
+-----------+--------------------+----------+----------------------------------------+------------+
| 10 | information_schema | proxysql | commit | 1 |
| 10 | information_schema | proxysql | select User from mysql.user for update | 1 |
| 10 | information_schema | proxysql | select * from beta1 for update | 2 |
| 10 | information_schema | proxysql | begin | 1 |
| 30 | information_schema | proxysql | select @@server_id | 2 |
+-----------+--------------------+----------+----------------------------------------+------------+
#只截取部分
-
count_start
统计 SQL 语句次数,可以分析哪些 SQL ,频繁执行
至此一个GreatSQL MGR + ProxySQL集群搭建方案到此部署完成
结尾
虽然ProxySQL的功能强大,但是ProxySQL毕竟不是官方原生的,在和MGR的配合上不如GreatSQL-MySQL-Router更顺滑,例如还需要额外创建存储过程以监控MGR的变化。此外就是ProxySQL的BUG其实也挺多的,当然了,如果是业务量不大,或者出于学习、实验用途,选用ProxySQL也是可以的。
推荐使用GreatSQL-MySQL-Router
,对GreatSQL MGR的配合更加丝滑,兼容度更高。
使用GreatSQL-MySQL-Router
构建MGR集群构建读写分离方案:https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/6-oper-guide/6-3-oper-rw-splitting.md
Enjoy GreatSQL 🙂
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。
相关链接: GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区:
社区有奖建议反馈: https://greatsql.cn/thread-服务器托管网54-1-1.html
社区博客有奖征稿详情: https://greatsql.cn/thread-100-1-1.html
(对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~)
技术交流群:
微信&QQ群:
QQ群:533341697
微信群:添加GreatSQL社区助手(微信号:wanlidbc
)好友,待社区助手拉您进群。
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net