MySQL Group Replication 在5.7.17版本之后出现的组复制技术。
操作系统：centos7.9
MySQL版本：8.0.24
查看uuid值，作为组复制时使用
$ cat /proc/sys/kernel/random/uuid
ba43a6fa-1b0f-43ef-a7e6-b201604bc368
db01 配置文件
$ cat .my.cnf
[mysqld]
basedir=/opt/mysql/
datadir=/opt/mysql/data
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/opt/mysql/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=192.168.3.112
report_port=3306
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ba43a6fa-1b0f-43ef-a7e6-b201604bc368"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.3.112:33061"
loose-group_replication_group_seeds="192.168.3.112:33061,192.168.3.113:33061,192.168.3.114:33061"
loose-group_replication_bootstrap_group=OFF
[mysql]
prompt=db01 [\\d]>
db2配置文件
$ cat .my.cnf
[mysqld]
basedir=/opt/mysql/
datadir=/opt/mysql/data
server_id=52
port=3306
secure-file-priv=/tmp
log_bin=/opt/mysql/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=192.168.3.113
report_port=3306
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ba43a6fa-1b0f-43ef-a7e6-b201604bc368"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.3.113:33061"
loose-group_replication_group_seeds="192.168.3.112:33061,192.168.3.113:33061,192.168.3.114:33061"
loose-group_replication_bootstrap_group=OFF
[mysql]
prompt=db02 [\\d]>
db3配置文件
$ cat .my.cnf
[mysqld]
basedir=/opt/mysql/
datadir=/opt/mysql/data
server_id=53
port=3306
secure-file-priv=/tmp
log_bin=/opt/mysql/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=192.168.3.114
report_port=3306
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
binlog_transaction_dependency_tracking = WRITESET
##指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction=XXHASH64
##group_replication变量使用的loose-前缀是指示Server启用时尚未加载复制插件也将继续启动
loose-group_replication_group_name="ba43a6fa-1b0f-43ef-a7e6-b201604bc368"
##设置为Server启动时不自动启动组复制
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.3.114:33061"
loose-group_replication_group_seeds="192.168.3.112:33061,192.168.3.113:33061,192.168.3.114:33061"
loose-group_replication_bootstrap_group=OFF
[mysql]
prompt=db03 [\\d]>
db1、db2、db3初始化数据库
$ cd bin
$ ./mysqld --initialize-insecure
db1、db2、db3启动数据库
[mysql@db2 support-files]$ ./mysql.server start
所有节点数据库初始设置
$ mysql -S /tmp/mysql.sock -e "alter user root@'localhost' identified with mysql_native_password by '123';"
## 安装插件
$ mysql -uroot -p123 -S /tmp/mysql.sock -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
## 创建复制用户
$ mysql -uroot -p123 -S /tmp/mysql.sock
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY '123';
CREATE USER repl@'localhost' IDENTIFIED BY '123';
CREATE USER repl@'127.0.0.1' IDENTIFIED BY '123';
GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
grant replication slave,replication client on *.* to repl@'localhost' ;
grant replication slave,replication client on *.* to repl@'127.0.0.1' ;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
启动单主模式MGR，主节点可以读写，从节点只能读
只有 db1 执行
db01 [(none)]>change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';
db01 [(none)]>SET GLOBAL group_replication_bootstrap_group=ON;
db01 [(none)]>start group_replication;
db01 [(none)]>SET GLOBAL group_replication_bootstrap_group=OFF;
db01 [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 03641de9-f974-11ed-97b4-000c29ea20fd | 192.168.3.112 | 3306 | ONLINE | PRIMARY | 8.0.24 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
其他节点加入组中，执行以下命令
db03 [(none)]>reset master;
db03 [(none)]>change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';
db03 [(none)]>start group_replication;
## 切换主节点，指定 memory_id
SELECT group_replication_set_as_primary('7e0649b3-ded7-11eb-9dd0-005056bef803');
## 切换到单主模式
SELECT group_replication_switch_to_single_primary_mode();
## 切换多主模式
SELECT group_replication_switch_to_multi_primary_mode();
单主切换多主，手动执行
MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置
group_replication_single_primary_mode=OFF 等参数，再启动组复制。
1、该模式启用需设置两个参数
group_replication_single_primary_mode=0 #这个参数很好理解，就是关闭单master模式
group_replication_enforce_update_everywhere_checks=1 #这个参数设置多主模式下各个节点
严格一致性检查
================
db02 [(none)]>stop GROUP_REPLICATION;
db02 [(none)]>set global group_replication_single_primary_mode=OFF;
db02 [(none)]>set global group_replication_enforce_update_everywhere_checks=1;
db03 [(none)]>stop GROUP_REPLICATION;
db03 [(none)]>set global group_replication_single_primary_mode=OFF;
db03 [(none)]>set global group_replication_enforce_update_everywhere_checks=1;
select
@@group_replication_single_primary_mode,@@group_replication_enforce_update_every
where_checks;
===============
2、 默认启动的都是单master模式，其他节点都设置了read_only、super_read_only这两个参数，需要
修改这两个配置
===========
db03 [(none)]>set global read_only=0;
db03 [(none)]>set global super_read_only=0;
===========
3、 完成上面的配置后就可以执行多点写入了，多点写入会存在冲突检查，这耗损性能挺大的，官方建议采用
网络分区功能，在程序端把相同的业务定位到同一节点，尽量减少冲突发生几率。
# 停止组复制(所有节点执行)：
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
# 随便选择某个节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他节点执行
START GROUP_REPLICATION;
# 查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY，
SELECT * FROM performance_schema.replication_group_members;
多主模式切换单主模式，手动操作
# 所有节点执行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;
# 主节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点执行
START GROUP_REPLICATION;
# 查看MGR组信息，SELECT * FROM performance_schema.replication_group_members;