环境准备

　　1、MySQL的MGR集群：参见【MySQL高可用集群五】MySQL的MGR集群

　　2、ProxySQL环境：参见【MySQL高可用集群六】ProxySQL安装部署

在准备好MySQL的MGR集群和 ProxySQL 服务以后，就可以通过 ProxySQL 进行 MGR 集群的代理了。

/etc/hosts 文件配置

#IP             域名|主机名
10.53.207.20    10.53.207.20  # mgr 
10.53.207.21    10.53.207.21  # mgr
10.53.207.22    10.53.207.22  # mgr
10.53.207.23    10.53.207.24  # proxysql

集群环境搭建

一、MySQL 服务相关配置（如果MGR是一主多从，则需要在MGR集群配置之前或者关闭MGR集群后进行配置，因为只读从节点无法执行写操作）

1、MySQL 每个节点添加两类数据库账号

　　监控账号：

　　ProxySQL 通过此账号连接MySQL服务，获取后端 MySQL 节点状态和 MGR集群状态（由于本例的集群复制配置了binlog_ignore_db，mysql 等库不会生成binlog日志，所以需要在每个节点都创建监控用户，没有配置binlog_ignore_db的话可以只在master上创建用户，会同步至其他slave节点）

create user 'monitor'@'10.53.207.%' identified with mysql_native_password by 'P@monitor';
#如果需要修改执行：ALTER user 'monitor'@'10.53.207.%' identified with mysql_native_password by 'P@monitor';
#监控普通主从复制时需要的权限
grant replication client on *.* to 'monitor'@'10.53.207.%';
#监控mgr集群时需要的权限,可以将mysql，sys，performance_schema，information_schema四个基本库进行授权，为了方便本人全部授权
grant select on *.* to `monitor`@`10.53.207.%`; 
flush privileges;

　　SQL执行账号：

　　ProxySQL通过此账号连接MySQL服务，发送需要执行的SQL语句并获得执行结果。此账号一般具有增删查改权限即可，其他额外权限用户可以自行增加授权。

create user 'operator'@'%' identified with mysql_native_password by 'P@operator';
grant insert,delete,select,update on *.* to 'operator'@'%';
flush privileges;

2、MySQL每个节点创建MGR信息监控视图

　　ProxySQL 通过监控账号连接MySQL服务，查询此MGR信息视图获取MGR集群信息。创建视图的脚本查看【MySQL高可用集群】相关SQL脚本中视图脚本第三条内容。视图需要创建在 sys 库下面，创建完后分别在各个节点查看视图来验证视图是否创建成功并生效。

SELECT * FROM sys.gr_member_routing_candidate_status;

由图可以看出视图已经正常创建并生效。视图中的 read_only 字段在 PRIMARY 节点为 NO，在 SECONDARY 节点为 YES。

二、ProxySQL 相关配置

1、账号相关功能配置

　　管理员账号：

　　ProxySQL通过管理员账号对ProxySQL进行配置的修改查询，可以通过 mysql 客户端用自定义的管理员账号进行登录，默认的账号 ‘admin:admin’ 只能在本地登录。

#查看管理员账户(console 入口的用户)
select @@admin-admin_credentials;
#设置管理员账户(admin账号只能在本地进行连接)
set admin-admin_credentials='admin:admin;hadmin:hpasswd';
#使修改立即生效
load admin variables to runtime;
#修改立即保存到磁盘
save admin variables to disk;

　　ProxySQL信息统计账号：

　　ProxySQL通过监控账号可以登录web界面查看ProxySQL提供的统计信息。

#查看统计用户(web 入口的用户)
select @@admin-stats_credentials;
#设置统计用户
set admin-stats_credentials='stats:stats;sqlstats:sqlstats';
#开启web界面访问，访问地址：https://服务IP:6080/
set admin-web_enabled=true;
load admin variables to runtime;
save admin variables to disk;

　　监控账号：

　　是在MySQL各个服务节点中已经创建的监控账号，ProxySQL通过此账号连接后端MySQL服务，进行相关状态监控。

set mysql-monitor_username='monitor';
set mysql-monitor_password='P@monitor';
load mysql variables to runtime;
save mysql variables to disk;

　　SQL执行账号：

　　是在MySQL各个服务节点中已经创建的SQL执行账号，ProxySQL 通过此账号向后端的MySQL服务发送SQL语句进行执行，并接受返回结果。同时外部应用可以通过此账号连接 ProxySQL代理执行数据库操作。default_hostgroup 表示默认的主机分组。通过 select * from runtime_mysql_users; 查询实时用户信息。

insert into mysql_users(username,password,default_hostgroup,comment) values('root','root',1,"root账户");
insert into mysql_users(username,password,default_hostgroup,comment) values('operator','P@operator',1,"数据操作账户");

2、集群相关配置

　　添加MGR 主机分组信息：

　　writer_is_also_reader 字段表示写节点是否也可以承担读取任务。默认 0 ，表示写节点只在写分组中（ProxySQL只会将写SQL路由到写节点），否则写节点会同时存在于读写组合写组中（读SQL也有机会路由到写节点）。ProxySQL会根据MySQL服务状态，自动将节点迁移至对应分组。

　　mysql_replication_hostgroups 分组是针对 MySQL传统的binlog主从分组集群。mysql_group_replication_hostgroups 时针对的 MGR 集群分组。

insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,writer_is_also_reader,comment) values (1,2,3,4,0,"mgr-cluster");
load mysql servers to runtime;
save mysql servers to disk;

　　添加MGR集群中的MySQL服务节点：

　　ProxySQL添加后端MySQL服务节点以后，会实时动态的将MySQL服务节点移动至对应的分组中。

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(1,'10.53.207.20',3306,"mgr-20");
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(1,'10.53.207.21',3306,"mgr-21");
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(1,'10.53.207.22',3306,"mgr-22");
load mysql servers to runtime;
save mysql servers to disk;

查看配置是否生效，服务是否正常，主机实时状态：

select * from runtime_mysql_servers;

查看 MGR 日志信息：

select hostname,viable_candidate,error from mysql_server_group_replication_log order by time_start_us limit 10;

MGR集群服务各项正常！

　　配置SQL路由规则：

　　ProxySQL根据配置的规则将SQL路由到指定的分组节点。规则的优先级根据rule_id的顺序决定，rule_id越大优先级越高，rule_id是自增的，也可以在插入时指定。SELECT…FOR UPDATE它会申请写锁，所以路由到分组 1 上。

INSERT INTO mysql_query_rules
(active,match_digest,destination_hostgroup,apply,comment)
VALUES 
(1,'^SELECT.*FOR UPDATE$',1,1,'加锁的查询'),
(1,'^SELECT',3,1,'普通查询'),
(1,'^DELETE',1,1,'删除操作'),
(1,'^UPDATE',1,1,'更新操作'),
(1,'^INSERT',1,1,'新增操作');
load mysql query rules to runtime;
save mysql query rules to disk;
#查看规则
select active,rule_id,destination_hostgroup,match_digest,apply,flagIN,flagOUT  from mysql_query_rules;

3、验证测试

远程通过mysql客户端向 ProxySQL 发送 SQL执行，进行集群负载验证。

读负载测试：

mysql -uoperator -pP@operator -P6033 -h10.53.207.24 -e "select @@server_id"

读写负载测试：

mysql -uoperator -pP@operator -P6033 -h10.53.207.24 -e "start transaction;select @@server_id;commit;select @@server_id;"

结果是写操作都能路由到写节点，读操作可以路由到只读节点。

读写节点动态切换测试：

停止 20写节点的组复制服务：stop group_replication; 可见 20 节点进入了 offline_hostgroup 分组。

重启 20 节点的组复制服务：start group_replication; 可见 20 节点以进入了 reader_hostgroup 分组。

原创文章，引用或转载注明出处：https://www.cnblogs.com/hewei-blogs/articles/17662162.html

posted @ 2023-08-28 14:13 蓝迷梦阅读(621) 评论(0) 收藏举报

刷新页面返回顶部

蓝迷梦

【MySQL高可用集群八】ProxySQL&MGR 环境搭建

公告

蓝迷梦

【MySQL高可用集群 八】ProxySQL&MGR 环境搭建

公告

【MySQL高可用集群八】ProxySQL&MGR 环境搭建