MySQL-ProxySQL基于MGR的读写分离

之前已经跟大家介绍过了通过MYSQLSHELL部署MGR
MySQL-通过mysql-shell部署MGR
再简单的介绍一下MGR的功能:

  • mysql原生的高可用方案
  • 支持自动故障自愈
  • 支持自动差异数据补偿
  • 支持通过clone的方式快速拉起一个新节点

根据上文,我们已经部署出一套一主二从的MGR,现在需要通过ProxySQL配合MGR实现读写分离。
同样,本文主要介绍ProxySQL配合MGR做读写分离的部署流程,关于实现原理将在后文介绍。

部署流程

1、通过mysql-shell部署mgr

192.168.14.11:3306 master
192.168.14.12:3306 slave
192.168.14.13:3306 slave

2、部署ProxySQL

#下载地址
https://repo.proxysql.com

#rpm安装自带依赖
yum install -y proxysql-2.0.10-1-centos7.x86_64.rpm

验证proxysql是否安装成功
端口监听6033、6032

登录proxysql管理端
mysql -uadmin -padmin -h127.0.0.1 -P6032

3、创建proxysql监控用户和业务用户,并赋予权限(MGR-master)

create user proxysql_monitor@‘%’ identified with mysql_native_password by ‘123456’;
grant all ON . to proxysql_monitor@‘%’;
create user test_work@‘%’ identified with mysql_native_password by ‘123456’;
grant all ON . to test_work@‘%’;
flush privileges;

4、注册监控用户(ProxySQL)

set mysql-monitor_username=‘proxysql_monitor’;
set mysql-monitor_password=‘123456’;

load mysql variables to runtime;
save mysql variables to disk;

5、配置主从分组信息(ProxySQL)

insert into mysql_group_replication_hostgroups values (10,20,30,40,1,1,0,‘100’,‘proxysql’);

load mysql servers to runtime;
save mysql servers to disk;

mysql_group_replication_hostgroups详解

write_hostgroup:
默认情况下会将所有流量发送到这个组。具有read_only=0的节点也将分配到这个组;
backup_writer_hostgroup:
如果集群有多个写节点(read_only=0)且超过了max_writers规定数量,则会把多出来的写节点放到备用写组里面;
reader_hostgroup:
读取的流量应该发送到该组,只读节点(read_only=1)会被分配到该组;
offline_hostgroup:
当ProxySQL监视到某个节点不正常时,会被放入该组;
active:
是否启用主机组,当启用时,ProxySQL将监视主机在各族之间移动;
max_writers:
最大写节点的数量,超过该值的节点应该被放入backup_write_hostgroup;
writer_is_also_reader:
0:写节点只能写,读节点只能读
1:写节点能读写,读节点只能读
2:写节点能读写,另一个备用写节点只能读,其他节点,不能读写

在这里插入图片描述

6、创建视图(MGR-master)

USE sys;

DROP VIEW IF EXISTS gr_member_routing_candidate_status;
DROP FUNCTION IF EXISTS IFZERO;
DROP FUNCTION IF EXISTS LOCATE2;
DROP FUNCTION IF EXISTS GTID_NORMALIZE;
DROP FUNCTION IF EXISTS GTID_COUNT;
DROP FUNCTION IF EXISTS gr_applier_queue_length;
DROP FUNCTION IF EXISTS gr_member_in_primary_partition;
DROP FUNCTION IF EXISTS gr_transactions_to_cert;

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_transactions_to_cert() RETURNS int(11)
    DETERMINISTIC
BEGIN
  RETURN (select  performance_schema.replication_group_member_stats.COUNT_TRANSACTIONS_IN_QUEUE AS transactions_to_cert
    FROM
        performance_schema.replication_group_member_stats where MEMBER_ID=@@SERVER_UUID );
END$$

CREATE FUNCTION my_server_uuid() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

CREATE VIEW gr_member_routing_candidate_status AS
    SELECT 
        IFNULL((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 = my_server_uuid()),
                'NO') AS viable_candidate,
        IF((SELECT 
                    ((SELECT 
                                GROUP_CONCAT(performance_schema.global_variables.VARIABLE_VALUE
                                        SEPARATOR ',')
                            FROM
                                performance_schema.global_variables
                            WHERE
                                (performance_schema.global_variables.VARIABLE_NAME IN ('read_only' , 'super_read_only'))) <> 'OFF,OFF')
                ),
            'YES',
            'NO') AS read_only,
        IFNULL(sys.gr_applier_queue_length(), 0) AS transactions_behind,
        IFNULL(sys.gr_transactions_to_cert(), 0) AS transactions_to_cert;$$

DELIMITER ;

SELECT * FROM sys.gr_member_routing_candidate_status;

(master)

(slave)
在这里插入图片描述

7、添加服务器列表(ProxySQL)

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,‘192.168.14.11’,3306,1,‘mgr1’),(10,‘192.168.14.12’,3306,1,‘mgr2’),(10,‘192.168.14.13’,3306,1,‘mgr3’);

load mysql servers to runtime;
save mysql servers to disk;

mysql_servers具体说明

hostgroup_id:
ProxySQL经过hostgroup的形式组织后端db实例,一个hostgroup表明同属于一个角色,默认为0
hostname:
后端实例IP
port:
后端实例监听端口,默认为3306
status:
后端实例状态,默认为online,可取值为:
Ø online:当先后端实例状态正常
Ø shunned:临时被剔除,可能由于后端too many connections error,或者超过了可容忍延迟阀值max_replication_lag
Ø offline_soft:“软离线”状态,再也不接收新的链接,但已创建的链接会等待活跃事务完成
Ø offline_hard:“硬离线”状态,再也不接收新的链接,已创建的链接或被强制中断,当后端实例宕机或网络不可达时,会出现
weight:
后端实例权重,默认为1
max_connections:
容许链接到该后端实例的最大链接数,不能大于MySQL设置的max_connections,若是后端实例hostname:port在多个hostgroup中,以较大者为准,而不是各自独立容许的最大链接数,默认为1000
max_replication_lag:
容许的最大延迟,master节点不受此影响,默认为0,若是>0,monitor模块监控主从延迟大于阀值时,会临时把它变为shunned
max_latency_ms:
mysql_ping响应时长,大于这个阀值会把它从链接池中剔除(即便是ONLINE状态),默认为0
comment:
备注

8、配置访问用户(ProxySQL)

insert into mysql_users(username,password,default_hostgroup) values(‘test_work’,‘123456’,10);

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

9、配置路由规则,实现读写分离(ProxySQL)

insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,‘^select.*for update$’,10,1),(1,‘^select’,30,1);

load mysql query rules to runtime;
save mysql query rules to disk;

10、验证(ProxySQL)

select * from runtime_mysql_servers;

在这里插入图片描述

11、验证读写分离

读:
mysql -utest_work -p123456 -P6033 -h 127.0.0.1 -e “select@@server_id;”
写:
mysql -utest_work -p123456 -P6033 -h 127.0.0.1 -e “begin;select@@server_id;commit”;

测试故障自愈

MGR-master宕机
1、查看ProxySQL状态(ProxySQL)

select * from runtime_mysql_servers;

在这里插入图片描述

2、查看MGR状态(MGR)

select * from performance_schema.replication_group_members;

在这里插入图片描述

可以看到,master宕机后,MGR自动切换了主从关系,同时proxysql也自动切换的读写节点

MGR-宕机节点恢复
3、查看proxysql状态(ProxySQL)

select * from runtime_mysql_servers;

在这里插入图片描述

4、查看MGR状态(MGR)

select * from performance_schema.replication_group_members;

在这里插入图片描述

可以看到,宕机节点恢复后,自动回到MGR集群,同时在proxysql中也自动变成了读节点

彩蛋

当使用主从架构时,则在mysql_replication_hostgroups中配置分组信息(本文使用MGR模式,所以在mysql_group_replication_hostgroups中配置),同时不需要在MGR创建视图
实则: 使用MGR时也可以用mysql_replication_hostgroups配置不需要配置视图,其读写效果相当于mysql_group_replication_hostgroups的writer_is_also_reader配置为1
在这里插入图片描述可以看到,写节点即可写又可读,其他节点只可读。
这种效果其实就是为了当所有读节点都挂完以后,剩下的唯一一个主节点依旧可以提供读写服务,相当于变成了一个单实例的mysql。

  • 15
    点赞
  • 18
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
ProxySQL MGRProxySQL Management Group Replication)是一种基于ProxySQL的高可用性、高性能、分布式数据库管理工具。它是ProxySQLMySQL Group Replication相结合的解决方案。 ProxySQL是一款开源的MySQL流量路由工具,它能够通过代理服务器的方式将客户端的请求路由到底层的MySQL服务器。它具备负载均衡、故障转移、查询缓存、重放日志等功能,能够大大提高MySQL的性能和可用性。 而MySQL Group Replication则是MySQL官方提供的一种主从复制解决方案,它通过一组MySQL服务器来提供高可靠的数据复制和故障切换能力。 结合ProxySQLMySQL Group Replication,就形成了ProxySQL MGR。它利用ProxySQL对数据库流量进行管理和路由,同时借助MySQL Group Replication的高可用性和故障转移能力,实现了分布式数据库管理。 ProxySQL MGR的工作原理如下:当客户端发起请求时,请求会被发送到ProxySQLProxySQL会根据事先设定的规则将请求路由到适当的MySQL服务器。ProxySQL会监控MySQL Group Replication的状态,一旦主节点出现故障,ProxySQL会自动将请求路由到新的主节点上,实现了高可用性。 此外,ProxySQL还具备自动扩缩容、读写分离、连接池管理等功能,能够进一步提升数据库的性能和可扩展性。 总之,ProxySQL MGR是一个强大的分布式数据库管理工具,能够提供高可用性、高性能的数据库解决方案。它能够提高数据库的负载能力、可用性和可扩展性,是开发者和运维人员在构建大规模分布式数据库系统时的有力工具。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值