环境介绍
| 操作系统 | IP | 主机名 | MySQL版本 |
| Windows Server 2016 | 192.168.1.91 | db1 | 8.0.31 |
| Windows Server 2016 | 192.168.1.92 | db2 | 8.0.31 |
| Windows Server 2016 | 192.168.1.93 | db3 | 8.0.31 |
关闭Windows Server 2016防火墙
配置C:\Windows\System32\drivers\etc\hosts添加如下信息：
192.168.1.91 db1
192.168.1.92 db2
192.168.1.93 db3安装MySQL Group Replication
1. Windows Server 2016安装MySQL Server 8.0.31和MySQL Shell 8.0.31参考我另一篇博客：https://www.cnblogs.com/haha029/p/16855562.html
2. 调整my.ini
[mysqld]
character_set_server = utf8mb4
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED
max-connections=300
# BIN LOG
sync_binlog=1
log-bin=log-bin
binlog_format=row
binlog_row_image=full
log_bin_trust_function_creators=1
binlog_expire_logs_seconds=604800
# ERROR LOG
log_error=mysql.err
# SLOW LOG
slow_query_log=on
slow_query_log_file=mysql_slow.log
long_query_time=2
#min_examined_row_limit = 100
#log_queries_not_using_indexes = on
#log_throttle_queries_not_using_indexes = 4
log_slow_admin_statements=on
log_output=file
log_slow_slave_statements=on
# Replication Base
server_id = 93 #每个mysql服务器不同
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository = table
relay_log_info_repository = table
binlog_checksum = none
log_slave_updates = on
# MySQL Group Replication
loose-plugin_load_add='mysql_clone.dll'
loose-plugin_load_add='group_replication.dll'
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="windows-server-2016-mgr"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.1.91:33061" #当前服务器“IP:port”
loose-group_replication_group_seeds="192.168.1.91:33061,192.168.1.92:33061,192.168.1.93:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.1.91,192.168.1.92,192.168.1.93" #如果在同一局域网可以不用配置
# 并行复制
slave_parallel_workers=16
slave_parallel_type=LOGICAL_CLOCK
binlog_transaction_dependency_tracking=WRITESET
binlog_transaction_dependency_history_size=25000
transaction_write_set_extraction=XXHASH64
slave_pending_jobs_size_max=0
slave_preserve_commit_order=03. 所有节点通过MySQL Shell配置MGR实例
C:\Users\Administrator>mysqlsh --uri root@localhost:3306 #MySQL Shell连接mysql
Please provide the password for 'root@localhost:3306': ********* #输入密码
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Yes #保存密码
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 10
Server version: 8.0.31 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3306 ssl JS > dba.configureInstance() #配置实例
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as db1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
ERROR: The account 'root'@'%' is missing privileges required to manage an InnoDB cluster:
GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'root'@'%' WITH GRANT OPTION;
1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel
Please select an option [1]: 2 #为Innodb Cluster创建新实例
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: mgr #指定用户名
Password for new account: ********* #指定密码
Confirm password: ********* #指定密码
applierWorkerThreads will be set to the default value of 4.
NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable | Current Value | Required Value | Note |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+
Do you want to perform the required configuration changes? [y/n]: y #更改配置
Cluster admin user 'mgr'@'%' created.
Configuring instance...
The instance 'db1:3306' was configured to be used in an InnoDB cluster.4. db1节点通过MySQL Shell配置初始化集群
C:\Users\Administrator>mysqlsh --uri root@localhost:3306 #连接
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 19
Server version: 8.0.31 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3306 ssl JS > dba.createCluster('mgr_cluster'); #创建MGR集群
A new InnoDB Cluster will be created on instance 'db1:3306'.
Validating instance configuration at localhost:3306...
This instance reports its own address as db1:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db1:3306'. Use the localAddress option to override.
Creating InnoDB Cluster 'mgr_cluster' on 'db1:3306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.5. 通过MySQL Shell添加SECONDARY节点
查看已创建的集群
MySQL localhost:3306 ssl JS > dba.getCluster()
<Cluster:mgr_cluster>6. 添加mgr@192.168.1.92:3306到集群中
MySQL localhost:3306 ssl JS > cluster=dba.getCluster() #配置cluster变量
<Cluster:mgr_cluster>
MySQL localhost:3306 ssl JS > cluster.addInstance('mgr@192.168.1.92:3306'); #添加mgr@192.168.1.92:3306到mgr_cluster集群中
WARNING: A GTID set check of the MySQL instance at 'db2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
db2:3306 has the following errant GTIDs that do not exist in the cluster:
4a6bccf4-5be3-11ed-8192-000c29cb9345:1-5
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of db2:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
Please select a recovery method [C]lone/[A]bort (default Abort): C #选择使用Clone复制方法
Validating instance configuration at 192.168.1.92:3306...
This instance reports its own address as db2:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db2:3306'. Use the localAddress option to override.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: db2:3306 is being cloned from db1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: db2:3306 is shutting down...
* Waiting for server restart... ready
* db2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 100.08 MB transferred in 6 sec (16.68 MB/s)
State recovery already finished for 'db2:3306'
The instance 'db2:3306' was successfully added to the cluster.7. 添加mgr@192.168.1.93:3306到集群中
MySQL localhost:3306 ssl JS > cluster.addInstance('mgr@192.168.1.93:3306');
WARNING: A GTID set check of the MySQL instance at 'db3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
db3:3306 has the following errant GTIDs that do not exist in the cluster:
ba6cf007-5bee-11ed-b753-000c2935601c:1-5
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of db3:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.
Please select a recovery method [C]lone/[A]bort (default Abort): Clone
Validating instance configuration at 192.168.1.93:3306...
This instance reports its own address as db3:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db3:3306'. Use the localAddress option to override.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: db3:3306 is being cloned from db2:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: db3:3306 is shutting down...
* Waiting for server restart... timeout
WARNING: Clone process appears to have finished and tried to restart the MySQL server, but it has not yet started back up.
Please make sure the MySQL server at 'db3:3306' is restarted and call <Cluster>.rescan() to complete the process. To increase the timeout, change shell.options["dba.restartWaitTimeout"].
Cluster.addInstance: Timeout waiting for server to restart (MYSQLSH 51156)8. 查看MGR集群信息，默认安装为“单主模式”（参数group_replication_single_primary_mode=ON）
MySQL localhost:3306 ssl JS > \sql select * from performance_schema.replication_group_members;
Fetching global names for auto-completion... Press ^C to stop.
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+故障切换
查看集群信息
MySQL localhost:3306 ssl JS > \sql select * from performance_schema.replication_group_members;
Fetching global names for auto-completion... Press ^C to stop.
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+模拟故障，手动关闭db1（192.168.1.91）端MySQL80服务
C:\Users\Administrator>net stop MySQL80在db3（192.168.1.93）端查看集群信息，此时看不到db1（192.168.1.91）的信息
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 | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+启动db1（192.168.1.91）端MySQL80服务
C:\Users\Administrator>net start MySQL80再次到db3（192.168.1.93）端查看集群信息，发现db1（192.168.1.91）端以SECONDARY角色加入到了集群中
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 | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+“单主”转换“多主”
查看集群信息，db2是PRIMARY，db1和db3为SECONDARY
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 | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+查看“单主模式”参数
mysql> show global variables like 'group_replication_single%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON |
+---------------------------------------+-------+从“单主”模式转换到“多主”模式
mysql> select group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| The group is already on multi-primary mode. |
+--------------------------------------------------+再次查看“单主模式”参数为0（OFF）
mysql> show global variables like 'group_replication_single%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | OFF |
+---------------------------------------+-------+“多主”转换“单主”
查看“单主模式”参数，此时参数为0（OFF）
mysql> select @@global.group_replication_single_primary_mode;
+------------------------------------------------+
| @@global.group_replication_single_primary_mode |
+------------------------------------------------+
| 0 |
+------------------------------------------------+查看MGR集群相关信息
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 | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+将“多主”转换为“单主”模式，指定要转为PRIMARY角色的MERBER_ID
mysql> select group_replication_switch_to_single_primary_mode('828c50b9-5b70-11ed-b440-000c29a4d8a7');
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('828c50b9-5b70-11ed-b440-000c29a4d8a7') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully. |
+-----------------------------------------------------------------------------------------+再次查看MGR集群信息
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 | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+再次查看“单主模式”参数为1（ON）
mysql> show global variables like 'group_replication_single%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | 1 |
+---------------------------------------+-------+MGR增删节点
将db3（192.168.1.93）节点从MGR集群中删除
C:\Users\Administrator>mysqlsh --uri mgr@localhost:3306
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'mgr@localhost:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 50
Server version: 8.0.31 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3306 ssl JS > cluster=dba.getCluster()
<Cluster:mgr_cluster>
MySQL localhost:3306 ssl JS > cluster.removeInstance('mgr@192.168.1.93:3306');
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.
* Waiting for instance 'db3:3306' to synchronize with the primary...
** Transactions replicated ############################################################ 100%
* Instance '192.168.1.93:3306' is attempting to leave the cluster...
The instance '192.168.1.93:3306' was successfully removed from the cluster.查看集群信息
MySQL localhost:3306 ssl JS > \sql select * from performance_schema.replication_group_members;
Fetching global names for auto-completion... Press ^C to stop.
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
db3（192.168.1.93）节点添加到MGR集群中
C:\Users\Administrator>mysqlsh --uri mgr@localhost:3306
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'mgr@localhost:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 58
Server version: 8.0.31 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3306 ssl JS > cluster=dba.getCluster()
<Cluster:mgr_cluster>
MySQL localhost:3306 ssl JS > cluster.addInstance('mgr@192.168.1.93:3306');
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'db3:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Incremental state recovery was selected because it seems to be safely usable.
Validating instance configuration at 192.168.1.93:3306...
This instance reports its own address as db3:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db3:3306'. Use the localAddress option to override.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
State recovery already finished for 'db3:3306'
The instance 'db3:3306' was successfully added to the cluster.查看MGR集群信息
MySQL localhost:3306 ssl JS > \sql select * from performance_schema.replication_group_members;
Fetching global names for auto-completion... Press ^C to stop.
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 4a6bccf4-5be3-11ed-8192-000c29cb9345 | db2 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
| group_replication_applier | 828c50b9-5b70-11ed-b440-000c29a4d8a7 | db1 | 3306 | ONLINE | PRIMARY | 8.0.31 | MySQL |
| group_replication_applier | ba6cf007-5bee-11ed-b753-000c2935601c | db3 | 3306 | ONLINE | SECONDARY | 8.0.31 | MySQL |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
