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。

相关文章

python管理工具之conda安装部署及使用详解

python管理工具之conda安装部署及使用详解

《python管理工具之conda安装部署及使用详解》这篇文章详细介绍了如何安装和使用conda来管理Python环境,它涵盖了从安装部署、镜像源配置到具体的conda使用方法,包括创建、激活、安装包... 目录pytpshheraerUhon管理工具：conda部署+使用一、安装部署1、 下载2、 安装3
阅读更多...
Python实现高效地读写大型文件

Python实现高效地读写大型文件

《Python实现高效地读写大型文件》Python如何读写的是大型文件,有没有什么方法来提高效率呢,这篇文章就来和大家聊聊如何在Python中高效地读写大型文件,需要的可以了解下... 目录一、逐行读取大型文件二、分块读取大型文件三、使用 mmap 模块进行内存映射文件操作（适用于大文件）四、使用 pand
阅读更多...
C# 读写ini文件操作实现

C# 读写ini文件操作实现

《C#读写ini文件操作实现》本文主要介绍了C#读写ini文件操作实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录一、INI文件结构二、读取INI文件中的数据在C#应用程序中，常将INI文件作为配置文件，用于存储应用程序的
阅读更多...
k8s部署MongDB全过程

k8s部署MongDB全过程

《k8s部署MongDB全过程》文章介绍了如何在Kubernetes集群中部署MongoDB,包括环境准备、创建Secret、创建服务和Deployment,并通过Robo3T工具测试连接... 目录一、环境准备1.1 环境说明1.2 创建 namespace1.3 创建mongdb账号/密码二、创建Sec
阅读更多...
Java中的Opencv简介与开发环境部署方法

Java中的Opencv简介与开发环境部署方法

《Java中的Opencv简介与开发环境部署方法》OpenCV是一个开源的计算机视觉和图像处理库,提供了丰富的图像处理算法和工具,它支持多种图像处理和计算机视觉算法,可以用于物体识别与跟踪、图像分割与... 目录1.Opencv简介Opencv的应用2.Java使用OpenCV进行图像操作opencv安装j
阅读更多...
将Python应用部署到生产环境的小技巧分享

将Python应用部署到生产环境的小技巧分享

《将Python应用部署到生产环境的小技巧分享》文章主要讲述了在将Python应用程序部署到生产环境之前,需要进行的准备工作和最佳实践,包括心态调整、代码审查、测试覆盖率提升、配置文件优化、日志记录完... 目录部署前夜：从开发到生产的心理准备与检查清单环境搭建：打造稳固的应用运行平台自动化流水线：让部署像
阅读更多...
Python项目打包部署到服务器的实现

Python项目打包部署到服务器的实现

《Python项目打包部署到服务器的实现》本文主要介绍了PyCharm和Ubuntu服务器部署Python项目,包括打包、上传、安装和设置自启动服务的步骤,具有一定的参考价值,感兴趣的可以了解一下... 目录一、准备工作二、项目打包三、部署到服务器四、设置服务自启动一、准备工作开发环境：本文以PyChar
阅读更多...
centos7基于keepalived+nginx部署k8s1.26.0高可用集群

centos7基于keepalived+nginx部署k8s1.26.0高可用集群

《centos7基于keepalived+nginx部署k8s1.26.0高可用集群》Kubernetes是一个开源的容器编排平台,用于自动化地部署、扩展和管理容器化应用程序,在生产环境中,为了确保集... 目录一、初始化（所有节点都执行）二、安装containerd（所有节点都执行）三、安装docker-
阅读更多...
在Ubuntu上部署SpringBoot应用的操作步骤

在Ubuntu上部署SpringBoot应用的操作步骤

《在Ubuntu上部署SpringBoot应用的操作步骤》随着云计算和容器化技术的普及,Linux服务器已成为部署Web应用程序的主流平台之一,Java作为一种跨平台的编程语言,具有广泛的应用场景,本... 目录一、部署准备二、安装 Java 环境1. 安装 JDK2. 验证 Java 安装三、安装 mys
阅读更多...
C#实现文件读写到SQLite数据库

C#实现文件读写到SQLite数据库

《C#实现文件读写到SQLite数据库》这篇文章主要为大家详细介绍了使用C#将文件读写到SQLite数据库的几种方法,文中的示例代码讲解详细,感兴趣的小伙伴可以参考一下... 目录1. 使用 BLOB 存储文件2. 存储文件路径3. 分块存储文件《文件读写到SQLite数据库China编程的方法》博客中，介绍了文
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2