MySQL8搭建MGR单主模式

一、介绍

MGR（MySQL Group Replication）复制，主结点收到SQL，同时发送到从结点，主从结点同时进行冲突检查，比较两个并发事务写操作时的数据集，行级别的。如两事务并发操作同一行数据，先执行结束的事务，所有结点commit，后执行结束事务，所有结点终止执行，主结点回滚，从结点删除。冲突检查过程称为certify，它确保数据一致性。事务进行commit操作时，采用paxos算法，必须超过一半结点同意，也就是事务在certify中过半结点通过冲突检查，主结点会提交事务。

在单主模式下，主结点挂掉后，从结点会选举新主结点，旧主结点从集群中剔除。

二、环境

2.1 软件版本

OS：CentOS Linux release 7.9.2009 (Core)

MySQL：8.0.28 MySQL Community Server - GPL

2.2 主机规划

主机	IP	类型
node1	172.17.0.2	主
node2	172.17.0.3	从1
node3	172.17.0.4	从2

三、搭建

3.1 配置hosts

172.17.0.3      node1
172.17.0.4      node2
172.17.0.5      node3

3.2 配置my.cnf

在my.cnf中[mysqld]下，新增如下配置

主

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON

binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="7c96bd57-4386-461d-b309-54ea9f7c6d22"
group_replication_start_on_boot=off
group_replication_local_address= "node1:33061"
group_replication_group_seeds= "node1:33061,node2:33061,node3:33061"
group_replication_bootstrap_group=off

从1

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON

binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="7c96bd57-4386-461d-b309-54ea9f7c6d22"
group_replication_start_on_boot=off
group_replication_local_address= "node1:33061"
group_replication_group_seeds= "node1:33061,node2:33061,node3:33061"
group_replication_bootstrap_group=off

从2

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON

binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'
group_replication_group_name="7c96bd57-4386-461d-b309-54ea9f7c6d22"
group_replication_start_on_boot=off
group_replication_local_address= "node1:33061"
group_replication_group_seeds= "node1:33061,node2:33061,node3:33061"
group_replication_bootstrap_group=off

3.3 创建复制账号

主、从1、从2

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY 'Mgr-0330';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Mgr-0330' FOR CHANNEL 'group_replication_recovery';

3.4 启动组复制

主

RESET MASTER;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

从1、从2

RESET MASTER;
START GROUP_REPLICATION;

3.5 检查

主、从1、从2

SELECT * FROM performance_schema.replication_group_members;

3.6 高可用宕机测试

主

pkill mysqld

从1或从2

SELECT * FROM performance_schema.replication_group_members;