MGR 搭建部署

MGR（MySQL Group Replication）是 2016年12月 MySQL 5.7.17 版本引进来的一个数据库高可用架构，解决了传统异步复制和半同步复制的缺陷（主从数据一致性的问题），它基于原生复制及分布式paxos协议的组复制技术，以插件的方式，提供一致数据安全保证。

1.准备工作

MGR如何保持事务一致性：

MGR基于Paxos协议的原子广播机制，确保事务要么在所有节点上成功提交，要么都不提交，避免数据不一致。集群内每个节点都可以单独执行事务，但是read-write（RW）的操作只有在组内验证后才可以commit，Read-only (RO)事务是不需要验证可以立即执行，当一个事务在一个节点上提交之前，会在组内自动进行原子性的广播，告知其他节点变更了什么内容、执行了什么事务，然后为该事务建立一个全局的排序，最终，这意味着所有的服务器都以相同的顺序接收相同的事务集。因此，所有服务器都按照相同的顺序应用相同的变更集，因此它们在组中保持一致。

搭建MGR的前提条件：

存储引擎必须是Innodb，并且每张表一定要有主键，在进行事务冲突检测时需要利用主键值对比
必须开启GTID，且主从状态信息存于表中，binlog日志格式必须设置为ROW
目前一个MGR集群组最多9个节点，超过9台无法加入集群
多主模式不支持外键和SERIALIZABLE事务隔离级别，单主模式支持外键

机器规划：

新主选举时，权重越大(默认50)则优先级越高，若权重相等则版本越低的节点优先级越高，若权重相等、版本相同则server_uuid排序越靠前的节点优先级越高
本文涉及的 MGR 搭建过程，对 MySQL 5.7.44 及 MySQL 8.0.32 均做了完整测试

ip地址	主机名	ssh port	OS版本	MySQL版本	sql port	server-id	权重
192.168.31.101	mgr101	23245	Centos 7.9 x64	8.0.32	3406	311013406	50
192.168.31.102	mgr102	23245	Centos 7.9 x64	8.0.32	3406	311023406	40
192.168.31.103	mgr103	23245	Centos 7.9 x64	8.0.32	3406	311033406	30

[所有节点]：初始化工作

#.配置DNS和时区
cat /etc/resolv.conf | grep "^nameserver" || echo "nameserver 223.5.5.5" > /etc/resolv.conf
timedatectl set-timezone Asia/Shanghai
yum install -y -q ntpdate
/usr/sbin/ntpdate -u ntp.aliyun.com

#.配置阿里云yum源
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.$(date +%Y%m%d%H%M%S)
curl -sL http://mirrors.aliyun.com/repo/Centos-7.repo -o /etc/yum.repos.d/CentOS-Base.repo
yum makecache

#.安装基础软件
yum install -y -q wget ntpdate lrzsz telnet zip unzip net-tools make

#.关闭iptables及防火墙
iptables -F
systemctl stop firewalld.service
systemctl disable firewalld.service

#.更改机器名
newhost=`ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tail -1|awk -F'.' '{print "mgr"$4}'`
hostnamectl set-hostname $newhost

#.关闭SELinux
sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
setenforce 0

#.更改ssh默认端口为23245
sed -i "s/#Port/Port/" /etc/ssh/sshd_config
sed -i '/^Port/c Port 23245' /etc/ssh/sshd_config
grep "^Port" /etc/ssh/sshd_config
systemctl restart sshd.service

#.退出重连(更换了ssh端口)
exit

2.安装 mysql 8.0.32

[所有节点]：安装 MySQL 8.0.32

#.开始安装
cd /opt
wget -c http://iso.sqlfans.cn/mysql/percona-server-8.0.32-24.tar.gz
wget -c http://iso.sqlfans.cn/mysql/config/8.0/my_innodb.cnf
wget -c http://iso.sqlfans.cn/mysql/install_percona_8032.sh
bash install_percona_8032.sh /opt /data 3406

#.登录测试
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select host,user,plugin,account_locked from mysql.user;"

3.搭建 mgr 单主模式

3.1.初始化数据库

[任一节点]：初始化数据库，可避免本地节点的事务（Local transactions）大于集群事务（Group transactions）所带来的问题

#.定义节点信息
workdir=/data
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103
sql_port=3406

#.1.[任一节点]-在主库上创建复制专用账户
#.假如已创建dba_repl帐户，[先create再alter] 优于 [先drop再create] 可以避免权限被重置
echo "SET SQL_LOG_BIN=0;" > /tmp/mgr_login.txt
echo "CREATE USER IF NOT EXISTS 'dba_repl'@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';" >> /tmp/mgr_login.txt
echo "ALTER USER 'dba_repl'@'%' IDENTIFIED with mysql_native_password BY 'MA6RuouuZZn4x_Hd';" >> /tmp/mgr_login.txt
echo "GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';" >> /tmp/mgr_login.txt
echo "FLUSH PRIVILEGES;" >> /tmp/mgr_login.txt
echo "SET SQL_LOG_BIN=1;" >> /tmp/mgr_login.txt
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port < /tmp/mgr_login.txt
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"show grants for 'dba_repl'@'%';"

#.2.[任一节点]-备份主库的所有数据库
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"CREATE DATABASE IF NOT EXISTS xxx_001;"
mysqldump --single-transaction --master-data=2 --flush-logs --set-gtid-purged=on --triggers --events --routines --skip-tz-utc -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port --all-databases > ${workdir}/alldb.2025.sql

#.3.[任一节点]-恢复数据到2台从库
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e "reset master;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "reset master;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port < ${workdir}/alldb.2025.sql
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port < ${workdir}/alldb.2025.sql

3.2.通用配置

[所有节点]：创建hostname和ip的映射，group_replication_group_seeds 支持 ip地址:port 或机器名:port

#.定义节点信息
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103

#.修改hosts
cat /etc/hosts | grep $node1 || echo "$node1 mgr101" >> /etc/hosts
cat /etc/hosts | grep $node2 || echo "$node2 mgr102" >> /etc/hosts
cat /etc/hosts | grep $node3 || echo "$node3 mgr103" >> /etc/hosts
cat /etc/hosts | grep mgr

[所有节点]：调整 my.cnf 通用配置

#.定义节点信息
sql_port=3406
my_cnf=/data/mysql_$sql_port/my_$sql_port.cnf

#.去掉注释
sed -i "s/#disabled_storage_engines/disabled_storage_engines/" $my_cnf
sed -i "s/#binlog_format/binlog_format/" $my_cnf
sed -i "s/#binlog_checksum/binlog_checksum/" $my_cnf
sed -i "s/#log_slave_updates/log_slave_updates/" $my_cnf
sed -i "s/#relay_log_info_repository/relay_log_info_repository/" $my_cnf
sed -i "s/#master_info_repository/master_info_repository/" $my_cnf
sed -i "s/#gtid_mode/gtid_mode/" $my_cnf
sed -i "s/#enforce_gtid_consistency/enforce_gtid_consistency/" $my_cnf

#.修改配置，启用binlog及gtid并禁用非InnoDB的存储引擎
sed -i 's/^disabled_storage_engines.*/disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"/' $my_cnf
sed -i 's/^binlog_format.*/binlog_format=ROW/' $my_cnf
sed -i 's/^binlog_checksum.*/binlog_checksum=NONE/' $my_cnf
sed -i 's/^log_slave_updates.*/log_slave_updates=true/' $my_cnf
sed -i 's/^relay_log_info_repository.*/relay_log_info_repository=TABLE/' $my_cnf
sed -i 's/^master_info_repository.*/master_info_repository=TABLE/' $my_cnf
sed -i 's/^gtid_mode.*/gtid_mode=ON/' $my_cnf
sed -i 's/^enforce_gtid_consistency.*/enforce_gtid_consistency=ON/' $my_cnf

#.确认配置
cat $my_cnf | egrep "(^log_bin|^gtid|^enforce|repository|^binlog_format|updates|checksum|engines)"


#.示例如下
[root@mgr101 opt]# cat $my_cnf | egrep "(^log_bin|^gtid|^enforce|repository|^binlog_format|updates|checksum|engines)"
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
log_bin = /data/mysql_3406/binlog/mysql-bin
binlog_format=ROW
binlog_checksum=NONE
log_slave_updates=true
relay_log_info_repository=TABLE
master_info_repository=TABLE
gtid_mode=ON
enforce_gtid_consistency=ON

[所有节点]：调整 my.cnf 关于 mgr 的通用配置

#.定义节点信息
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103
sql_port=3406
mgr_port=34061
my_group_name="\"ce9be252-2b71-11e6-b8f4-00212844f856"\"
my_group_seeds="\"$node1:$mgr_port,$node2:$mgr_port,$node3:$mgr_port"\"
my_cnf=/data/mysql_$sql_port/my_$sql_port.cnf

#.去掉注释
sed -i "s/#transaction_write_set_extraction/transaction_write_set_extraction/" $my_cnf
sed -i "s/#loose-group_replication/loose-group_replication/" $my_cnf

#.修改配置
sed -i 's/^transaction_write_set_extraction.*/transaction_write_set_extraction=XXHASH64/' $my_cnf
sed -i "s/^loose-group_replication_group_name.*/loose-group_replication_group_name=$my_group_name/" $my_cnf
sed -i 's/^loose-group_replication_start_on_boot.*/loose-group_replication_start_on_boot=off/' $my_cnf
sed -i "s/^loose-group_replication_group_seeds.*/loose-group_replication_group_seeds=$my_group_seeds/" $my_cnf
sed -i 's/^loose-group_replication_bootstrap_group.*/loose-group_replication_bootstrap_group=off/' $my_cnf
sed -i 's/^loose-group_replication_single_primary_mode.*/loose-group_replication_single_primary_mode=TRUE/' $my_cnf
sed -i 's/^loose-group_replication_enforce_update_everywhere_checks.*/loose-group_replication_enforce_update_everywhere_checks=FALSE/' $my_cnf

#.确认配置
cat $my_cnf | grep -v "local_address" | egrep "(transaction_write_set_extraction|^loose-group)"


#.示例如下
[root@mgr101 opt]# cat $my_cnf | grep -v "local_address" | egrep "(transaction_write_set_extraction|^loose-group)"
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_group_seeds="192.168.31.101:34061,192.168.31.102:34061,192.168.31.103:34061"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=50
loose-group_replication_single_primary_mode=TRUE
loose-group_replication_enforce_update_everywhere_checks=FALSE

参数名称	参数解释
transaction_write_set_extraction=XXHASH64	使用哈希算法将其编码为散列
loose-group_replication_group_name	mgr的组名，必须是一个有效的uuid，所有节点相同，可修改，只要格式对
loose-group_replication_start_on_boot=off	不自动启用组复制集群
loose-group_replication_local_address	本地IP地址字符串，只能用于组内节点之间的连接，不能开放给上层应用
loose-group_replication_group_seeds	组中成员访问列表，此处配置的端口不是mysql服务端口
loose-group_replication_bootstrap_group=off	不启用引导组
loose-group_replication_member_weight	新主选举时，权重越大，优先级越高，默认50
loose-group_replication_single_primary_mode	TRUE(默认)表示mgr单主模式，FALSE表示多主模式
loose-group_replication_enforce_update_everywhere_checks	TRUE表示启用多主模式的一致性检查，FALSE表示禁用多主一致性检查

3.3.配置 mgr

[节点1]：配置组复制

#.定义节点信息
node1=192.168.31.101
sql_port=3406
mgr_port=34061
my_addr="\"$node1:$mgr_port"\"
my_weht=50
my_cnf=/data/mysql_$sql_port/my_$sql_port.cnf

#.修改配置，权重50
sed -i "s/^loose-group_replication_local_address.*/loose-group_replication_local_address=$my_addr/" $my_cnf
sed -i "s/^loose-group_replication_member_weight.*/loose-group_replication_member_weight=$my_weht/" $my_cnf

#.确认配置
cat $my_cnf | egrep "(^server|local_address|member_weight|group_seeds)"

#.重启mysql服务
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &

#.构建组复制集群信息（不建议添加 MASTER_PORT=3406,MASTER_AUTO_POSITION=1）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"CHANGE MASTER TO MASTER_USER='dba_repl', MASTER_PASSWORD='MA6RuouuZZn4x_Hd' FOR CHANNEL 'group_replication_recovery';"

#.安装组复制插件
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';"

#.设置 group_replication_bootstrap_group=ON 开启组复制引导，启动组复制之后，再关闭引导
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SET GLOBAL group_replication_bootstrap_group=ON;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"START GROUP_REPLICATION;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SET GLOBAL group_replication_bootstrap_group=OFF;"

#.确认 $mgr_port 端口已监听
netstat -lnpt | grep $mgr_port

#.查看mgr集群的节点信息
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SELECT * FROM performance_schema.replication_group_members;"

[节点2]：配置组复制

#.定义节点信息
node2=192.168.31.102
sql_port=3406
mgr_port=34061
my_addr="\"$node2:$mgr_port"\"
my_weht=40
my_cnf=/data/mysql_$sql_port/my_$sql_port.cnf

#.修改配置，权重40
sed -i "s/^loose-group_replication_local_address.*/loose-group_replication_local_address=$my_addr/" $my_cnf
sed -i "s/^loose-group_replication_member_weight.*/loose-group_replication_member_weight=$my_weht/" $my_cnf

#.确认配置
cat $my_cnf | egrep "(^server|local_address|member_weight|group_seeds)"

#.重启mysql服务
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &

#.构建组复制集群信息（不建议添加 MASTER_PORT=3406,MASTER_AUTO_POSITION=1）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"CHANGE MASTER TO MASTER_USER='dba_repl', MASTER_PASSWORD='MA6RuouuZZn4x_Hd' FOR CHANNEL 'group_replication_recovery';"

#.安装组复制插件
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';"

#.加入组复制（由于复制组已被创建，只需要将该节点添加进去即可）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"START GROUP_REPLICATION;"

#.确认 $mgr_port 端口已监听
netstat -lnpt | grep $mgr_port

#.查看mgr集群的节点信息
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SELECT * FROM performance_schema.replication_group_members;"

[节点3]：配置组复制

#.定义节点信息
node3=192.168.31.103
sql_port=3406
mgr_port=34061
my_addr="\"$node3:$mgr_port"\"
my_weht=30
my_cnf=/data/mysql_$sql_port/my_$sql_port.cnf

#.修改配置，权重30
sed -i "s/^loose-group_replication_local_address.*/loose-group_replication_local_address=$my_addr/" $my_cnf
sed -i "s/^loose-group_replication_member_weight.*/loose-group_replication_member_weight=$my_weht/" $my_cnf

#.确认配置
cat $my_cnf | egrep "(^server|local_address|member_weight|group_seeds)"

#.重启mysql服务
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &

#.构建组复制集群信息（不建议添加 MASTER_PORT=3406,MASTER_AUTO_POSITION=1）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"CHANGE MASTER TO MASTER_USER='dba_repl', MASTER_PASSWORD='MA6RuouuZZn4x_Hd' FOR CHANNEL 'group_replication_recovery';"

#.安装组复制插件
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';"

#.加入组复制（由于复制组已被创建，只需要将该节点添加进去即可）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"START GROUP_REPLICATION;"

#.确认 $mgr_port 端口已监听
netstat -lnpt | grep $mgr_port

#.查看mgr集群的节点信息
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"SELECT * FROM performance_schema.replication_group_members;"

4.确认与测试

[任一节点]：执行建库测试，验证数据同步

注：单主模式下，只有一个节点可以读写，其他节点提供只读服务

#.定义节点信息
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103
sql_port=3406

#.通过 read_only 区分主库或从库，OFF为主库，ON为从库
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"show variables where variable_name in ('read_only');"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"show variables where variable_name in ('read_only');"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"show variables where variable_name in ('read_only');"

#.主库创建db，从库确认是否创建成功
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"create database if not exists xxx_002;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"show databases;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"show databases;"

[任一节点]：查看mgr集群的相关信息

#.查看mgr集群的节点信息.针对mysql 5.7
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 17cdcd31-af04-11f0-84c3-000c291f8494 | 192.168.31.103 |        3406 | ONLINE       |
| group_replication_applier | 1e41fe7c-af04-11f0-84c2-000c29e1cb09 | 192.168.31.101 |        3406 | ONLINE       |
| group_replication_applier | eca8f592-af03-11f0-834b-000c29f317f7 | 192.168.31.102 |        3406 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+

#.查看mgr集群的节点信息.针对mysql 8.0
select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;

+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

#.查看当前主master
select b.member_host,b.member_port,b.member_state from performance_schema.global_status a,performance_schema.replication_group_members b \
where a.variable_value=b.member_id and a.variable_name='group_replication_primary_member';

+----------------+-------------+--------------+
| member_host    | member_port | member_state |
+----------------+-------------+--------------+
| 192.168.31.101 |        3406 | ONLINE       |
+----------------+-------------+--------------+

#.查看当前主master
select * from performance_schema.global_status where variable_name='group_replication_primary_member';

+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | eca8f592-af03-11f0-834b-000c29f317f7 |
+----------------------------------+--------------------------------------+

#.查看当前节点的组复制参数列表
show variables like 'group_replication%';
select * from performance_schema.global_variables where variable_name like 'group_replication%';

#.查看当前节点信息
show variables like 'group_replication_local_address';
select * from performance_schema.global_variables where variable_name='group_replication_local_address';

#.查看群组节点信息
show variables like 'group_replication_group_seeds';
select * from performance_schema.global_variables where variable_name='group_replication_group_seeds';

#.设置白名单
set global group_replication_ip_whitelist="192.168.0.0/16,127.0.0.1";
show variables like 'group_replication_ip_whitelist';

5.灾难演练

5.1.手动切换

第1步，[节点3]：查集群状态，当前主节点为节点1

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;

+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

第2步，[节点1]：主节点执行 STOP GROUP_REPLICATION; 会触发重新选举，节点2会选为新的主master（节点1/2/3的权重依次为50/40/30）

[root@mgr101 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> show variables where variable_name in ('read_only');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+

mysql> STOP GROUP_REPLICATION;

mysql> show variables where variable_name in ('read_only');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

第3步，[节点3]：查看集群状态，集群无宕机节点1、当前主库为节点2

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;

+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

第4步，[节点1]：执行 START GROUP_REPLICATION; 将宕机节点重新加入集群，不会触发选举

[root@mgr101 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> START GROUP_REPLICATION;

第5步，[节点3]：查看集群状态，所有节点online、当前主库仍为节点2

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;

+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

5.2.模拟主库宕机

第1步，[节点3]：查集群状态，当前主节点为节点2

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;

+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

第2步，[节点2]：停掉宕机主节点的mysql服务，模拟主节点宕机，此时会触发重新选举

[root@mgr102 ~]# mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown

第3步，[节点3]：查看集群状态，集群无宕机节点2、当前主库为节点1（节点1/2/3的权重依次为50/40/30）

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;

+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

第4步，[节点2]：启动该节点的 mysql 服务，由于配置了“不自动启用组复制”，所以此时主节点仍为节点1，集群状态同第3步一样（省略...）

[root@mgr102 ~]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &

[root@mgr102 ~]# cat /data/mysql_3406/my_3406.cnf | egrep "(start_on_boot|bootstrap_group)"
loose-group_replication_start_on_boot=off
loose-group_replication_bootstrap_group=off

第5步，[节点2]：执行 START GROUP_REPLICATION; 将宕机节点重新加入集群，不会触发选举

[root@mgr102 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> START GROUP_REPLICATION;
mysql> exit

第6步，[节点3]：查看集群状态，所有节点online、当前主库仍为节点1

[root@mgr103 ~]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;

+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

6.附录

6.1.遇到的问题

问题1：在主节点1执行 CHANGE MASTER 报错 ERROR 3139 (HY000): CHANGE MASTER with the given parameters cannot be performed on channel 'group_replication_recovery'.。将 group_replication_recovery 改为自定义的 channel_mgr，重试ok。

补：若执行 CHANGE MASTER ... FOR CHANNEL 'group_replication_recovery'; 不添加 MASTER_PORT=3406,MASTER_AUTO_POSITION=1，再执行 START GROUP_REPLICATION; 不添加 USER='****',PASSWORD='****'，亦可解决

#.本实例 mysql 8.0.32
mysql> CHANGE MASTER TO MASTER_PORT=3406, MASTER_USER='dba_repl', MASTER_PASSWORD='****' FOR CHANNEL 'group_replication_recovery';
ERROR 3139 (HY000): CHANGE MASTER with the given parameters cannot be performed on channel 'group_replication_recovery'.

#.从 mysql 8.0.23 开始，将 CHANGE MASTER 替换为 CHANGE REPLICATION SOURCE
mysql> CHANGE REPLICATION SOURCE TO SOURCE_PORT=3406, SOURCE_USER='dba_repl', SOURCE_PASSWORD='****' FOR CHANNEL 'group_replication_recovery';
ERROR 3139 (HY000): CHANGE MASTER with the given parameters cannot be performed on channel 'group_replication_recovery'.

#.方案1：测试ok
mysql> CHANGE MASTER TO MASTER_PORT=3406, MASTER_USER='dba_repl', MASTER_PASSWORD='****' FOR CHANNEL 'channel_mgr';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

#.方案2：测试ok
mysql> CHANGE MASTER TO MASTER_USER='dba_repl', MASTER_PASSWORD='****' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

问题2：在主节点1执行 START GROUP_REPLICATION; 报错，然后错误日志报错 ERROR group_replication Unable to announce tcp port 3406. Port already in use。修改所有节点的配置文件，将 local_address 及 group_seeds 中的 ip地址:3406 改为 ip地址:34061，然后重启mysql服务，重试ok。

#.主库.启动组复制
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

#.主库.查看错误日志
[root@mgr101 ~]# more /data/mysql_3406/error/mysql_run.err
ERROR group_replication Unable to announce tcp port 3406. Port already in use

#.所有节点.将 local_address 及 group_seeds 中的 3406 端口改掉,示例改为 34061
[root@mgr101 ~]# cat /data/mysql_3406/my_3406.cnf | egrep "(local_address|group_seeds)"              
loose-group_replication_local_address="192.168.31.101:34061"
loose-group_replication_group_seeds="192.168.31.101:34061,192.168.31.102:34061,192.168.31.103:34061"

#.所有节点.重启mysql服务并重试

问题3：在节点2执行 START GROUP_REPLICATION; 报错，然后错误日志报错 Local transactions: xxx:1-9 > Group transactions: xxx:1-2。对主库进行备份并恢复到所有从库（请参考第3.1.章），重试ok。

#.从库.启动组复制
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

#.从库.查看错误日志
[root@mgr102 ~]# more /data/mysql_3406/error/mysql_run.err
2025-10-21T18:57:41.323641+08:00 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: b6a45572-ae64-11f0-ba66-000c29f317f7:1-9 > Group transactions: 7d4fbec2-ae64-11f0-bbec-000c29e1cb09:1-9, ce9be252-2b71-11e6-b8f4-00212844f856:1-2'

#.备份及恢复，请参考 第3.1.章

问题4：在节点2执行 START GROUP_REPLICATION; 无报错，但是一直状态为 RECOVERING，然后错误日志报错 [ERROR] [MY-013117] [Repl] Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server。调整为 START GROUP_REPLICATION USER='dba_repl', PASSWORD='MA6RuouuZZn4x_Hd';，重试ok。

补：若执行 CHANGE MASTER ... FOR CHANNEL 'group_replication_recovery'; 不添加 MASTER_PORT=3406,MASTER_AUTO_POSITION=1，再执行 START GROUP_REPLICATION; 不添加 USER='****',PASSWORD='****'，亦可解决

#.从库.启动组复制
mysql> START GROUP_REPLICATION;

mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.102 |        3406 | RECOVERING   | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

#.从库.查看错误日志
[root@mgr102 ~]# more /data/mysql_3406/error/mysql_run.err
2025-10-22T06:50:41.681199+08:00 78 [ERROR] [MY-013117] [Repl] Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: MY-013117

#.从库.启动组复制
mysql> STOP GROUP_REPLICATION;
mysql> START GROUP_REPLICATION USER='dba_repl', PASSWORD='MA6RuouuZZn4x_Hd';

mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | SECONDARY   | 8.0.32         |
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

6.2.如何启用多主模式

单主模式、多主模式的区别：

对比项	单主模式	多主模式
读写模式	只有一个节点可以读写，其他节点只读	所有节点都设置为读写模式
隔离级别	-	不支持SERIALIZABLE隔离级别，且不能完全支持级联外键约束
参数配置	loose-group_replication_single_primary_mode=on loose-group_replication_enforce_update_everywhere_checks=off	loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on

场景1.在创建时就启用多主模式：

在创建时就启用多主模式，只需在修改配置文件 /etc/my.cnf 时修改如下两个参数，其余保持不变

loose-group_replication_single_primary_mode=off　　             //关闭单master模式
loose-group_replication_enforce_update_everywhere_checks=on     //多主一致性检查

场景2.由单主模式改为多主模式：

[所有节点]：由单主改为多主时，修改所有节点的两个参数

#.修改配置
my_cnf=/data/mysql_3406/my_3406.cnf 
sed -i "s/^loose-group_replication_single_primary_mode.*/loose-group_replication_single_primary_mode=OFF/" $my_cnf
sed -i 's/^loose-group_replication_enforce_update_everywhere_checks.*/loose-group_replication_enforce_update_everywhere_checks=ON/' $my_cnf

#.确认配置
cat $my_cnf | grep "^loose-group" | egrep "(single_primary_mode|everywhere_checks)"


#.示例如下
[root@mgr101 ~]# cat $my_cnf | grep "^loose-group" | egrep "(single_primary_mode|everywhere_checks)"
loose-group_replication_single_primary_mode=OFF                 //关闭单master模式
loose-group_replication_enforce_update_everywhere_checks=ON     //开启多主一致性检查

[所有节点]：停止组复制集群，并设置多主模式的2个参数

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

mysql> select CHANNEL_NAME,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSION from performance_schema.replication_group_members;
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 192.168.31.101 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
| group_replication_applier | 192.168.31.103 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
| group_replication_applier | 192.168.31.102 |        3406 | ONLINE       | PRIMARY     | 8.0.32         |
+---------------------------+----------------+-------------+--------------+-------------+----------------+

[任一节点]：执行建库测试，验证数据同步

注：多主模式下，所有节点都可以读写

#.定义节点信息
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103
sql_port=3406

#.执行建库测试
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"create database if not exists xxx_101;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"create database if not exists xxx_102;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"create database if not exists xxx_103;"

#.验证数据同步
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"show databases;"

6.3.多主环境的重启顺序

针对3个节点的 MGR-多主环境（节点1/2/3均为主库），启停顺序如下：

#.1.停止所有节点（无所谓顺序）
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"stop group_replication;"
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown

#.2.任一节点，引导组复制
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> set @@global.group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set @@global.group_replication_bootstrap_group=off;
mysql> exit

#.3.剩余节点，加入组复制
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"start group_replication;"

#.4.任意节点：确认节点信息
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;"

6.4.单主环境的重启顺序

针对一主二从的 MGR-单主环境（假设节点1为主库、节点2/3为从库），启停顺序如下：

#.1.先停止从库2、3
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"stop group_replication;"
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown

#.2.再停止主库1
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"stop group_replication;"
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown

#.3.先启动主库1，引导组复制
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock
mysql> set @@global.group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set @@global.group_replication_bootstrap_group=off;
mysql> exit

#.4.再启动从库2、3，加入组复制
/opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3406/my_3406.cnf &
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"start group_replication;"

#.5.任意节点：确认节点信息
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members;"

6.5.如何配置mgr连接字符串

MySQL MGR 的连接字符串配置与普通MySQL连接略有不同，主要考虑MGR的多主或单主模式以及故障转移的需求。

注：核心参数 autoReconnect=true、failOverReadOnly=false、loadBalanceStrategy=bestResponseTime

#.JDBC字符串,注意以下核心参数
String url = "jdbc:mysql://node1:3306,node2:3306,node3:3306/mydb?" +
    "useSSL=false&" +
    "allowPublicKeyRetrieval=true&" +
    "useAffectedRows=true&" +
    "serverTimezone=UTC&" +
    "autoReconnect=true&" +
    "failOverReadOnly=false&" +
    "maxReconnects=10&" +
    "socketTimeout=60000&" +
    "connectTimeout=10000&" +
    "loadBalanceStrategy=bestResponseTime";
String username = "yourusername";
String password = "yourpassword";
try (Connection conn = DriverManager.getConnection(url, username, password);

#.HikariCP配置连接池,注意以下核心参数
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://node1:3306,node2:3306,node3:3306/mydb?useSSL=false&allowPublicKeyRetrieval=true&useAffectedRows=true&serverTimezone=UTC&autoReconnect=true&failOverReadOnly=false&loadBalanceStrategy=bestResponseTime");
config.setUsername("yourusername");
config.setPassword("yourpassword");
config.setMaximumPoolSize(50);  // 最大连接数
config.setMinimumIdle(10);      // 最小空闲连接数
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
HikariDataSource ds = new HikariDataSource(config);

在“直连”模式下，驱动本身不会主动探测或区分主库和从库，而是在执行数据库操作之前，显示地通过 setReadonly(true/false) 来声明这个事务是只读或读写。如果声明 setReadonly(true) 驱动就知道这是个查询操作，会从根据 loadBalanceStrategy 策略选择一个从库来执行；如果声明 setReadonly(false) 或默认驱动就知道这个是写操作，会固定将请求发送给当前的主库。

注1：在建立连接初始化的时候，驱动会连接到串中列出的任意一个节点获取整个集群的节点信息，这样就知道知道当前谁是Primary/Secondary节点。

注2：loadBalanceStrategy 不是 MGR 的服务端配置，而是 MySQL 连接器在处理多主机连接时的一项设置，比如 bestResponseTime 会将请求路由到之前响应时间最短的主机上。

6.6.参考文章

参考：MySQL 5.7 Group Replication
参考：MySQL 8.0 组复制

mgr搭建部署