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;
-
配置 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
-
测试:
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)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和复制用户都是只读
-
故障模拟:
./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)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
有数据插入整个集群,需要提交下事务