proxysql 2.6部署代理MGR集群读写分离

2024-03-06 22:20
文章标签 分离 部署 集群 代理 读写 2.6 proxysql mgr

本文主要是介绍proxysql 2.6部署代理MGR集群读写分离，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

官方文档

https://proxysql.com/documentation/ProxySQL-Configuration/

下载安装proxysql

https://github.com/sysown/proxysql/releases/download/v2.6.0/proxysql-2.6.0-1-centos7.x86_64.rpmyum -y localinstall proxysql-2.6.0-1-centos7.x86_64.rpm
# 软链接数据目录
rm -rf /var/lib/proxysql/
mkdir -p /data/proxysql
ln -s /data/proxysql/ /var/lib/
chown -R proxysql.proxysql /var/lib/proxysql/
chown -R proxysql.proxysql /var/lib/proxysql# 启动proxysql
systemctl start proxysql
systemctl status proxysql
systemctl enable proxysql

防火墙放行

firewall-cmd --permanent --zone=public --add-port={6032..6033}/tcp && firewall-cmd --reload

下载安装mysql客户端

tar -zxvf mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar.gz
rpm -ivh mysql-community-client-8.0.19-1.el7.x86_64.rpm --nodeps --force

连接proxy管理接口配置

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '# 确认mysql_servers、mysql_replication_hostgroups、mysql_query_rules三张表的配置是否为空
SELECT * FROM mysql_servers;
SELECT * from mysql_replication_hostgroups;
SELECT * from mysql_query_rules;
proxysql添加后端mysql
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.200.31',3308);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.200.32',3308);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.200.33',3308);load mysql servers to runtime;
save mysql servers to disk;

MGR集群主节点添加监控账号

create user 'monitor'@'192.168.200.%' identified with mysql_native_password by 'UBXWlwDw4X93s%#GtXyx';GRANT SELECT on sys.* to 'monitor'@'192.168.200.%';
GRANT SELECT on performance_schema.* to 'monitor'@'192.168.200.%';

proxysql添加设置监控账号

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='UBXWlwDw4X93s%#GtXyx' WHERE variable_name='mysql-monitor_password';load mysql variables to runtime;
save mysql variables to disk;

MGR集群主节点添加监控视图

# addition_to_sys.sql MGR创建监控视图sql文件
https://lefred.be/content/mysql-group-replication-native-support-in-proxysql/

cat > addition_to_sys.sql << EOF
USE sys;DELIMITER $$CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGINDECLARE result BIGINT DEFAULT 0;DECLARE colon_pos INT;DECLARE next_dash_pos INT;DECLARE next_colon_pos INT;DECLARE next_comma_pos INT;SET gtid_set = GTID_NORMALIZE(gtid_set);SET colon_pos = LOCATE2(':', gtid_set, 1);WHILE colon_pos != LENGTH(gtid_set) + 1 DOSET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THENSET result = result +SUBSTR(gtid_set, next_dash_pos + 1,LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;ELSESET result = result + 1;END IF;SET colon_pos = next_colon_pos;END WHILE;RETURN result;
END$$CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGINRETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGINRETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$DELIMITER ;
EOF

cat > addition_to_sys2.sql << EOF
USE sys;DELIMITER $$DROP FUNCTION gr_member_in_primary_partition$$DROP VIEW gr_member_routing_candidate_status$$CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGINRETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats rgms USING(member_id) WHERE rgms.MEMBER_ID=@@SERVER_UUID);
END$$CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' 
from performance_schema.replication_group_member_stats rgms 
where rgms.MEMBER_ID=(select gv.VARIABLE_VALUE from `performance_schema`.global_variables gv where gv.VARIABLE_NAME='server_uuid');$$DELIMITER ;
EOF

加载视图sql

source /root/addition_to_sys.sql;

查看视图监控状态

select * from gr_member_routing_candidate_status;

如果报错

ERROR 1242 (21000): Subquery returns more than 1 row

source /root/addition_to_sys2.sql;

proxysql设置读写组

# 查看读写复制组表insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
values(10,20,30,40,1,1,0,0);load mysql servers to runtime;
save mysql servers to disk;

MGR集群状态查询

mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.04 sec)

proxysql状态查询

Admin> select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+----------------+--------+
| hostgroup_id | hostname       | status |
+--------------+----------------+--------+
| 10           | 192.168.200.32 | ONLINE |
| 20           | 192.168.200.31 | ONLINE |
| 20           | 192.168.200.33 | ONLINE |
+--------------+----------------+--------+

MGR主节点创建业务账号

create user 'gadmin'@'192.168.200.%' identified with mysql_native_password by 'UBXWlwDw4X93s%#GtXyx';GRANT all privileges on *.* to 'gadmin'@'192.168.200.%';

proxysql添加业务账号

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('gadmin','UBXWlwDw4X93s%#GtXyx',10);LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;SELECT * FROM mysql_users;

proxysql添加读写分离规则

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'gadmin','^SELECT.*FOR UPDATE$',10,1);INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'gadmin','^SELECT',30,1);load mysql query rules to runtime;
save mysql query rules to disk;# 查看规则
SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='gadmin' ORDER BY rule_id;# 查看路由状态
SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

测试读写分离

mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'create database proxysql_mgr_test;'mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'use proxysql_mgr_test;select * from tb01;'mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'show databases;'mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'drop database proxysql_mgr_test;'

这篇关于proxysql 2.6部署代理MGR集群读写分离的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/781544。

相关文章

通过Docker容器部署Python环境的全流程

通过Docker容器部署Python环境的全流程

《通过Docker容器部署Python环境的全流程》在现代化开发流程中,Docker因其轻量化、环境隔离和跨平台一致性的特性,已成为部署Python应用的标准工具,本文将详细演示如何通过Docker容... 目录引言一、docker与python的协同优势二、核心步骤详解三、进阶配置技巧四、生产环境最佳实践
阅读更多...
Nginx部署HTTP/3的实现步骤

Nginx部署HTTP/3的实现步骤

《Nginx部署HTTP/3的实现步骤》本文介绍了在Nginx中部署HTTP/3的详细步骤,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学... 目录前提条件第一步：安装必要的依赖库第二步：获取并构建 BoringSSL第三步：获取 Nginx
阅读更多...
ShardingProxy读写分离之原理、配置与实践过程

ShardingProxy读写分离之原理、配置与实践过程

《ShardingProxy读写分离之原理、配置与实践过程》ShardingProxy是ApacheShardingSphere的数据库中间件,通过三层架构实现读写分离,解决高并发场景下数据库性能瓶... 目录一、ShardingProxy技术定位与读写分离核心价值1.1 技术定位1.2 读写分离核心价值二
阅读更多...
redis-sentinel基础概念及部署流程

redis-sentinel基础概念及部署流程

《redis-sentinel基础概念及部署流程》RedisSentinel是Redis的高可用解决方案,通过监控主从节点、自动故障转移、通知机制及配置提供,实现集群故障恢复与服务持续可用,核心组件包... 目录一. 引言二. 核心功能三. 核心组件四. 故障转移流程五. 服务部署六. sentinel部署
阅读更多...
Redis中哨兵机制和集群的区别及说明

Redis中哨兵机制和集群的区别及说明

《Redis中哨兵机制和集群的区别及说明》Redis哨兵通过主从复制实现高可用,适用于中小规模数据；集群采用分布式分片,支持动态扩展,适合大规模数据,哨兵管理简单但扩展性弱,集群性能更强但架构复杂,根... 目录一、架构设计与节点角色1. 哨兵机制（Sentinel）2. 集群（Cluster）二、数据分片
阅读更多...
Spring Security 前后端分离场景下的会话并发管理

Spring Security 前后端分离场景下的会话并发管理

《SpringSecurity前后端分离场景下的会话并发管理》本文介绍了在前后端分离架构下实现SpringSecurity会话并发管理的问题,传统Web开发中只需简单配置sessionManage... 目录背景分析传统 web 开发中的 sessionManagement 入口ConcurrentSess
阅读更多...
Linux部署中的文件大小写问题的解决方案

Linux部署中的文件大小写问题的解决方案

《Linux部署中的文件大小写问题的解决方案》在本地开发环境（Windows/macOS）一切正常,但部署到Linux服务器后出现模块加载错误,核心原因是Linux文件系统严格区分大小写,所以本文给大... 目录问题背景解决方案配置要求问题背景在本地开发环境（Windows/MACOS）一切正常，但部署到
阅读更多...
MySQL中读写分离方案对比分析与选型建议

MySQL中读写分离方案对比分析与选型建议

《MySQL中读写分离方案对比分析与选型建议》MySQL读写分离是提升数据库可用性和性能的常见手段,本文将围绕现实生产环境中常见的几种读写分离模式进行系统对比,希望对大家有所帮助... 目录一、问题背景介绍二、多种解决方案对比2.1 原生mysql主从复制2.2 Proxy层中间件：ProxySQL2.3
阅读更多...
使用IDEA部署Docker应用指南分享

使用IDEA部署Docker应用指南分享

《使用IDEA部署Docker应用指南分享》本文介绍了使用IDEA部署Docker应用的四步流程：创建Dockerfile、配置IDEADocker连接、设置运行调试环境、构建运行镜像,并强调需准备本... 目录一、创建 dockerfile 配置文件二、配置 IDEA 的 Docker 连接三、配置 Do
阅读更多...
在MySQL中实现冷热数据分离的方法及使用场景底层原理解析

在MySQL中实现冷热数据分离的方法及使用场景底层原理解析

《在MySQL中实现冷热数据分离的方法及使用场景底层原理解析》MySQL冷热数据分离通过分表/分区策略、数据归档和索引优化,将频繁访问的热数据与冷数据分开存储,提升查询效率并降低存储成本,适用于高并发... 目录实现冷热数据分离1. 分表策略2. 使用分区表3. 数据归档与迁移在mysql中实现冷热数据分
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2