MySQL的MGR 安装多主模式部署

MySQL系列

一：MySQL 的MGR 集群部署

1.1 系统环境初始化

系统：CentOS7.9x64
cat /etc/hosts 
-----
192.168.100.11  node01.flyfish.com
192.168.100.12  node02.flyfish.com
192.168.100.13  node03.flyfish.com
192.168.100.14  node04.flyfish.com
192.168.100.15  node05.flyfish.com
192.168.100.16  node06.flyfish.com
192.168.100.17  node07.flyfish.com
192.168.100.18  node08.flyfish.com
192.168.100.19  node09.flyfish.com
------
本次安装前3三台，并安装好MySQL5.7.36

1.2 MySQL group Replication 多主模式项目实施

01.配置参数：
  vi /mysql/data/3306/my.cnf
  先注释掉：
    #bind-address = 0.0.0.0   (所有机器)
----
#### log_bin 参数####
log_bin=/mysql/log/3306/binlog/itpuxdb-binlog
log_bin_index=/mysql/log/3306/binlog/itpuxdb-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
gtid_mode = on 
enforce_gtid_consistency = 1 
log-slave-updates = 1 
binlog_gtid_simple_recovery=1
relay_log = /mysql/log/3306/relaylog/itpuxdb-relay.log 
relay-log-index = /mysql/log/3306/relaylog/itpuxdb-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.100.11:33006"   ### 这个地方不一样填写本地地址端口
loose-group_replication_group_seeds= "192.168.100.11:33006,192.168.100.12:33006,192.168.100.13:33006" 
loose-group_replication_bootstrap_group= off 
group_replication_single_primary_mode=off 
group_replication_enforce_update_everywhere_checks=on
-----
从启MySQL：
systemctl restart mysqld

关于参数备注：

02. 配置第一台机器：（192.168.100.11）
   ### 配置所用授权用户
   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;

配置第二台机器：（192.168.100.12）
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;

其他节点：
   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;

create database itpuxdb2; 
use itpuxdb2; 
create table itpuxdb2.itpuxbak11 (id int primary key ,name varchar(40)); insert into itpuxdb2.itpuxbak11 values (1,'itpux111'),(2,'itpux112'),(3,'itpux113'),(4,'itpux114'),(5,'itpux115');
commit; 
select * from itpuxdb2.itpuxbak11; 
insert into itpuxdb2.itpuxbak11 values (21,'itpux111'),(22,'itpux112'),(23,'itpux113'),(24,'itpux114'),(25,'i tpux115'); 
commit; 
select * from itpuxdb2.itpuxbak11; 
insert into itpuxdb2.itpuxbak11 values (31,'itpux111'),(32,'itpux112'),(33,'itpux113'),(34,'itpux114'),(35,'i tpux115'); 
commit;