ProxySQL cluster+MGR

最新推荐文章于 2024-06-03 21:45:49 发布
最新推荐文章于 2024-06-03 21:45:49 发布
阅读量466 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41690116/article/details/127667153
版权

部署规划

系统hostnameIP地址端口角色安装程序
Centos 7.8wl_01172.31.0.1013306PRIMARYMySQL8.0.28
Centos 7.8wl_02172.31.0.1023306SECONDARYMySQL8.0.28
Centos 7.8wl_03172.31.0.1033306SECONDARYMySQL8.0.28
Centos 7.8wl_04172.31.0.104ProxySQL:6032、6033读写分离、业务部署ProxySQL、业务代码
Centos 7.8wl_05172.31.0.105ProxySQL:6032、6033读写分离、业务部署ProxySQL、业务代码

提前部署好mysql MGR环境

mysql> select MEMBER_ID,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;
+--------------------------------------+--------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+--------------+-------------+--------------+-------------+
| 1b3163f8-5a57-11ed-845e-52549e082d61 | 172.31.0.102 |        3306 | ONLINE       | SECONDARY   |
| 2569bcbb-5a57-11ed-8e01-52549e4107ef | 172.31.0.103 |        3306 | ONLINE       | SECONDARY   |
| f9181e7f-5a56-11ed-8ed6-52549e1b2dbf | 172.31.0.101 |        3306 | ONLINE       | PRIMARY     |
+--------------------------------------+--------------+-------------+--------------+-------------+

(1)'创建proxysql的监控用户'
mysql> create user 'proxysql_monitor'@'172.31.0.%' identified with mysql_native_password by 'proxysql_monitor123';
mysql> grant replication client on *.* to 'proxysql_monitor'@'172.31.0.%';

(2)'创建业务用户,用于测试'
mysql> create user 'appuser'@'172.31.0.%' identified with mysql_native_password by '123';
mysql> grant all on appdb.* to 'appuser'@'172.31.0.%';

创建系统视图

在MGR集群的primary节点上创建如下视图：(可以在官方网站获取视图https://proxysql.com/documentation/main-runtime/#mysql_group_replication_hostgroups)

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
BEGIN
  DECLARE 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 DO
     SET 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 THEN
       SET 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;
     ELSE
       SET 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
BEGIN
  RETURN (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
BEGIN
  RETURN (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 ;


#给proxysql的监控用户添加关于系统视图的权限
mysql> GRANT SELECT ON sys.* TO 'proxysql_monitor'@'172.31.0.%';

#验证视图
mysql> select gr_member_in_primary_partition();
mysql> select * from sys.gr_member_routing_candidate_status;

在这里插入图片描述

视图报错的解决办法

如果上面视图查询时，出现

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

的报错，可能是因为mysql版本太新，继续执行以下操作可以消除报错：

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
BEGIN
  RETURN (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 ;

1.下载安装proxysql（wl_04、wl_05）

(1)获取rpm包
[root@wl_05 opt]# wget https://github.com/sysown/proxysql/releases/download/v2.4.4/proxysql-2.4.4-1-centos7.x86_64.rpm
(2)安装依赖包
[root@wl_05 opt]# yum install -y perl-DBD-MySQL  perl-DBI  perl-Time-HiRes perl-IO-Socket-SSL gnutls
(3)安装proxysql
[root@wl_05 opt]# rpm -ivh proxysql-2.4.4-1-centos7.x86_64.rpm 
(4)创建数据目录
[root@wl_05 opt]# mkdir /proxysql
[root@wl_05 opt]# chown proxysql.proxysql -R /proxysql

2.编辑proxysql的配置文件（wl_04、wl_05）

[root@wl_05 opt]# cat /etc/proxysql.cnf 
datadir="/proxysql"
errorlog="/proxysql/proxysql.log"
admin_variables=
{
        admin_credentials="admin:admin;cluster_user:cluster_user123"  #配置用于proxysql cluster实例节点间通讯的账号
        mysql_ifaces="0.0.0.0:6032"                                   #全网开放登录
        cluster_username="cluster_user"                            #集群用户名称,与admin_credentials中配置的相同
        cluster_password="cluster_user123"                         #集群用户密码,与admin_credentials中配置的相同
        cluster_check_interval_ms=200                     
        cluster_check_status_frequency=100
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3
}
mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        interfaces="0.0.0.0:6033"    #代理请求的端口
        default_schema="information_schema"
        stacksize=1048576
        server_version="8.0.28"    #指定后端数据库版本
        connect_timeout_server=3000
        monitor_username="proxysql_monitor"    #监控用户
        monitor_password="proxysql_monitor123"    #监控用户的密码
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
} 
proxysql_servers =      #在这个部分提前定义好集群的成员                        
(
        {
                hostname="172.31.0.104"
                port=6032
                weight=1
                comment="ProxySQL_node1"
        },
        {
                hostname="172.31.0.105"
                port=6032
                weight=1
                comment="ProxySQL_node2"
        }
)

3.启动proxysql服务（wl_04、wl_05）

[root@wl_04 opt]# /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
[root@wl_04 opt]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql> select * from runtime_proxysql_servers;
+--------------+------+--------+----------------+
| hostname     | port | weight | comment        |
+--------------+------+--------+----------------+
| 172.31.0.105 | 6032 | 1      | ProxySQL_node2 |
| 172.31.0.104 | 6032 | 1      | ProxySQL_node1 |
+--------------+------+--------+----------------+

mysql> select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics; 
+--------------+------+----------------+----------+---------------+
| hostname     | port | comment        | Uptime_s | last_check_ms |
+--------------+------+----------------+----------+---------------+
| 172.31.0.105 | 6032 | ProxySQL_node2 | 20       | 6827          |
| 172.31.0.104 | 6032 | ProxySQL_node1 | 20       | 11525         |
+--------------+------+----------------+----------+---------------+

4.添加数据库节点与读写组

4.1 添加mysql_servers

[root@wl_05 opt]# /usr/local/mysql_5733/bin/mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql> insert into mysql_servers(hostgroup_id,hostname,port,max_connections,comment) values(10,'172.31.0.101',3306,2000,'MGR_01');
mysql> insert into mysql_servers(hostgroup_id,hostname,port,max_connections,comment) values(10,'172.31.0.102',3306,2000,'MGR_02');
mysql> insert into mysql_servers(hostgroup_id,hostname,port,max_connections,comment) values(10,'172.31.0.103',3306,2000,'MGR_03');
mysql> select hostgroup_id,hostname,port,max_connections,comment,status from mysql_servers;

在这里插入图片描述

4.2 添加MGR hostgroups信息

mysql> show create table mysql_group_replication_hostgroups\G

在这里插入图片描述

  1. writer_hostgroup：默认的写组。后端read_only=0的节点会自动分配到这个组中。
  2. backup_writer_hostgroup：为备份节点组。超出max_writers数量的但允许写的节点都会放进备份组backup_writer_hostgroup中。
  3. reader_hostgroup：负责读的组。读请求都会路由到该主机组中的节点,后端read_only=1的节点会自动分配到这个组中。
  4. offline_hostgroup：当ProxySQL监控并决定了某节点为OFFLINE后，会将其放进组offline_hostgroup中。
  5. active：当启用后，ProxySQL会监控该主机组，并在不同组之间合理地移动节点。
  6. max_writers：该字段的值决定writer_hostgroup中最大允许的节点数。
  7. writer_is_also_reader：决定一个节点升级为写节点(放进writer_hostgroup)后是否仍然保留在reader_hostgroup组中提供读服务。
  8. max_transactions_behind： 当某节点延后于写节点时，为了防止读取到过期数据，ProxySQL可能会自动避开该节点。
  9. comment：该字段用于说明、注释。
mysql> 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,comment) values(10,11,12,13,1,1,0,10,'MGR_cluster_01');
mysql> select * from mysql_group_replication_hostgroups;

在这里插入图片描述

4.3 将配置加载到内存

mysql> load mysql servers to runtime;
mysql> save mysql servers to disk;
#查看当前runtime_mysql_servers,可以看到hostgroup_id已经自动根据后端db的read_only参数,调整为对应值
mysql> select hostgroup_id,hostname,port,max_connections,status from runtime_mysql_servers;

在这里插入图片描述

5.添加路由规则

'---需要注意: select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。'
[root@wl_05 opt]# /usr/local/mysql_5733/bin/mysql -uadmin -padmin123 -h172.31.0.103 -P6032
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',12,1);
mysql> load mysql query rules to runtime;
mysql> save mysql query rules to disk;
mysql> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

在这里插入图片描述

6. 添加用户

mysql> insert into mysql_users(username,password,default_hostgroup) values('appuser','123',10);

mysql> load mysql users to runtime;
mysql> save mysql users to disk;

7.测试读写分离

[root@wl_04 ~]# mysql -uappuser -p123 -P6033 -h172.31.0.102 -e "select @@hostname;"
+------------+
| @@hostname |
+------------+
| wl_02      |
+------------+
[root@wl_04 ~]# mysql -uappuser -p123 -P6033 -h172.31.0.102 -e "select @@hostname;"
+------------+
| @@hostname |
+------------+
| wl_03      |
+------------+
自撰良方服之卒
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
《童虎学习笔记》20分钟实战ProxySQL MGR高可用及读写分离架构
tonghu_note的博客
04-16 1413
1
免安装版proxysql+mgr搭建，亲测成功
03-05
文档中写了详细的proxysql搭建过程，绕过很多坑点。希望各位老司机指正。
ProxySQL + Mysql MGR实现数据库高可用
weixin_43790613的博客
09-04 731
数据库mysql
ProxySQL Cluster 概述
老叶茶馆
03-18 2422
导读作者：张灿（邮箱：1920153783@qq.com，欢迎交流）知数堂MySQL DBA 班第9期学员1：前言在ProxySQL 1.4.2 之前，ProxySQL 单点的解决方法有配合keepalived 使用来实现ProxySQL 的主备，但是需要在主备上配置两份完全相同的路由或规则，如果再没有自动运维平台，同时维护两份配置的也是相当麻烦的。而且目前主流的云环境，均不支持keepalive
ProxySQL + MySQL MGR 实现读写分离实战
Coder Xu
06-03 806
万字详解 MySQL MGR 高可用集群搭建ProxySQL 和 ShardingJDBC 属于竞品关系，国内使用ShardingJDBC更多，国外使用ProxySQL更多。当我们搭建好了MGR集群，由于组复制的存在，保证每个节点的数据都是强一致的。不会出现主节点有数据，而从节点没有数据的情况。
proxysql cluster 的搭建
weixin_34129145的博客
10-11 660
proxysql cluster 搭建笔记官方文档：https://proxysql.com/blog/proxysql-clusterhttps://proxysql.com/blog/proxysql-cluster-part2MySQL环境：主库 172.100.2.13从库1 172.100.2.11从库2 172.100.2.14proxysql节点：172...
ProxySQL+MGR单主
十五十六
06-07 3411
文章目录ProxySQL+MGR单主MGR单主模式搭建前期规划前期准备安装MySQL启动组复制ProxySQL简介多级配置系统常用的表mysql_serversmysql_replication_hostgroupsmysql_group_replication_hostgroupsmysql_usersmysql_query_rulesProxySQL节点规划前期准备安装ProxySQL启动Pr...
mysql集群按照proxysql-基于MGR组复制集群
最新发布
06-21
在本场景中，我们关注的是基于ProxySQL的MGR（Group Replication）组复制集群。ProxySQL是一个高性能、高可用的MySQL中间件，而MGR是MySQL的一种多主复制模式，提供了强大的数据一致性保证。 **ProxySQL的角色与...
MySQL、MySQL主从、MySQL-cluster、mgr、mha、读写分离.zip
06-20
文档内容：概念、二进制安装MySQL、对库的操作、对表的操作、表结构操作、表的插入、修改和删除记录、约束、查询、条件查询、函数、分组...以及高级应用的MySQL主从架构、读写分离、cluster集群、mgr集群、MHA高可用。
mgr.rar_MGR
09-19
`mgr.rar_MGR`这个压缩包包含的是与实现管理器接口到设备对象相关的源代码和头文件，主要由`mgr.c`和`mgr.h`组成。下面将详细讨论这两个文件可能包含的知识点以及它们在驱动开发中的作用。 1. **设备模型**：在...
proxysql + MGR(mysql group replication) +keepalived 搭建高可用mysql集群
qq_31024251的博客
08-17 545
proxyweb：ProxySQL的开源Web界面
02-25
代理网 开源Web UI 当前功能包括： 干净，响应式设计 全局和每服务器选项 隐藏未使用的表（基于全局或​​每个服务器） 按任何列对内容排序（asc / desc） 在线配置编辑器 缩小内容搜索 分页内容 设置 在ProxySQL旁边安装ProxyWeb 使用Doc ker： docker run -h proxyweb --name proxyweb --network="host" -d proxyweb/proxyweb:latest 将其安装为systemd服务（Ubuntu） git clone https://github.com/edmodo/proxyweb cd proxyweb make install 首先访问并根据需要调整凭据。 默认连接是具有默认凭据的本地连接。 安装ProxyWeb以与远程ProxySQL服务器一起使用 配置ProxySQL以进行
ProxySQL Cluster 高可用集群环境部署记录
weixin_30348519的博客
02-21 434
ProxySQL在早期版本若需要做高可用，需要搭建两个实例，进行冗余。但两个ProxySQL实例之间的数据并不能共通,在主实例上配置后，仍需要在备用节点上进行配置，对管理来说非常不方便。但是ProxySQl 从1.4.2版本后，ProxySQL支持原生的Cluster集群搭建，实例之间可以互通一些配置数据，大大简化了管理与维护操作。 ProxySQL是一个非中心化代理，在拓扑中，...
centos mysql proxy_mysql proxysql+mgr集群 centos7系统安装配置
weixin_39516865的博客
01-26 154
编译安装wget https://codeload.github.com/sysown/proxysql/tar.gz/v1.4.4tar xf v1.4.4cd proxysql-1.4.4yum install -y automake bzip2 cmake make gcc gcc-c++ git openssl openssldevel patchmakemake installyum 安...
Proxysql 的基础知识
zhangdd的博客
06-04 1967
Proxysql 的基础知识ProxySQL是一个可以实现MySQL读写分离的轻量级工具。ProxySQL的特点将所有配置保存写入到SQLit表中。支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。支持query cache。支持对query的路由，可以针对某个语句进行分配去哪个实例执行。故障切换。过滤危险的SQL。不支持分表，可以分库，但是利用规则配置实现分表。Prox...
MySQL MGR与Galera性能测试
热门推荐
陈海峰的博客
08-24 1万+
很多关注数据库技术的IT人士可能记不住去年双十二都剁手买了什么东西，但是一定会有人对当时一篇“Galera将死——MySQL Group Replication正式发布”的文章还有印象。 长期以来MySQL官方都缺少原生的MySQL集群多活方案，所以也给第三方公司提供了发展的机会。Galera就是其中的一个典型，两种基于Galera的方案MariaDB Galera Cluster 和Per
MySQL Group Replication HA with ProxySQL [主节点故障切换对应用无感知]
CrazyPig的技术博客
01-18 1万+
MGR单主模式下，实现主节点故障切换，对应用无感知
ProxySQL 搭建
叱咤少帅的博客
09-07 532
简介 ProxySQL 是基于 MySQL 的一款开源的中间件的产品，是一个灵活的 MySQL 代理层，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行缓存，支持动态加载（无需重启 ProxySQL 服务），故障切换和一些 SQL 的过滤功能。 ...
MYSQL数据库proxysql配置，实现读写分离
云之巢的博客
07-18 2723
我们用三个节点来做实验： node1节点：172.16.251.132 主节点 node2节点：172.15.252.32 从节点 node3节点：172.16.251.49 配置proxysql 先配置主从复制： node节点主节点： #ntpdate 172.16.0.1 同步时间 #vim /etc/my.cnf.d/server.cnf [mysqld] innodb_file
proxysql mgr
12-12
ProxySQL MGR（ProxySQL Management Group Replication）是一种基于ProxySQL的高可用性、高性能、分布式数据库管理工具。它是ProxySQL与MySQL Group Replication相结合的解决方案。 ProxySQL是一款开源的MySQL流量...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值