mysql mgr集群部署 单主模式和多主模式
MGR介绍
MySQL Group Replication(MGR:mysql组复制技术)是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案,以插件形式提供。 **MGR基于分布式paxos协议,实现组复制,在分布式中保证数据一致性和原子性,且具有容错率的一致性算法。**内置故障检测和自动选主功能,只要不是集群中的大多数节点都宕机,就可以继续正常工作。 提供单主模式与多主模式,多主模式支持多点写入。 MySQL Group Replication(MGR)是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案,以插件形式提供,实现了分布式下数据的最终一致性,总结MGR特点如下: 高一致性:基于原生复制以及分布式paxos协议实现组复制,并已插件的方式提供,保证数据一致性(paxos协议:解决多节点写入的问题); 高容错性:自动检测机制,只要不是大多数节点都宕机就可以继续工作,有自动检测机制,当不同节点产生资源竞争时,不会出现错误,按照先到先优先的原则进行处理,内置自动化防脑裂保护机制; 高扩展性:节点的增加与移除会自动更新组成员信息,新节点加入后,自动从其他节点同步增量数据,直到与其他节点数据一致,如果某个节点被移除了,其他节点会自动更新组信息,自动维护新的组信息。 高灵活性:提供单主模式和多主模式,单主模式在主库宕机后能够自动选主,所有写入都在主节点进行,多主模式支持多节点写入。
MGR使用限制
限制一:仅支持innodb存储引擎
MGR集群中,只支持innodb存储引擎,能够创建非innodb引擎的表,但是无法写入数据,向非innodb表写数据直接报错。因为事物支持在commit时对各节点进行冲突检查 要求: 节点必须开启binlog且为row模式 必须开启gtid 且主从状态信息存于表中 --master-info-repository=TABLE --realy-log-info-repository=TABLE --log-slave-updates打开,级联更新) 一致性检测设置 --transaction-write-extraction=XXHASH64
限制二:表必须有主见或者非NULL的唯一值
MGR集群中,只支持innodb引擎的表,并且该表必须有显式的主键,或者非Null的唯一键,否则即使能够创建表,也无法向表中写入数据。
限制三:网络限制
MGR 组通信引擎目前仅支持IPv4网络,并且对节点间的网络性能要求较高,低延迟、高带宽的网络是部署MGR集群的基础。
限制四:MGR忽略表锁和命名锁
在MGR中lock tables、unlock tables、get_lock、release_lock等这些表锁和命名锁将被忽略。
其他限制
MGR使用限制: 1.RP和普通复制binlog校验不能共存,需设置–binlog-checksum=none; 2.不支持gap lock(间隙锁),隔离级别需设置为read committed; 3.MGR多主模式中,默认不支持 SERIALIZABLE 隔离级别。 4.多主模式下,对同一个对象进行并发的有冲突的ddl和dml操作导致这种冲突在部分成员节点中无法检测到,最终可能导致数据不一致。 5.多主模式下,不支持级联约束的外键,可能造成有冲突的操作无法检测。 6.不支持超大事务。 7.多主模式下可能导致死锁,比如select …for update在不同节点执行,由于多节点锁无法共享,很容易导致死锁。 8.不支持复制过滤,如果有节点设置了复制过滤,将影响节点间决议的达成。 9.MGR最多支持9个节点,大于9个节点,将拒绝新节点的加入。
ip地址 | 主机名 | 数据库 | 端口号 | server Id |
---|---|---|---|---|
192.168.3.86(主) | zyj86 | mysql-server-8.0.36 | 3306 | 86 |
192.168.3.87(从) | zyj87 | mysql-server-8.0.36 | 3306 | 87 |
192.168.3.88(从) | zyj88 | mysql-server-8.0.36 | 3306 | 88 |
主从模式
环境准备
修改主机名称和ip地址映射关系
hostnamectl set-hostname zyj86 && bash
hostnamectl set-hostname zyj87 && bash
hostnamectl set-hostname zyj88 && bash
#追加hostname和ip映射关系
cat <<EOF >> /etc/hosts
192.168.3.86 zyj86
192.168.3.87 zyj87
192.168.3.88 zyj88
EOF
安装mysql服务
dnf module install mysql -y
systemctl enable --now mysqld
mysqladmin -u root password "aaa...111"
主节点设置
修改zyj86配置文件
vim /etc/my.cnf.d/mysql-server.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id = 86 #服务ID
gtid_mode = ON #全局事务
enforce_gtid_consistency = ON #强制GTID的一致性
master_info_repository = TABLE #将master.info元数据保存在系统表中
relay_log_info_repository = TABLE #将relay.info元数据保存在系统表中
binlog_checksum = NONE #禁用二进制日志事件校验
log_slave_updates = ON #级联复制
log_bin = binlog #开启二进制日志记录
binlog_format= ROW #以行的格式记录
plugin_load_add='group_replication.so' #加载组复制插件
transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
group_replication_group_name = '2f424e1a-6c28-11ef-8d31-000c29298301' #加入的组名
group_replication_start_on_boot = off #不自动启用组复制集群
group_replication_local_address = 'zyj86:33061' #以本机端口33061接受来自组中成员的传入连接
group_replication_group_seeds ='zyj86:33061,zyj87:33061,zyj88:33061' #组中成员访问表
group_replication_bootstrap_group = off #不启用引导组
重启mysql服务
systemctl restart mysqld
安装插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
mysql配置文件中指定了 plugin_load_add=‘group_replication.so’ 也会安装上的
mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
。。。。。。。。。。。
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so 可以看到此行说明插件安装好了
zyj86主机上创建账号及授权
mysql> set SQL_LOG_BIN=0; #停掉日志记录
mysql> CREATE USER repl@'%' IDENTIFIED BY '123456'; #创建用户
mysql> GRANT REPLICATION SLAVE ON *.* to repl@'%'; #授权从库复制
mysql> GRANT CONNECTION_ADMIN ON *.* TO repl@'%'; #它确保在组复制中处于脱机模式时不会终止组复制连接
mysql> GRANT BACKUP_ADMIN ON *.* TO repl@'%'; #如果此服务器在组复制中设置成了支持克隆,那么在分布式恢复的克隆操作中充当授权人角色
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO repl@'%'; #组复制建立和维护连接的授权
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1; #开启日志记录
mysql> change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery'; #构建group replication集群
启动zyj86主机上mysql的group replication(组复制)
#设置group_replication_bootstrap_group为ON是表示这台是master,以后加入的是slave,以后加入的不需要设置。引导只能由一台服务器完成。
mysql> set global group_replication_bootstrap_group=ON;
#作为首个节点启动mgr集群
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
#启动成功后,这台服务器已经是master了。有了master后,引导器就没有用了,就可以关闭了。
查看mgr的状态
#查询表performance_schema.replication_group_members
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | c9a758d3-6c26-11ef-82b9-000c29298301 | zyj86 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
测试zyj86主机上的mysql
mysql> create database testsql;
Query OK, 1 row affected (0.01 sec)
mysql> use testsql;
Database changed
mysql> create table t1 (id int primary key,name varchar(20)); #注意创建主键
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,'test1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | test1 |
+----+------+
1 row in set (0.00 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 486 | No |
| binlog.000002 | 1160 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.36, Binlog ver: 4 |
| binlog.000001 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000001 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 236 | Query | 1 | 463 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*EF0036DB88787680886352FD7982D31F99BBFAC4' /* xid=3 */ |
| binlog.000001 | 463 | Stop | 1 | 486 | |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
添加第一个从节点
复制组添加新主机zyj87(从库)
修改mysql配置文件
vim /etc/my.cnf.d/mysql-server.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id = 87 #服务ID
gtid_mode = ON #全局事务
enforce_gtid_consistency = ON #强制GTID 的一致性
master_info_repository = TABLE #将master.info元数据保存在系统表中
relay_log_info_repository = TABLE #将relay.info元数据保存在系统表中
binlog_checksum = NONE #禁用二进制日志事件校验
log_slave_updates = ON #级联复制
log_bin = binlog #开启二进制日志记录
binlog_format= ROW #以行的格式记录
plugin_load_add='group_replication.so' #加载组复制插件
transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
group_replication_group_name = '2f424e1a-6c28-11ef-8d31-000c29298301' #加入的组名
group_replication_start_on_boot = off #不自动启用组复制集群
group_replication_local_address = 'zyj87:33061' #以本机端口33061接受来自组中成员的传入连接
group_replication_group_seeds ='zyj86:33061,zyj87:33061,zyj88:33061' #组中成员访问表
group_replication_bootstrap_group = off #不启用引导组
重启mysql服务
systemctl restart mysqld
确认已经安装插件
mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
。。。。。。。。。。。
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so 可以看到此行说明插件安装好了
zyj87主机上创建账号及授权
mysql> set SQL_LOG_BIN=0; #停掉日志记录
mysql> CREATE USER repl@'%' IDENTIFIED BY '123456'; #创建用户
mysql> GRANT REPLICATION SLAVE ON *.* to repl@'%'; #授权从库复制
mysql> GRANT CONNECTION_ADMIN ON *.* TO repl@'%'; #它确保在组复制中处于脱机模式时不会终止组复制连接
mysql> GRANT BACKUP_ADMIN ON *.* TO repl@'%'; #如果此服务器在组复制中设置成了支持克隆,那么在分布式恢复的克隆操作中充当授权人角色
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO repl@'%'; #组复制建立和维护连接的授权
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1; #开启日志记录
mysql> change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery'; #构建group replication集群
把主机添到之前的复制组中
mysql> start group_replication;
Query OK, 0 rows affected (1.92 sec)
在zyj86主机上查看复制组状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a218869d-6c32-11ef-aa91-000c29298301 | zyj86 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | a39d9bb0-6c32-11ef-aaf6-000c29257029 | zyj87 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.01 sec)
在新的主机( zyj87 )上查看数据库发现testsql库和t1表已经同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testsql |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testsql
mysql> show tables;
+-------------------+
| Tables_in_testsql |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
+----+-------+
1 row in set (0.00 sec)
添加第二个从节点
同理添加zyj88主机
修改mysql配置文件
vim /etc/my.cnf.d/mysql-server.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id = 88 #服务ID
gtid_mode = ON #全局事务
enforce_gtid_consistency = ON #强制GTID 的一致性
master_info_repository = TABLE #将master.info元数据保存在系统表中
relay_log_info_repository = TABLE #将relay.info元数据保存在系统表中
binlog_checksum = NONE #禁用二进制日志事件校验
log_slave_updates = ON #级联复制
log_bin = binlog #开启二进制日志记录
binlog_format= ROW #以行的格式记录
plugin_load_add='group_replication.so' #加载组复制插件
transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
group_replication_group_name = '2f424e1a-6c28-11ef-8d31-000c29298301' #加入的组名
group_replication_start_on_boot = off #不自动启用组复制集群
group_replication_local_address = 'zyj88:33061' #以本机端口33061接受来自组中成员的传入连接
group_replication_group_seeds ='zyj86:33061,zyj87:33061,zyj88:33061' #组中成员访问表
group_replication_bootstrap_group = off #不启用引导组
重启mysql服务
systemctl restart mysqld
确认已经安装插件
mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
。。。。。。。。
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so 可以看到此行说明插件安装好了
zyj88主机上创建账号及授权
mysql> set SQL_LOG_BIN=0; #停掉日志记录
mysql> CREATE USER repl@'%' IDENTIFIED BY '123456'; #创建用户
mysql> GRANT REPLICATION SLAVE ON *.* to repl@'%'; #授权从库复制
mysql> GRANT CONNECTION_ADMIN ON *.* TO repl@'%'; #它确保在组复制中处于脱机模式时不会终止组复制连接
mysql> GRANT BACKUP_ADMIN ON *.* TO repl@'%'; #如果此服务器在组复制中设置成了支持克隆,那么在分布式恢复的克隆操作中充当授权人角色
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO repl@'%'; #组复制建立和维护连接的授权
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1; #开启日志记录
mysql> change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery'; #构建group replication集群
把主机添到之前的复制组中
mysql> start group_replication;
Query OK, 0 rows affected (1.92 sec)
在zyj86主机上查看复制组状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a218869d-6c32-11ef-aa91-000c29298301 | zyj86 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | a3941bf1-6c32-11ef-a94f-000c29c7eaf1 | zyj88 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
| group_replication_applier | a39d9bb0-6c32-11ef-aaf6-000c29257029 | zyj87 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
在新的主机( zyj88 )上查看数据库发现testsql库和t1表已经同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testsql |
+--------------------+
5 rows in set (0.00 sec)
mysql> use testsql
mysql> show tables;
+-------------------+
| Tables_in_testsql |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
+----+-------+
1 row in set (0.00 sec)
至此一主两从mgr集群搭建完成
查看集群参数设置列表
mysql> show variables like 'group_replication%';
+-----------------------------------------------------+--------------------------------------+
| Variable_name | Value |
+-----------------------------------------------------+--------------------------------------+
。。。。。。。
| group_replication_recovery_zstd_compression_level | 3 |
| group_replication_single_primary_mode | ON
单主模式
|
| group_replication_ssl_mode | DISABLED |
| group_replication_start_on_boot | OFF |
。。。。。。
+-----------------------------------------------------+--------------------------------------+
61 rows in set (0.03 sec)
查看master是否可读写
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF | #可读可写
| super_read_only | OFF |
| transaction_read_only | OFF |
+-----------------------+-------+
4 rows in set (0.00 sec)
| read_only | OFF | #可读可写
查看另外两台主机(zyj87 88)
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON | #只读
| super_read_only | ON | #只读
| transaction_read_only | OFF |
+-----------------------+-------+
4 rows in set (0.07 sec)
| read_only | ON | #只读
| super_read_only | ON | #只读
多主模式
使用多主模式只需要在单主模式上执行切换模式即可
1、该模式启用需设置两个参数
#这个参数很好理解,就是关闭单master模式
group_replication_single_primary_mode=0;
#这个参数设置多主模式下各个节点严格一致性检查
group_replication_enforce_update_everywhere_checks=1;
2、 默认启动的都是单master模式,其他节点都设置了read_only、super_read_only这两个参数,需要修改这两个配置
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;
而对于其他的从节点,执行下面的操作
stop GROUP_REPLICATION;
set global group_replication_single_primary_mode=off;
set global group_replication_enforce_update_everywhere_checks=ON;
start group_replication;
查看是否可读写
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
+-----------------------+-------+
4 rows in set (0.00 sec)
在zyj86主机上查看复制组状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a218869d-6c32-11ef-aa91-000c29298301 | zyj86 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | a3941bf1-6c32-11ef-a94f-000c29c7eaf1 | zyj88 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | a39d9bb0-6c32-11ef-aaf6-000c29257029 | zyj87 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
zyj86 zyj87 zyj88 MEMBER_ROLE 均为 PRIMARY 均为主节点,成功切换为多主模式
测试写入数据
mysql> insert into testsql.t1 values (2,'test2');
Query OK, 1 row affected (0.01 sec)
mysql> select * from testsql.t1;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
切换回单主模式(原来的主库执行)
随机单主节点
select group_replication_switch_to_single_primary_mode();
指定单主切换
因为随机切有很大的不确定性,指定主节点的切换命令
select group_replication_set_as_primary("member_id");
这里的 member_id查看方法
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a218869d-6c32-11ef-aa91-000c29298301 | zyj86 |
MEMBER_ID 为:a218869d-6c32-11ef-aa91-000c29298301
这里我指定MEMBER_ID是 a218869d-6c32-11ef-aa91-000c29298301 为主节点
select group_replication_set_as_primary("a218869d-6c32-11ef-aa91-000c29298301");
在zyj86主机上查看复制组状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | a218869d-6c32-11ef-aa91-000c29298301 | zyj86 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | a3941bf1-6c32-11ef-a94f-000c29c7eaf1 | zyj88 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
| group_replication_applier | a39d9bb0-6c32-11ef-aaf6-000c29257029 | zyj87 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
已切换为单主