图文结合丨GreatSQL MGR + ProxySQL集群搭建方案

bashendan

发布于 2023-11-30 10:30

浏览

0收藏

前言

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集群搭建方案-鸿蒙开发者社区

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/Linux1.
2.
3.
4.

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"1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

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"1.
2.
3.
4.
5.
6.
7.

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"1.
2.
3.
4.
5.
6.
7.

搭建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';1.
2.
3.
4.
5.

接下来即可启动MGR集群

greatsql> set global group_replication_bootstrap_group=ON;
greatsql> start group_replication;
greatsql> select * from performance_schema.replication_group_members\G
*************************** 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: XCom1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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)1.
2.
3.
4.
5.
6.
7.

MGR集群搭建成功

greatsql> select * from performance_schema.replication_group_members\G
*************************** 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: XCom1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

在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;1.
2.
3.
4.
5.
6.

用户认证的方式需要修改为 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 |
+------------------+-----------+1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

安装ProxySQL

ProxySQL文档中有详细的安装教程可以浏览➥https://github.com/sysown/proxysql

RPM方式和yum方式都可以安装。本文采用RPM方式安装，如果要用yum安装需要更换yum源

$ cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/repo_pub_key
EOF1.
2.
3.
4.
5.
6.
7.

Red Hat 系统要把 \$releasever 改为 7

接着直接安装即可

$ yum install proxysql1.

这里要注意一下，如果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 proxysql1.
2.
3.

查看下端口是否开放

$ 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  1.
2.
3.

6032 是ProxySQL的管理端口号
6033 是对外服务的端口号

ProxySQL的用户名和密码都是默认的admin

配置ProxySQL

管理员登录

$ mysql -uadmin -padmin -h 127.0.0.1 -P 60321.

可以看到有一些数据库可用， 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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

这些数据库作用如下：

main：内存配置数据库使用此数据库，可以轻松地以自动方式查询和更新ProxySQL的配置。使用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';1.
2.

上面这两句是修改变量的方式还可以在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';1.
2.

配置默认组信息

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);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);1.

这个 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');1.
2.
3.

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);1.
2.

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;1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

加载完成后，可以使用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          |
+---------------+------+------------------+-------------------------+---------------+
#只截取部分1.
2.
3.
4.
5.
6.
7.
8.

对心跳信息的监控(对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       |
+---------------+------+------------------+----------------------+------------+
#只截取部分1.
2.
3.
4.
5.
6.
7.
8.

测试读写分离

通过proxysql 连接看看读操作，是否路由给了读组

$ mysql -uproxysql -pProxysql@666 -P 6033 -h 172.17.139.77 -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|      103317 |
+-------------+
#可以看到已经把路由分配给了读组1.
2.
3.
4.
5.
6.
7.

测试下写操作

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

如果想在 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          |
+-----------+--------------------+----------+----------------------------------------+------------+
#只截取部分1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

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

已于2023-11-30 10:30:39修改

51CTO

51CTO博客

51CTO学堂