MGR 单主配置 ,以及报错处理

本文详细介绍了如何配置MySQL MGR集群参数,包括group replication settings、用户权限设置和故障模拟演练。重点讲解了如何启动群主节点、分配角色以及解决常见错误如local disjoint GTIDs和成员状态问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

01.配置参数:

vi /mysql/data/3306/my.cnf

#bind_address= 0.0.0.0  注释掉    loose-group_replication_local_address 改掉    #server_id改掉

########group replication settings########

log_bin=/mysql/log/3306/binlog/msyqldb-binlog

log_bin_index=/mysql/log/3306/binlog/msyqldb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

binlog_checksum=none

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=4

slave_preserve_commit_order=1

gtid_mode = on

enforce_gtid_consistency = 1

log-slave-updates = 1

binlog_gtid_simple_recovery=1

relay_log = /mysql/log/3306/relaylog/msyqldb-relay.log

relay-log-index = /mysql/log/3306/relaylog/msyqldb-relay.index

master_info_repository=table

relay_log_info_repository=table

#MGR

plugin_load="group_replication=group_replication.so"

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address="192.168.0.51:33006"

loose-group_replication_group_seeds="192.168.0.51:33006,192.168.0.52:33006,192.168.0.53:33006"

loose-group_replication_bootstrap_group= off

loose-group_replication_member_weight=50

systemctl restart mysqld

02.第一个节点:

mysql -uroot -proot

set sql_log_bin=0;

create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

change master to

master_user='repuser',

master_password='repuser123'

for channel 'group_replication_recovery';

-- install plugin group_replication soname 'group_replication.so';

show plugins;

set global group_replication_bootstrap_group=on;   #作为一个群主起来

start group_replication;

set global group_replication_bootstrap_group=off;

select * from performance_schema.replication_group_members;

03.第二个节点:

mysql -uroot -proot

set sql_log_bin=0;

create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

change master to

master_user='repuser',

master_password='repuser123'

for channel 'group_replication_recovery';

-- install plugin group_replication soname 'group_replication.so';

show plugins;

start group_replication;

select * from performance_schema.replication_group_members;

  1. 配置 mgr 的其它节点:

mysql -uroot -proot

set sql_log_bin=0;

create user repuser@'%' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'%';

create user repuser@'127.0.0.1' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'127.0.0.1';

create user repuser@'localhost' identified by 'repuser123';

grant replication slave,replication client on *.* to repuser@'localhost';

set sql_log_bin=1;

change master to

master_user='repuser',

master_password='repuser123'

for channel 'group_replication_recovery';

-- install plugin group_replication soname 'group_replication.so';

show plugins;

start group_replication;

select * from performance_schema.replication_group_members;

有什么报错看下面的ERROR

 

  1. 测试:

create database itpuxdb1;

use itpuxdb1;

create table itpuxdb1.itpuxbak11 (id int primary key ,name varchar(40));

insert into itpuxdb1.itpuxbak11 values(1,'itpux111'),(2,'itpux112'),(3,'itpux113'),(4,'itpux114'),(5,'itpux115');

commit;

select * from itpuxdb1.itpuxbak11;

select * from itpuxdb1.itpuxbak11;

 

 

  1. #如何查找集群中的主节点

1)show variables like 'read_only';

2)select b.member_id, b.member_host, b.member_port from performance_schema.global_status a join performance_schema.replication_group_members b on a.variable_value = b.member_id where a.variable_name= 'group_replication_primary_member';

3)select @@read_only, @@super_read_only;

#第一个节点可写,后面节点,root和复制用户都是只读

  1. 故障模拟:

./mysql.stop 停掉主节点01

02,03自动选择一个上升为主,另一个为备库

01起来,start group_replication;

数据自动同步,上升为备库,只写

mysql> show variables like 'read_only';

insert into itpuxdb1.itpuxbak11 values(31,'itpux111'),(32,'itpux112'),(33,'itpux113'),(34,'itpux114'),(35,'itpux115');

 

 

 

  1. 这里是两个报错:处理

1)error:

set global group_replication_allow_local_disjoint_gtids_join=ON;

这个参数开启的目的,假如当前数据库上的事务在组复制上不存在,也允许加入组复制

2)error

MGR member state   卡在recovering

select * from performance_schema.replication_group_member_stats \G ;

show global variables like '%gtid%' ;

set global gtid_purged='98ecc24f-8349-11eb-b26e-000c29672232:1-6,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-3';

change master to

master_user='repuser',

master_password='repuser123'

for channel 'group_replication_recovery';

start group_replication;

select * from performance_schema.replication_group_members;

3)三台机器,单主,主宕机了,主机恢复后,宕机后的主出现下图问题

stop group_replication;

start group_replication;

 

4)有锁  cant execute the given command bacause you have active locaked tables or an active transaction 

有数据插入整个集群,需要提交下事务

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值