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

张进佳

前言

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集群进行读写分离，集群状态检测，实现故障切换。
部署环境配置

部署软件详情

软件名版本号GreatSQL8.0.32-24ProxySQL2.5.4-58部署环境准备

本次采用的是单机多实例的部署方式，如何部署单机多实例可以前往➥https://gitee.com/GreatSQL/GreatSQL-Manual/blob/master/6-oper-guide/6-6-multi-instances.md
IP端口角色172.17.139.773306MGR01172.17.139.773307MGR02172.17.139.776032、6033ProxySQL

1. $ cat /etc/system-release
2. Red Hat Enterprise Linux Server release 7.9 (Maipo)
3. $ uname -a
4. 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配置

1. #mgr settings
2. loose-plugin_load_add = 'mysql_clone.so'
3. loose-plugin_load_add = 'group_replication.so'
4. loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
5. loose-group_replication_group_seeds = '172.17.139.77:33061,172.17.139.77:33071'
6. loose-group_replication_start_on_boot = ON
7. loose-group_replication_bootstrap_group = OFF
8. loose-group_replication_exit_state_action = READ_ONLY
9. loose-group_replication_flow_control_mode = "DISABLED"
10. loose-group_replication_single_primary_mode = ON
11. loose-group_replication_communication_max_message_size = 10M
12. loose-group_replication_transaction_size_limit = 3G
13. loose-group_replication_arbitrator = 0
14. loose-group_replication_single_primary_fast_mode = 0
15. loose-group_replication_request_time_threshold = 20000
16. report_host = "172.17.139.77"

复制代码

MGR01节点配置如下

1. [mysqld@mgr01]
2. datadir=/data/GreatSQL/mgr01
3. socket=/data/GreatSQL/mgr01/mysql.sock
4. port=3306
5. server_id=103306
6. log-error=/data/GreatSQL/mgr01/error.log
7. loose-group_replication_local_address= "172.17.139.77:33061"

复制代码

MGR02节点配置如下

1. [mysqld@mgr02]
2. datadir=/data/GreatSQL/mgr02
3. socket=/data/GreatSQL/mgr02/mysql.sock
4. port=3307
5. server_id=103317
6. log-error=/data/GreatSQL/mgr02/error.log
7. loose-group_replication_local_address= "172.17.139.77:33071"

复制代码

搭建MGR集群及ProxySQL

搭建GreatSQL MGR 集群

MGR01实例操作

1. greatsql> set session sql_log_bin=0;
2. greatsql> create user 'repl'@'%' identified with mysql_native_password by 'GreatSQL@666';
3. greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
4. greatsql> set session sql_log_bin=1;
5. greatsql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='GreatSQL@666' FOR CHANNEL 'group_replication_recovery';

复制代码

接下来即可启动MGR集群

1. greatsql> set global group_replication_bootstrap_group=ON;
2. greatsql> start group_replication;
3. greatsql> select * from performance_schema.replication_group_members\G
4. *************************** 1. row ***************************
5.               CHANNEL_NAME: group_replication_applier
6.                  MEMBER_ID: 2920447e-35bf-11ee-89a5-00163e566da1
7.                MEMBER_HOST: 172.17.139.77
8.                MEMBER_PORT: 3306
9.               MEMBER_STATE: ONLINE
10.                MEMBER_ROLE: PRIMARY
11.             MEMBER_VERSION: 8.0.32
12. MEMBER_COMMUNICATION_STACK: XCom

复制代码

MGR02实例操作

1. greatsql> set session sql_log_bin=0;
2. greatsql> create user 'repl'@'%' identified with mysql_native_password by 'GreatSQL@666';
3. greatsql> GRANT BACKUP_ADMIN, REPLICATION SLAVE ON *.* TO `repl`@`%`;
4. greatsql> set session sql_log_bin=1;
5. 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集群搭建成功

1. greatsql> select * from performance_schema.replication_group_members\G
2. *************************** 1. row ***************************
3.               CHANNEL_NAME: group_replication_applier
4.                  MEMBER_ID: 8f20696c-3b46-11ee-b59e-00163e566da1
5.                MEMBER_HOST: 172.17.139.77
6.                MEMBER_PORT: 3306
7.               MEMBER_STATE: ONLINE
8.                MEMBER_ROLE: PRIMARY
9.             MEMBER_VERSION: 8.0.32
10. MEMBER_COMMUNICATION_STACK: XCom
11. *************************** 2. row ***************************
12.               CHANNEL_NAME: group_replication_applier
13.                  MEMBER_ID: 8ff26a55-3b46-11ee-bd70-00163e566da1
14.                MEMBER_HOST: 172.17.139.77
15.                MEMBER_PORT: 3307
16.               MEMBER_STATE: ONLINE
17.                MEMBER_ROLE: SECONDARY
18.             MEMBER_VERSION: 8.0.32
19. MEMBER_COMMUNICATION_STACK: XCom

复制代码

在MGR集群上创建ProxySQL所需的账号

1. # proxysql的监控账户
2. greatsql> create user 'monitor'@'%' identified with mysql_native_password by 'Monitor@666';
3. greatsql> grant all privileges on *.* to 'monitor'@'%' with grant option;
4. # proxysql的对外访问账户
5. greatsql> create user 'proxysql'@'%' identified with mysql_native_password by 'Proxysql@666';
6. greatsql> grant all privileges on *.* to 'proxysql'@'%' with grant option;

复制代码

用户认证的方式需要修改为 mysql_native_password

看看有没有创建成功

1. greatsql> select User,Host from mysql.user;
2. +------------------+-----------+
3. | User             | Host      |
4. +------------------+-----------+
5. | monitor          | %         |
6. | proxysql         | %         |
7. | repl             | %         |
8. | mysql.infoschema | localhost |
9. | mysql.session    | localhost |
10. | mysql.sys        | localhost |
11. | root             | localhost |
12. +------------------+-----------+

复制代码

安装ProxySQL

ProxySQL文档中有详细的安装教程可以浏览➥https://github.com/sysown/proxysql
RPM方式和yum方式都可以安装的，本文采用RPM方式安装，如果要用yum安装需要更换yum源

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

复制代码

这些数据库作用如下：

• 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中间件（二）【微信添加】“
为配置监控账号

1. $ yum install proxysql

复制代码

上面这两句是修改变量的方式还可以在main库下面用sql语句方式修改

1. $ systemctl start proxysql
2. # 检查是否启动成功
3. $ systemctl status proxysql

复制代码

配置默认组信息

1. $ netstat -anlp | grep proxysql
2. tcp  0  0 0.0.0.0:6033  0.0.0.0:*  LISTEN 1009915/proxysql   
3. tcp  0  0 0.0.0.0:6032  0.0.0.0:*  LISTEN 1009915/proxysql  

复制代码

这段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内

1. $ mysql -uadmin -padmin -h 127.0.0.1 -P 6032

复制代码

这个 SQL 代码的作用是将一个 MySQL 服务器节点添加到 ProxySQL 的管理中，以便 ProxySQL 可以根据定义的规则和策略来分发连接请求，从而实现负载均衡和高可用性。

配置主节点定义为写组10,从节点定义为只读组30

1. greatsql> show databases;
2. +-----+---------------+-------------------------------------+
3. | seq | name          | file                                |
4. +-----+---------------+-------------------------------------+
5. | 0   | main          |                                     |
6. | 2   | disk          | /var/lib/proxysql/proxysql.db       |
7. | 3   | stats         |                                     |
8. | 4   | monitor       |                                     |
9. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
10. +-----+---------------+-------------------------------------+
11. 5 rows in set (0.00 sec)

复制代码

• 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+中可用）。

1. greatsql> set mysql-monitor_username='monitor';
2. greatsql> set mysql-monitor_password='Monitor@666';

复制代码

• 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生效：

1. greatsql>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
2. greatsql>UPDATE global_variables SET variable_value='Monitor@666' WHERE variable_name='mysql-monitor_password';

复制代码

加载完成后，可以使用select * 查询下设置的各表的信息是否有误

验证监控信息 ProxySQL 监控模块的指标都保存在monitor库的log表中 ，以下是连接是否正常的监控，对connect指标的监控 ，在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常

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

复制代码

对心跳信息的监控(对ping 指标的监控)

1. greatsql> insert into mysql_users(username,password,default_hostgroup)values('proxysql','Proxysql@666',10);

复制代码

测试读写分离

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

1. 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');
2. 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');
3. 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. greatsql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,^SELECT.*FORUPDATE$,10,1);
2. greatsql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',30,1);

复制代码

如果想在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest

1. greatsql> save mysql users to disk;
2. greatsql> save mysql servers to disk;
3. greatsql> save mysql query rules to disk;
4. greatsql> save mysql variables to disk;
5. greatsql> save admin variables to disk;
6. greatsql> load mysql users to runtime;
7. greatsql> load mysql servers to runtime;
8. greatsql> load mysql query rules to runtime;
9. greatsql> load mysql variables to runtime;
10. greatsql> load admin variables to runtime;

复制代码

• 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
来源:https://www.cnblogs.com/greatsql/p/17716565.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！