mysql5.7 MGR multi-master + proxysql
结构
主机名 | IP | 端口 | 作用 |
proxysql | 172.17.0.7 | 6032,6033 | 代理 |
master | 172.17.0.3 | 3306 | MGR集群 |
slave1 | 172.17.0.4 | 3306 | |
slave2 | 172.17.0.3 | 3306 | |
slave3 | 172.17.0.6 | 3306 |
MGR部署为多主, 检查集群状态
mgr其中一个节点运行sql 脚本,在sys库中建立function,地址如下:
https://github.com/zhishutech/mysql_gr_routing_check
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 ;
master> source /root/addition_to_sys.sql;
查看各个节点状态, 由于采用multi-master 模式,所有的节点都可写入,所以read_only 为NO
安装 proxysql
[root@proxysql ~]# yum -y install proxysql-2.0.10-1-centos7.x86_64.rpm
启动 proxysql
systemctl start proxysql.service
建立监控账号:
MGR 其中一个节点建立 monitor 账号(proxysql 配置文件默认的 cat /etc/proxysql.conf)
create user 'monitor'@'172.17.0.%' identified by 'monitor'; grant select on sys.* to 'monitor'@'172.17.0.%'
proxysql 中需要有 mysql环境,登录ProxySQL管理端口进行配置
mysql -h 127.0.0.1 -u admin -padmin -P6032
MGR 分组规划
writer_hostgroup --------------- 100 backup_writer_hostgroup -------- 101 reader_hostgroup --------------- 102 offline_hostgroup -------------- 103
1. mysql_group_replication_hostgroups 表添加分组
insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,max_writers,writer_is_also_reader,max_transactions_behind,comment) values(100,101,102,103,1,0,100,'MGR');
2. 添加 MGR中的服务器 到proxysql, 由于所有节点都是可写的,hostgroup_id 为100.
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.3',3306,'master'); insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.4',3306,'slave1'); insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.5',3306,'slave2'); insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.6',3306,'slave3');
3. mysql_users
insert into mysql_users(username,password,default_hostgroup,default_schema,comment) values('fengjian','123456',100,'T800','add connection user');
4. 保存到runtime 和 disk
save mysql users to disk;
save mysql server to disk;
load mysql users to runtime;
load mysql servers to runtime;
5. 检查
6. 设置读写规则mysql_query_rules :
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(1,1,'^SELECT.*FOR UPDATE$',100,1),(2,1,'^SELECT',102,1);
保存到disk 和runtime
slave mysql query rules to disk;
load mysql query rules to runtime;
注意 runtime_mysql_servers 中, 没有读的库,所以需要设置 writer_is_also_reader 只为1,
ProxySQL代理多主模型的MGR时,必须设置writer_is_also_reader=1
update mysql_group_replication_hostgroups set writer_is_also_reader=1;
save mysql servers to disk;
laod mysql servers to runtime;
writer_is_also_reader
:决定一个节点升级为写节点(放进writer_hostgroup
)后是否仍然保留在reader_hostgroup
组中提供读服务。
7. 登录测试
有mysql客户端的机器: mysql -h 172.17.0.7 -u fengjian -p123456 -P6033
测试读, 发现一个问题,客户端连接后,读的库不变, 客户端再连接,可能连接到其他的读库上
mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec)
mysql> select @@server_id for update;
+-------------+
| @@server_id |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
参考:
https://www.lagou.com/lgeduarticle/9142.html
https://www.cnblogs.com/zhouwanchun/p/11456378.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
2019-03-26 kafka libjvm 报错