MySQL InnoDB Cluster

注意：

此文档对应的 MySQL 版本为 8.0.32 社区版。

对于 MySQL 的高可用解决方案有：

• MySQL Replication：是 MySQL 数据库自带的主从复制功能，它允许将一个 MySQL 实例的更改同步到其他 MySQL 实例。
• MySQL Group Replication：是 MySQL 官方推出的高可用性解决方案。它是基于 MySQL Replication 的，但是可以将多个 MySQL 实例组成一个组，实现自动故障检测和自动故障转移，提高数据库的可用性。
• MySQL InnoDB Cluster：是 MySQL 官方推出的一种完全集成的高可用性和可伸缩性解决方案。它基于 MySQL Group Replication，MySQL Shell 和 MySQL Router，提供了自动化的故障检测和故障转移，同时支持读写分离和负载均衡。某个节点出现故障时，应用无需调整，MySQL Router 会自动将请求路由到正常服务的节点上。

MySQL InnoDB Cluster 使用 MySQL Group Replication 同步数据，使用 MySQL Shell 的 AdminAPI 配置和管理集群，使用 MySQL Router 处理客户端请求，以此实现自动转移故障和自动路由请求。例如在单主模式，如果主节点出现故障，MySQL Group Replication 会自动选出一个组成员作为主节点，MySQL Router 会检测到此故障并将客户端请求路由到新的主节点。

InnoDB Cluster Requirements

InnoDB Cluster 需要满足以下条件：

• 由于 InnoDB Cluster 使用 Group Replication，故需要满足 Group Replication 的需求，参考：Group Replication Requirements。可以使用 MySQL Shell 的 AdminAPI 提供的 dba.checkInstanceConfiguration() 方法验证实例是否满足需求，dba.configureInstance() 方法配置实例以满足需求。
• 数据必须存储在 InnoDB 存储引擎，设置参数 disabled_storage_engines 阻止使用其他存储引擎。

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

• 表必须有主键。
• 任一实例上都不能配置其他复制通道，会通过 Group Replication 自动创建复制通道（group_replication_applier 和 group_replication_recovery）。
• 不能将 group_replication_tls_source 设置为 mysql_admin。
• 所有实例都必须启用 Performance Schema。
• 必须安装好 Python，使用以下命令检查是否配置正确：

[root@s1 ~]# /usr/bin/env python

• 从 MySQL 8.0.17 开始，集群各个实例的 server_id 参数必须唯一。
• 从 MySQL 8.0.23 开始，集群各个实例必须配置 Parallel Replication Applier。
• 在使用 AdminAPI 配置集群时，不会调整 transaction_isolation 参数，会保持默认值为 REPEATABLE READ。此默认值仅适用于单主模式，如果是多主模式，则需要修改为 READ COMMITTED 隔离级别。
• InnoDB Cluster 仅支持单个参数文件，不支持使用 --defaults-extra-file 选项指定额外的参数文件。

InnoDB Cluster Limitations

InnoDB Cluster 有以下限制：

• 由于 InnoDB Cluster 使用 Group Replication，故对 Group Replication 的限制同样适用于 InnoDB Cluster，参考：Group Replication Limitations。
• InnoDB Cluster 不会管理手动配置的异步复制通道。
• 适用于部署在本地局域网，满足低延迟，高带宽的网络需求。
• 对于 AdminAPI 操作，只能使用 TCP/IP 连接和传统 MySQL 协议，不支持使用 UNiX 套接字和命名管道，也不支持 X 协议。
• 多主模式不支持在不同实例上对同一对象执行并发 DDL 和 DML 操作。

User Accounts for InnoDB Cluster

InnoDB Cluster 中的成员使用 3 类账户：

InnoDB Cluster server configuration account：用于配置成员，只有 1 个，所有成员的配置账户名称和密码需保持一致。首选使用 dba.configureInstance() 命令的 clusterAdmin 和 clusterAdminPassword 选项创建该账户。由于 MySQL Shell 在执行 dba.configureInstance() 命令时会禁用二进制日志，故需要在所有成员上执行该命令。

InnoDB Cluster administrator accounts：用于管理集群，1 个或多个，所有成员的管理账户名称和密码需保持一致。在将所有实例都接入到集群后，使用 cluster.setupAdminAccount() 命令创建管理账户，此事务会写入二进制日志，故只需在主节点执行即可。

MySQL Router accounts：用于连接集群，1 个或多个，所有成员的连接账户名称和密码需保持一致。与创建管理账户一样，使用 cluster.setupAdminAccount() 命令创建连接账户。

Deploying a Production InnoDB Cluster

在生产环境，组成 InnoDB Cluster 的 MySQL server 实例运行在多个主机上，故在部署集群前，必须在各个主机上安装 MySQL server，参考：Installing MySQL on Linux。同样也在各个主机上安装 MySQL Shell。

至少需要 3 个实例才能组成 InnoDB Cluster，此时允许 1 个实例不可用。环境如下：

No.	Hostname	IP	OS	Role
1	s1	192.168.44.135	CentOS 7.8	Primary
2	s2	192.168.44.136	CentOS 7.8	Secondary
3	s3	192.168.44.137	CentOS 7.8	Secondary

先在每个实例上创建 Server Configuration Account：

[(none)]> create user icadmin identified by 'MyNewPass4!';
Query OK, 0 rows affected (0.02 sec)

[(none)]> 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 'icadmin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

[(none)]> GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'icadmin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

[(none)]> 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 'icadmin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

[(none)]> 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 'icadmin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

[(none)]> 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 'icadmin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Configuring Production Instances for InnoDB Cluster Usage

使用 MySQL Shell 的 AdminAPI 提供的 dba.configureInstance() 配置每个实例，然后使用 dba.checkInstanceConfiguration() 检查配置和表是否满足 InnoDB Cluster 的要求。

节点 s1：

[root@s1 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 39
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > dba.configureInstance('icadmin@s1:3306')
Please provide the password for 'icadmin@s1:3306': ***********
Save password for 'icadmin@s1:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as s1: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.

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
Configuring instance...
The instance 's1:3306' was configured to be used in an InnoDB cluster.

 MySQL  localhost  JS > dba.checkInstanceConfiguration('icadmin@s1:3306')
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as s1: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.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 's1:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

节点 s2：

[root@s2 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > dba.configureInstance('icadmin@s2:3306')
Please provide the password for 'icadmin@s2:3306': ***********
Save password for 'icadmin@s2:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as s2: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.

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
Configuring instance...
The instance 's2:3306' was configured to be used in an InnoDB cluster.

 MySQL  localhost  JS > dba.checkInstanceConfiguration('icadmin@s2:3306')
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as s2: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.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 's2:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

节点 s3：

[root@s3 ~]# mysqlsh
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'root@/data%2Fmysql%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 10
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost  JS > dba.configureInstance('icadmin@s3:3306')
Please provide the password for 'icadmin@s3:3306': ***********
Save password for 'icadmin@s3:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as s3: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.

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
Configuring instance...
The instance 's3:3306' was configured to be used in an InnoDB cluster.

 MySQL  localhost  JS > dba.checkInstanceConfiguration('icadmin@s3:3306')
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as s3: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.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 's3:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

Creating an InnoDB Cluster

配置好所有实例后，使用 MySQL Shell 连接到种子实例（s1），运行 dba.createCluster() 创建集群。

[root@s1 ~]# mysqlsh icadmin@s1:3306
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'icadmin@s1:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 48
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  s1:3306 ssl  JS > var cluster = dba.createCluster('cluster1')
A new InnoDB Cluster will be created on instance 's1:3306'.

Validating instance configuration at s1:3306...

This instance reports its own address as s1:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 's1:3306'. Use the localAddress option to override.

Creating InnoDB Cluster 'cluster1' on 's1: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.

将 dba.createCluster() 创建的对象赋予给变量 cluster，后续就可以使用此变量访问集群对象的方法了。

Adding Instances to an InnoDB Cluster

使用 cluster.addInstance() 增加实例到集群。

在节点 1 上添加节点 s2 上的实例到集群：

 MySQL  s1:3306 ssl  JS > cluster.addInstance('icadmin@s2:3306')

WARNING: A GTID set check of the MySQL instance at 's2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

s2:3306 has the following errant GTIDs that do not exist in the cluster:
b4bdbc58-019c-11ee-9cc3-000c2986525b:1-15

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of s2: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
Validating instance configuration at s2:3306...

This instance reports its own address as s2:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 's2: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: s2:3306 is being cloned from s1:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: s2:3306 is shutting down...

* Waiting for server restart... ready 
* s2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s)

State recovery already finished for 's2:3306'

The instance 's2:3306' was successfully added to the cluster.

在节点 1 上添加节点 s3 上的实例到集群：

 MySQL  s1:3306 ssl  JS > cluster.addInstance('icadmin@s3:3306')

WARNING: A GTID set check of the MySQL instance at 's3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

s3:3306 has the following errant GTIDs that do not exist in the cluster:
fe074cfd-ebe0-11ed-9b23-000c29c21d3a:1-15

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of s3: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
Validating instance configuration at s3:3306...

This instance reports its own address as s3:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 's3: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: s3:3306 is being cloned from s1:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: s3:3306 is shutting down...

* Waiting for server restart... ready 
* s3:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.60 MB transferred in about 1 second (~72.60 MB/s)

State recovery already finished for 's3:3306'

The instance 's3:3306' was successfully added to the cluster.

完成后使用 cluster.status() 查看集群状态：

 MySQL  s1:3306 ssl  JS > cluster.status()
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s1:3306"
}

Adopting a Group Replication Deployment

使用 dba.createCluster() 函数的 adoptFromGR 参数可以基于一个部署好的 Group Replication 环境创建 InnoDB Cluster。

mysql-js> var cluster = dba.createCluster('prodCluster', {adoptFromGR: true});

A new InnoDB cluster will be created on instance 'root@gr-member-2:3306'.

Creating InnoDB cluster 'prodCluster' on 'root@gr-member-2: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.

如果连接到的实例属于一个复制组，即使没有指定 adoptFromGR: true，MySQL Shell 也会提示确认是否基于此复制组创建集群。

集群的模式与组复制的模式一致。如果组复制为单主模式，则集群也为单主模式。如果组复制为多主模式，则集群也为多主模式。

Configuring InnoDB Cluster

Setting Options for InnoDB Cluster

使用 cluster.options() 查看集群设置：

 MySQL  s1:3306 ssl  JS > cluster.options()
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "globalOptions": [
            {
                "option": "groupName", 
                "value": "020da8db-090a-11ee-bfb7-000c29b7db9b", 
                "variable": "group_replication_group_name"
            }, 
            {
                "option": "memberSslMode", 
                "value": "REQUIRED", 
                "variable": "group_replication_ssl_mode"
            }, 
            {
                "option": "transactionSizeLimit", 
                "value": "150000000", 
                "variable": "group_replication_transaction_size_limit"
            }, 
            {
                "option": "disableClone", 
                "value": false
            }, 
            {
                "option": "replicationAllowedHost", 
                "value": "%"
            }, 
            {
                "option": "communicationStack", 
                "value": "MYSQL", 
                "variable": "group_replication_communication_stack"
            }
        ], 
        "tags": {
            "global": [], 
            "s1:3306": [], 
            "s2:3306": [], 
            "s3:3306": []
        }, 
        "topology": {
            "s1:3306": [
                {
                    "option": "autoRejoinTries", 
                    "value": "3", 
                    "variable": "group_replication_autorejoin_tries"
                }, 
                {
                    "option": "consistency", 
                    "value": "BEFORE_ON_PRIMARY_FAILOVER", 
                    "variable": "group_replication_consistency"
                }, 
                {
                    "option": "exitStateAction", 
                    "value": "READ_ONLY", 
                    "variable": "group_replication_exit_state_action"
                }, 
                {
                    "option": "expelTimeout", 
                    "value": "5", 
                    "variable": "group_replication_member_expel_timeout"
                }, 
                {
                    "option": "groupSeeds", 
                    "value": "s2:3306,s3:3306", 
                    "variable": "group_replication_group_seeds"
                }, 
                {
                    "option": "ipAllowlist", 
                    "value": "AUTOMATIC", 
                    "variable": "group_replication_ip_allowlist"
                }, 
                {
                    "option": "ipWhitelist", 
                    "value": "AUTOMATIC", 
                    "variable": "group_replication_ip_whitelist"
                }, 
                {
                    "option": "localAddress", 
                    "value": "s1:3306", 
                    "variable": "group_replication_local_address"
                }, 
                {
                    "option": "memberWeight", 
                    "value": "50", 
                    "variable": "group_replication_member_weight"
                }, 
                {
                    "value": "WRITESET", 
                    "variable": "binlog_transaction_dependency_tracking"
                }, 
                {
                    "value": "LOGICAL_CLOCK", 
                    "variable": "replica_parallel_type"
                }, 
                {
                    "value": "4", 
                    "variable": "replica_parallel_workers"
                }, 
                {
                    "value": "ON", 
                    "variable": "replica_preserve_commit_order"
                }, 
                {
                    "value": "XXHASH64", 
                    "variable": "transaction_write_set_extraction"
                }
            ], 
            "s2:3306": [
                {
                    "option": "autoRejoinTries", 
                    "value": "3", 
                    "variable": "group_replication_autorejoin_tries"
                }, 
                {
                    "option": "consistency", 
                    "value": "BEFORE_ON_PRIMARY_FAILOVER", 
                    "variable": "group_replication_consistency"
                }, 
                {
                    "option": "exitStateAction", 
                    "value": "READ_ONLY", 
                    "variable": "group_replication_exit_state_action"
                }, 
                {
                    "option": "expelTimeout", 
                    "value": "5", 
                    "variable": "group_replication_member_expel_timeout"
                }, 
                {
                    "option": "groupSeeds", 
                    "value": "s1:3306,s3:3306", 
                    "variable": "group_replication_group_seeds"
                }, 
                {
                    "option": "ipAllowlist", 
                    "value": "AUTOMATIC", 
                    "variable": "group_replication_ip_allowlist"
                }, 
                {
                    "option": "ipWhitelist", 
                    "value": "AUTOMATIC", 
                    "variable": "group_replication_ip_whitelist"
                }, 
                {
                    "option": "localAddress", 
                    "value": "s2:3306", 
                    "variable": "group_replication_local_address"
                }, 
                {
                    "option": "memberWeight", 
                    "value": "50", 
                    "variable": "group_replication_member_weight"
                }, 
                {
                    "value": "WRITESET", 
                    "variable": "binlog_transaction_dependency_tracking"
                }, 
                {
                    "value": "LOGICAL_CLOCK", 
                    "variable": "replica_parallel_type"
                }, 
                {
                    "value": "4", 
                    "variable": "replica_parallel_workers"
                }, 
                {
                    "value": "ON", 
                    "variable": "replica_preserve_commit_order"
                }, 
                {
                    "value": "XXHASH64", 
                    "variable": "transaction_write_set_extraction"
                }
            ], 
            "s3:3306": [
                {
                    "option": "autoRejoinTries", 
                    "value": "3", 
                    "variable": "group_replication_autorejoin_tries"
                }, 
                {
                    "option": "consistency", 
                    "value": "BEFORE_ON_PRIMARY_FAILOVER", 
                    "variable": "group_replication_consistency"
                }, 
                {
                    "option": "exitStateAction", 
                    "value": "READ_ONLY", 
                    "variable": "group_replication_exit_state_action"
                }, 
                {
                    "option": "expelTimeout", 
                    "value": "5", 
                    "variable": "group_replication_member_expel_timeout"
                }, 
                {
                    "option": "groupSeeds", 
                    "value": "s2:3306,s1:3306", 
                    "variable": "group_replication_group_seeds"
                }, 
                {
                    "option": "ipAllowlist", 
                    "value": "AUTOMATIC", 
                    "variable": "group_replication_ip_allowlist"
                }, 
                {
                    "option": "ipWhitelist", 
                    "value": "AUTOMATIC", 
                    "variable": "group_replication_ip_whitelist"
                }, 
                {
                    "option": "localAddress", 
                    "value": "s3:3306", 
                    "variable": "group_replication_local_address"
                }, 
                {
                    "option": "memberWeight", 
                    "value": "50", 
                    "variable": "group_replication_member_weight"
                }, 
                {
                    "value": "WRITESET", 
                    "variable": "binlog_transaction_dependency_tracking"
                }, 
                {
                    "value": "LOGICAL_CLOCK", 
                    "variable": "replica_parallel_type"
                }, 
                {
                    "value": "4", 
                    "variable": "replica_parallel_workers"
                }, 
                {
                    "value": "ON", 
                    "variable": "replica_preserve_commit_order"
                }, 
                {
                    "value": "XXHASH64", 
                    "variable": "transaction_write_set_extraction"
                }
            ]
        }
    }
}

可以在集群级别和实例级别配置这些参数：

• cluster.setOption(option, value)：在集群级别配置参数。
• cluster.setInstanceOption(instance, option, value)：在实例级别配置参数。

具体使用哪个函数，取决于是否可以将参数在所有实例上配置为相同值。

既可以在集群级别配置，也可以在实例级别配置的参数有：

• autoRejoinTries
• exitStateAction
• memberWeight
• ipAllowList
• tag:option

只能在集群级别配置的参数有：

• clusterName
• disableClone
• replicationAllowedHost
• expelTimeout
• consistency
• transactionSizeLimit

只能在实例级别配置的参数有：

• label

Configuring the Election Process

可以在执行 dba.createCluster() 和 cluster.addInstance() 时使用 memberWeight 选项以修改 group_replication_member_weight 参数，从而指定各个成员的权重，默认为 50，范围为 0 到 100，值越大权重越高，则在单主模式中更有可能被选为主节点。如果多个成员的权重相同，则选择参数 server_uuid 指定的 UUID 最小的成员为主节点。具体可以参考：Primary Election Algorithm。

dba.createCluster('cluster1', {memberWeight:35})
var mycluster = dba.getCluster()
mycluster.addInstance('icadmin@s2', {memberWeight:25})
mycluster.addInstance('icadmin@s3', {memberWeight:50})

Configuring Failover Consistency

可以在执行 dba.createCluster() 时使用 consistency 选项以修改 group_replication_consistency 参数，从而指定所有成员的事务一致性级别，集群默认为 BEFORE_ON_PRIMARY_FAILOVER。具体可以参考： Transaction Consistency Guarantees。

Configuring Automatic Rejoin of Instances

可以在以下命令中使用 autoRejoinTries 选项修改 group_replication_autorejoin_tries 参数，指定在成员被驱逐后重新加入集群的尝试次数，默认为 3，范围为 0 到 2016。具体可以参考：Auto-Rejoin。

• dba.createCluster()
• cluster.addInstance()
• cluster.setOption()
• cluster.setInstanceOption()

可以在以下命令中使用 exitStateAction 选项修改 group_replication_exit_state_action 参数，指定在成员意外离开集群时执行特定的退出操作，默认为 READ_ONLY。具体可以参考：Exit Action。

• cluster.status()
• dba.getCluster()
• cluster.rejoinInstance()
• cluster.addInstance()
• cluster.removeInstance()
• cluster.rescan()
• cluster.checkInstanceState()

Configuring the Parallel Replication Applier

从 MySQL 8.0.23 开始，可以使用并行复制应用（Parallel Replication Applier）线程，也就是所谓的多线程复制。需要配置如下参数：

• binlog_transaction_dependency_tracking=WRITESET
• slave_preserve_commit_order=ON
• slave_parallel_type=LOGICAL_CLOCK
• transaction_write_set_extraction=XXHASH64

默认情况下，应用线程的数量为 4，由参数 replica_parallel_workers 指定。

使用 cluster.status(extended=1) 命令查看并行复制应用的信息。使用 cluster.options() 命令查看并行复制应用的参数。

可以在执行 dba.configureInstance() 和 dba.configureReplicaSetInstance() 时使用 applierWorkerThreads 选项以修改 replica_parallel_workers 参数，从而指定并行复制应用线程数量，默认为 4，范围为 0 到 1024，例如：

mysql-js> dba.configureInstance(instance, {applierWorkerThreads: 8, restart: true})

注意：

修改并行复制应用线程数量需要重启实例。

InnoDB Cluster and Auto-increment

对于 InnoDB Cluster 中的实例，为避免自增主键冲突，需要做如下配置：

• 单主模式：设置参数 auto_increment_increment 为 1，auto_increment_offset 为 2。
• 多主模式：
  • 少于等于 7 个实例：设置参数 auto_increment_increment 为 7，auto_increment_offset 为 1 + server_id % 7。
  • 多于等于 8 个实例：设置参数 auto_increment_increment 为实例数量，auto_increment_offset 为 1 + server_id % 7。

Configuring the Group Replication Communication Stack

从 MySQL Shell 8.0.30 开始，InnoDB Cluster 支持从 MySQL 8.0.27 中为 Group Replication 引入的 MySQL 通信协议栈。

Communication Stack Types

支持以下通信协议栈：

MYSQL： MySQL 8.0.27 及后续版本的默认值，Oracle 推荐使用 MYSQL 通信协议栈替换 XCOM。

XCOM：MySQL 8.0.27 及之前版本的默认值。

Selecting Communication Stack

可以在执行 dba.createCluster() 时使用 communicationStack 选项以修改 group_replication_communication_stack 参数，从而指定通信协议栈。例如：

js> dba.createCluster("testCluster", {communicationStack: "mysql"})

Switching Communication Stack

可以在重启时切换通信协议栈：

js> dba.rebootClusterFromCompleteOutage("testcluster", {switchCommunicationStack: "mysql"})

Monitoring InnoDB Cluster

使用 cluster.describe() 获取 InnoDB Cluster 的结构信息：

 MySQL  s1:3306 ssl  JS > cluster.describe()
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "s1:3306", 
                "label": "s1:3306", 
                "role": "HA"
            }, 
            {
                "address": "s2:3306", 
                "label": "s2:3306", 
                "role": "HA"
            }, 
            {
                "address": "s3:3306", 
                "label": "s3:3306", 
                "role": "HA"
            }
        ], 
        "topologyMode": "Single-Primary"
    }
}

使用 cluster.status() 获取 InnoDB Cluster 的运行状态：

 MySQL  s1:3306 ssl  JS > cluster.status()
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s1:3306"
}

其中的 status 表示 InnoDB Cluster 的运行状态，可能的状态有：

• OK：正常提供服务，所有成员处于联机状态。
• OK_PARTIAL：正常提供服务，少数成员处于非联机状态，但仍有冗余成员。
• OK_NO_TOLERANCE：正常提供服务，处于非联机状态的成员数量达到最大，此时无冗余成员。
• NO_QUORUM：不能提供服务，多数成员不可用。
• OFFLINE：所有成员都处于离线状态。
• ERROR：没有成员处于联机状态。
• UNREACHABLE：无法连接到任一联机成员。
• FENCED_WRITES：集群被阻止写入。

Restoring and Rebooting an InnoDB Cluster

Rejoining an Instance to a Cluster

实例离开集群后，如果无法自动加入集群，使用 cluster.rejoinInstance(instance) 将实例重新加入集群。

如果实例的 server_uuid 参数发生了变化，则需要先使用 cluster.removeInstance() 及 force 选项移除旧的 server_uuid，再使用 cluster.rescan() 将新实例的 server_uuid 加入到元数据中。

例如：

cluster.removeInstance("root@instanceWithOldUUID:3306", {force: true})

cluster.rescan()

Restoring a Cluster from Quorum Loss

如果集群中的多数成员不可用，则此时集群停止对外提供服务，不再处理写事务，也不能修改集群的拓扑，包括增加，重新加入及删除实例。参考： Fault-tolerance。如果此时还有包含 InnoDB Cluster 元数据的实例处于 ONLINE 状态，则可以还原集群。

注意：

此操作应被视为最后的补救措施，必须小心使用，只能用于多数成员失败的场景。如果误用，可能会出现裂脑或整个系统不可用。

连接到包含集群元数据的实例，执行 cluster.forceQuorumUsingPartitionOf(instance)，将此时该实例的组视图中所有处于 ONLINE 状态的实例都加入到还原的集群中。

mysql-js> cluster.forceQuorumUsingPartitionOf("icadmin@ic-1:3306")

  Restoring replicaset 'default' from loss of quorum, by using the partition composed of [icadmin@ic-1:3306]

  Please provide the password for 'icadmin@ic-1:3306': ******
  Restoring the InnoDB cluster ...

  The InnoDB cluster was successfully restored using the partition from the instance 'icadmin@ic-1:3306'.

  WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset
  are removed or joined back to the group that was restored.

具体可以参考：Unblocking a Partition。

Rebooting a Cluster from a Major Outage

关闭所有成员实例后，使用 dba.rebootClusterFromCompleteOutage() 启动集群。

注意：

在使用 dba.rebootClusterFromCompleteOutage() 前，需确保所有成员实例已启动。

连接到之前的主节点（即有最新数据的节点）执行：

  JS> var cluster = dba.rebootClusterFromCompleteOutage()

如果所有成员的 GTID 相同，则连接执行的成员变为主节点。

dba.rebootClusterFromCompleteOutage() 按以下步骤确保正确配置集群：

• 从当前成员获取集群元数据和集群拓扑。
• 如果某个成员的状态为 RECOVERING 或者 ERROR，其他所有成员的状态为 OFFLINE 或者 ERROR，dba.rebootClusterFromCompleteOutage() 将尝试停止这个成员的组复制，如果组复制停止失败，则该命令停止并报错。
• 查看当前连接成员的 InnoDB Cluster 元数据中的 GTID 超集，如果当前连接成员不包含 GTID 超集，则放弃该重启操作。
• 如果当前连接成员包含 GTID 超集，则基于此成员的元数据进行恢复。
• 检查集群中哪些成员是可以访问的，如果任一成员无法访问，则操作失败。
• 检查集群中哪些成员是无法访问的，如果无法访问，不能使用 dba.rebootClusterFromCompleteOutage() 增加或者移除这些成员。
• 如果在单主模式时，主节点启用了 super_read_only，则会禁用。

GTID Superset

必须连接到有 GTID 超集的成员重启集群。

可以使用以下任一方法确定哪个成员有 GTID 超集。

• 连接实例使用 dryRun: true 选项运行 dba.rebootClusterFromCompleteOutage() ，可以在输出报告中看到有 GTID 超集的实例：

              Switching over to instance '127.0.0.1:4001' to be used as seed.

• 使用 SQL 模式依次连接到每个实例执行：

SHOW VARIABLES LIKE 'gtid_executed';

有最大 GTID 集合的成员包含了 GTID 超集。

Options

dba.rebootClusterFromCompleteOutage() 有以下选项：

• force: true | false (default)：在某些成员无法访问，或者成员不包含 GTID 超集，使用 force: true 强制执行。例如：

  JS> var cluster = dba.rebootClusterFromCompleteOutage("myCluster",{force: true})

• dryRun: true | false (default)：只进行验证，不实际执行，执行完成后生成报告。例如：

JS > var cluster = dba.rebootClusterFromCompleteOutage("myCluster",{primary: "127.0.0.1:4001", dryRun: true})

NOTE: dryRun option was specified. Validations will be executed, but no changes will be applied.
Cluster instances: '127.0.0.1:4000' (OFFLINE), '127.0.0.1:4001' (OFFLINE), '127.0.0.1:4002' (OFFLINE)
Switching over to instance '127.0.0.1:4001' to be used as seed.
dryRun finished.

• primary：指定主节点。例如：

var cluster = dba.rebootClusterFromCompleteOutage("myCluster",{primary: "127.0.0.1:4001"})

• switchCommunicationStack: mysql | xcom：指定通信协议栈，对应参数 group_replication_communication_stack。例如：

        js> dba.rebootClusterFromCompleteOutage("testcluster", {switchCommunicationStack: "mysql"})

• ipAllowList：使用 XCOM 协议栈时，允许加入到复制组的主机列表，对应参数 group_replication_ip_allowlist。
• localAddress：使用 XCOM 协议栈时，与其他组成员进行内部通信时使用的地址和端口，对应参数 group_replication_local_address。

Example

例子：重启 InnoDB Cluster

[root@s1 ~]# mysqlsh icadmin@s1:3306
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'icadmin@s1:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 133
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  s1:3306 ssl  JS > var cluster = dba.rebootClusterFromCompleteOutage("cluster1",{dryRun: true})
NOTE: dryRun option was specified. Validations will be executed, but no changes will be applied.
Cluster instances: 's1:3306' (OFFLINE), 's2:3306' (OFFLINE), 's3:3306' (OFFLINE)
Waiting for instances to apply pending received transactions...
dryRun finished.

 MySQL  s1:3306 ssl  JS > var cluster = dba.rebootClusterFromCompleteOutage()
Restoring the Cluster 'cluster1' from complete outage...

Cluster instances: 's1:3306' (OFFLINE), 's2:3306' (OFFLINE), 's3:3306' (OFFLINE)
Waiting for instances to apply pending received transactions...
Validating instance configuration at s1:3306...

This instance reports its own address as s1:3306

Instance configuration is suitable.
* Waiting for seed instance to become ONLINE...
s1:3306 was restored.
Validating instance configuration at s2:3306...

This instance reports its own address as s2:3306

Instance configuration is suitable.
Rejoining instance 's2:3306' to cluster 'cluster1'...

Re-creating recovery account...
NOTE: User 'mysql_innodb_cluster_44136'@'%' already existed at instance 's1:3306'. It will be deleted and created again with a new password.

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...
** Transactions replicated  ############################################################  100% 

The instance 's2:3306' was successfully rejoined to the cluster.

Validating instance configuration at s3:3306...

This instance reports its own address as s3:3306

Instance configuration is suitable.
Rejoining instance 's3:3306' to cluster 'cluster1'...

Re-creating recovery account...
NOTE: User 'mysql_innodb_cluster_44137'@'%' already existed at instance 's1:3306'. It will be deleted and created again with a new password.

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...
** Transactions replicated  ############################################################  100% 

The instance 's3:3306' was successfully rejoined to the cluster.

The Cluster was successfully rebooted.

 MySQL  s1:3306 ssl  JS > cluster.status()
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s1:3306"
}

Rescanning a Cluster

如果没有使用 AdminAPI，而是直接使用组复制的命令和参数对集群进行了配置，需要使用 cluster.rescan() 更新 InnoDB Cluster 的元数据以匹配当前的配置。cluster.rescan() 可以检测到没有在元数据中注册的新活动实例并添加到元数据，也可以检测到注册到元数据中的不再活动的过时实例并从元数据中删除。

语法为：

cluster.rescan([options])

options 可以是：

• interactive：是否启用交互模式，默认值为 shell.options.useWizards 的值。

 MySQL  s1:3306 ssl  JS > shell.options.useWizards
true

• addInstances：要加入到元数据的新活动实例列表，也可以使用 auto 自动添加实例到元数据。

  • 列表中指定的实例将添加到元数据中，不会提示确认。
  • 在交互模式下，会提示确认添加未包含在列表中的实例。
  • 在非交互模式下，输出报告显示未包含在列表中的实例，但不会提示添加。

• removeInstances：要从元数据删除的过时实例列表，也可以使用 auto 自动从元数据删除过时实例。

  • 列表中指定的实例将从元数据中删除，不会提示确认。

  • 在交互模式下，会提示确认删除未包含在列表中的过时实例。

  • 在非交互模式下，输出报告显示未包含在列表中的过时实例，但不会提示删除。

• updateTopologyMode：是否需要更新元数据中的拓扑模式（单主模式或者多主模式）以与集群相匹配。默认不更新。

  • 如果值为 true，会比较 InnoDB Cluster 元数据中的拓扑模式与 Group Replication 所使用的模式，如果不一致，则进行更新。
  • 如果值为 false，即使 InnoDB Cluster 元数据中的拓扑模式与 Group Replication 所使用的模式不一致，也不会更新。
  • 如果没有指定值，且 InnoDB Cluster 元数据中的拓扑模式与 Group Replication 所使用的模式不一致：
    • 在交互模式下，会提示确认更新元数据中的拓扑模式。
    • 在非交互模式下，如果模式不一致，会报告但不会更新。
  • 当更新了元数据中的拓扑模式，所有实例上的自增参数也会更新。参考： InnoDB Cluster and Auto-increment。

Modifying or Dissolving an InnoDB Cluster

Changing a Cluster's Topology

默认情况下，InnoDB Cluster 运行在单主模式，一个主节点可读写，其余实例只读。

将集群配置在多主模式下运行时，集群中的所有实例都是主节点，都可读写。

如果集群所有实例的 MySQL Server 版本为 8.0.15 或更高，则可以在集群联机时更改集群的拓扑。在以前的版本中，必须完全解散并重新创建群集才能进行配置更改。

在单主模式下，如果主节点意外离开集群，会自动选择一个实例作为主节点。可以使用 cluster.setPrimaryInstance(instance[, options]) 指定某个实例为新主节点，从 MySQL Shell 8.0.29 开始，还可以使用 runningTransactionsTimeout 选项为正在运行的事务指定超时时间，范围为 0 到 3600 秒。

可以使用以下操作更改拓扑：

• cluster.switchToMultiPrimaryMode()：将集群切换到多主模式。
• cluster.switchToSinglePrimaryMode([instance])：将集群切换到单主模式。如果指定了 instance 选项，则此实例为主节点；如果没有指定 instance 选项，则权重最高的成员为主节点，权重一致则 UUID 最小的成员为主节点。

Removing Instances from an InnoDB Cluster

可以使用 cluster.removeInstance(instance) 从集群移除实例。

例如：

mysql-js> cluster.removeInstance('root@localhost:3310')

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.

Attempting to leave from the Group Replication group...

The instance 'localhost:3310' was successfully removed from the cluster.

可以使用 interactive 选项指定是否需要对移除实例进行确认。

不能移除集群中最后一个 ONLINE 状态的实例。

当被移除实例上有事务在运行时，会等待 dba.gtidWaitTimeout 设置的超时时间，默认为 60 秒。达到超时时间后：

• 如果 force 选项为 false 或未指定，则返回错误，放弃移除操作。
• 如果 force 选项为 true，不会报错，继续移除实例。

Dissolving an InnoDB Cluster

连接到读写实例，使用 cluster.dissolve() 解散集群。此操作会删除集群相关的所有元数据和配置，并禁用组复制，但不会删除业务数据。

 MySQL  s2:3306 ssl  JS > cluster.dissolve()
The cluster still has the following registered instances:
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "s1:3306", 
                "label": "s1:3306", 
                "role": "HA"
            }, 
            {
                "address": "s2:3306", 
                "label": "s2:3306", 
                "role": "HA"
            }, 
            {
                "address": "s3:3306", 
                "label": "s3:3306", 
                "role": "HA"
            }
        ], 
        "topologyMode": "Single-Primary"
    }
}
WARNING: You are about to dissolve the whole cluster and lose the high availability features provided by it. This operation cannot be reverted. All members will be removed from the cluster and replication will be stopped, internal recovery user accounts and the cluster metadata will be dropped. User data will be maintained intact in all instances.

Are you sure you want to dissolve the cluster? [y/N]: y

* Waiting for instance 's1:3306' to apply received transactions...
** Transactions replicated  ############################################################  100% 

* Waiting for instance 's2:3306' to apply received transactions...
** Transactions replicated  ############################################################  100% 

* Waiting for instance 's3:3306' to apply received transactions...
** Transactions replicated  ############################################################  100% 

* Dissolving the Cluster...
* Waiting for instance 's1:3306' to apply received transactions...
** Transactions replicated  ############################################################  100% 

* Instance 's1:3306' is attempting to leave the cluster...
* Waiting for instance 's2:3306' to apply received transactions...
** Transactions replicated  ############################################################  100% 

* Instance 's2:3306' is attempting to leave the cluster...
* Waiting for instance 's3:3306' to apply received transactions...
** Transactions replicated  ############################################################  100% 

* Instance 's3:3306' is attempting to leave the cluster...

The cluster was successfully dissolved.
Replication was disabled but user data was left intact.

注意：

不能取消解散集群操作，只能使用 dba.createCluster() 重建集群。

cluster.dissolve() 只能处理联机或可访问的实例。

在交互模式，如果有无法访问的集群成员，则在解散过程中会显示错误和提示确认：

mysql-js> cluster.dissolve()
The cluster still has the following registered instances:
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "ic-1:3306",
                "label": "ic-1:3306",
                "role": "HA"
            },
            {
                "address": "ic-2:3306",
                "label": "ic-2:3306",
                "role": "HA"
            },
            {
                "address": "ic-3:3306",
                "label": "ic-3:3306",
                "role": "HA"
            }
        ]
    }
}
WARNING: You are about to dissolve the whole cluster and lose the high
availability features provided by it. This operation cannot be reverted. All
members will be removed from the cluster and replication will be stopped,
internal recovery user accounts and the cluster metadata will be dropped. User
data will be maintained intact in all instances.

Are you sure you want to dissolve the cluster? [y/N]: y

ERROR: The instance 'ic-2:3306' cannot be removed because it is on a '(MISSING)'
state. Please bring the instance back ONLINE and try to dissolve the cluster
again. If the instance is permanently not reachable, then you can choose to
proceed with the operation and only remove the instance from the Cluster
Metadata.

Do you want to continue anyway (only the instance metadata will be removed)?
[y/N]: y

Instance 'ic-3:3306' is attempting to leave the cluster...  Instance 'ic-1:3306'
is attempting to leave the cluster...

WARNING: The cluster was successfully dissolved, but the following instance was
skipped: 'ic-2:3306'. Please make sure this instance is permanently unavailable
or take any necessary manual action to ensure the cluster is fully dissolved.

在非交互模式，可以使用 force 选项忽略不能访问的实例，强制解散集群：

mysql-js> cluster.dissolve({force: true})

可以使用 interactive 选项覆盖 MySQL Shell 的当前模式：

mysql-js> Cluster.dissolve({interactive: true})

与移除实例类似，在解散集群时，当联机实例上有事务在运行时，cluster.dissolve() 操作会等待 dba.gtidWaitTimeout 设置的超时时间，默认为 60 秒。达到超时时间后：

• 如果 force 选项为 false 或未指定，则返回错误，放弃解散操作。
• 如果 force 选项为 true，不会报错，继续解散集群。

Using MySQL Router with InnoDB Cluster

Configuring the MySQL Router User

创建好集群后，使用 setupRouterAccount(user, [options]) 创建 MySQL Router 账户：

[root@s1 ~]# mysqlsh icadmin@s1:3306
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'icadmin@s1:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 86
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  s1:3306 ssl  JS > var cluster = dba.getCluster();
 MySQL  s1:3306 ssl  JS > cluster.setupRouterAccount('router')

Missing the password for new account router@%. Please provide one.
Password for new account: MyNewPass4!
Confirm password: MyNewPass4!

Creating user router@%.
Account router@% was successfully created.

Deploying MySQL Router

在应用服务器安装 MySQL Router 后，使用 --bootstrap 选项，通过引导来生成 MySQL Router 配置文件 mysqlrouter.conf。

[root@s1 ~]# mysqlrouter --bootstrap icadmin@s1:3306 --account router --user mysqlrouter
Please enter MySQL password for icadmin: MyNewPass4!
# Bootstrapping system MySQL Router instance...

Please enter MySQL password for router: MyNewPass4!
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'

# MySQL Router configured for the InnoDB Cluster 'cluster1'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

InnoDB Cluster 'cluster1' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

注意：

--user 需要指定为 mysqlrouter，不要指定为 root，否则使用 systemctl start mysqlrouter.service 会启动失败。

查看生成的配置文件：

[root@s1 ~]# cat /etc/mysqlrouter/mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=system
user=mysqlrouter
keyring_path=/var/lib/mysqlrouter/keyring
master_key_path=/etc/mysqlrouter/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=/var/lib/mysqlrouter/state.json
client_ssl_cert=/var/lib/mysqlrouter/router-cert.pem
client_ssl_key=/var/lib/mysqlrouter/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error

[logger]
level=INFO

[metadata_cache:bootstrap]
cluster_type=gr
router_id=1
user=router
metadata_cluster=cluster1
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://cluster1/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://cluster1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:bootstrap_x_rw]
bind_address=0.0.0.0
bind_port=6448
destinations=metadata-cache://cluster1/?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:bootstrap_x_ro]
bind_address=0.0.0.0
bind_port=6449
destinations=metadata-cache://cluster1/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

[http_server]
port=8443
ssl=1
ssl_cert=/var/lib/mysqlrouter/router-cert.pem
ssl_key=/var/lib/mysqlrouter/router-key.pem

[http_auth_realm:default_auth_realm]
backend=default_auth_backend
method=basic
name=default_realm

[rest_router]
require_realm=default_auth_realm

[rest_api]

[http_auth_backend:default_auth_backend]
backend=metadata_cache

[rest_routing]
require_realm=default_auth_realm

[rest_metadata_cache]
require_realm=default_auth_realm

在 [DEFAULT] 部分的 dynamic_state 选项指定了集群的成员信息：

[root@s1 ~]# cat /var/lib/mysqlrouter/state.json
{
    "metadata-cache": {
        "group-replication-id": "020da8db-090a-11ee-bfb7-000c29b7db9b",
        "cluster-metadata-servers": [
            "mysql://s1:3306",
            "mysql://s2:3306",
            "mysql://s3:3306"
        ]
    },
    "version": "1.0.0"
}

MySQL Router 会自动跟踪和存储活动的 MySQL 成员地址到 dynamic_state 指定的文件中。

在 [routing:bootstrap_xxxx] 部分的 bind_port 选项指定了 4 个端口，用于传统 MySQL 协议和 X 协议与集群的连接：

• 6446：用于传统 MySQL 协议的读写会话，会将请求重定向到主节点。
• 6447：用于传统 MySQL 协议的只读会话，会将请求重定向到从节点。
• 6448：用于 X 协议的读写会话，会将请求重定向到主节点。
• 6449：用于 X 协议的只读会话，会将请求重定向到从节点。

在 [routing:bootstrap_xxxx] 部分的 routing_strategy 选项指定了路由策略，具体有：

• round-robin：
• round-robin-with-fallback
• first-available
• next-available

Starting MySQL Router

启动 MySQL Router 并配置为开机启动：

[root@s1 ~]# systemctl start mysqlrouter.service 
[root@s1 ~]# systemctl enable mysqlrouter.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqlrouter.service to /usr/lib/systemd/system/mysqlrouter.service.
[root@s1 ~]# systemctl status mysqlrouter.service
● mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2023-06-14 18:07:14 CST; 17s ago
 Main PID: 2693 (mysqlrouter)
   Status: "running"
   CGroup: /system.slice/mysqlrouter.service
           └─2693 /usr/bin/mysqlrouter

Jun 14 18:07:14 s1 systemd[1]: Starting MySQL Router...
Jun 14 18:07:14 s1 systemd[1]: Started MySQL Router.

测试连接：

[root@s1 ~]# mysqlsh stone@s1:6446 --sql
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'stone@s1:6446'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 9346
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  s1:6446 ssl  SQL > select @@hostname,@@port;
+------------+--------+
| @@hostname | @@port |
+------------+--------+
| s1         |   3306 |
+------------+--------+
1 row in set (0.0008 sec)

Testing InnoDB Cluster High Availability

此处主要测试单主模式下，3 节点的集群。

[root@s1 ~]# mysqlsh icadmin@s1:3306
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'icadmin@s1:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 22355
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  s1:3306 ssl  JS > var cluster = dba.getCluster();
 MySQL  s1:3306 ssl  JS > cluster.status();
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s1:3306"
}

​

1. 测试只读节点的负载均衡

[root@s1 ~]# for ((i=0;i<=5;i++));do mysql -h192.168.44.135 -ustone -p'MyNewPass4!' -P6447 -e"select @@hostname;";sleep 1;done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s3         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s3         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s3         |
+------------+

可以看到，使用 6447 只读端口会连接到 InnoDB Cluster 集群中所有只读节点，并且以 round-robin-with-fallback 的策略调度。

​

2. 测试在运行过程中，关闭一个只读实例，再启动，MySQL Route 是否可以剔除问题节点，添加恢复正常的节点。

先模拟访问：

[root@s1 ~]# for ((i=0;i<=5;i++));do mysql -h192.168.44.135 -ustone -p'MyNewPass4!' -P6447 -e"select @@hostname;";sleep 3;done;

再关闭只读实例 s3：

[root@s3 ~]# systemctl stop mysqld.service

 MySQL  s1:3306 ssl  JS > cluster.status();
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE_PARTIAL", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "n/a", 
                "readReplicas": {}, 
                "role": "HA", 
                "shellConnectError": "MySQL Error 2003: Could not open connection to 's3:3306': Can't connect to MySQL server on 's3:3306' (111)", 
                "status": "(MISSING)"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s1:3306"
}

可以看到此时集群状态为 OK_NO_TOLERANCE_PARTIAL，s3 的状态为 MISSING。

查看访问结果：

[root@s1 ~]# for ((i=0;i<=5;i++));do mysql -h192.168.44.135 -ustone -p'MyNewPass4!' -P6447 -e"select @@hostname;";sleep 3;done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+

可以看到访问实例 s3 时报错，后续就没有再连接该实例了，MySQL Route 剔除了该问题节点。

继续模拟访问：

[root@s1 ~]# for ((i=0;i<=5;i++));do mysql -h192.168.44.135 -ustone -p'MyNewPass4!' -P6447 -e"select @@hostname;";sleep 3;done;

然后启动实例 s3：

[root@s3 ~]# systemctl start mysqld.service

 MySQL  s1:3306 ssl  JS > cluster.status();
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s1:3306"
}

此时集群恢复正常，查看访问结果：

[root@s1 ~]# for ((i=0;i<=5;i++));do mysql -h192.168.44.135 -ustone -p'MyNewPass4!' -P6447 -e"select @@hostname;";sleep 3;done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s3         |
+------------+

可以看到此时访问到 s3 实例了，MySQL Route 添加了恢复正常的节点。

​

3. 测试关闭主节点，MySQL Route 是否可以切换到新的主机点

先模拟访问：

[root@s1 ~]# for ((i=0;i<=5;i++));do mysql -h192.168.44.135 -ustone -p'MyNewPass4!' -P6446 -e"select @@hostname;";sleep 5;done;

再关闭主节点实例 s1：

[root@s1 ~]# systemctl stop mysqld.service 

 MySQL  s2:3306 ssl  JS > cluster.status();
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s2:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE_PARTIAL", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "SECONDARY", 
                "mode": "n/a", 
                "readReplicas": {}, 
                "role": "HA", 
                "shellConnectError": "MySQL Error 2003: Could not open connection to 's1:3306': Can't connect to MySQL server on 's1:3306' (111)", 
                "status": "(MISSING)"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s2:3306"
}

可以看到实例 s1 关闭后，选择了实例 s2 为新的主节点。

查看访问结果：

[root@s1 ~]# for ((i=0;i<=5;i++));do mysql -h192.168.44.135 -ustone -p'MyNewPass4!' -P6446 -e"select @@hostname;";sleep 5;done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s1         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| s2         |
+------------+

可以看到不能访问实例 s1 后，自动访问新主节点 s2。

再次启动实例 s1：

[root@s1 ~]# systemctl start mysqld.service 

 MySQL  s2:3306 ssl  JS > cluster.status();
{
    "clusterName": "cluster1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "s2:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "s1:3306": {
                "address": "s1:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s2:3306": {
                "address": "s2:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }, 
            "s3:3306": {
                "address": "s3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.32"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "s2:3306"
}

启动后，主节点还是 s2，s1 变为只读节点。

Working with a Cluster's Routers

可以在多个应用服务器上部署 MySQL Router，连接到 InnoDB Cluster。

使用 cluster.listRouters() 查看注册到集群的所有 MySQL Router 实例：

[root@s1 ~]# mysqlsh icadmin@s1:3306
MySQL Shell 8.0.32

Copyright (c) 2016, 2023, 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 Classic session to 'icadmin@s1:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 6365
Server version: 8.0.32 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  s1:3306 ssl  JS > var cluster = dba.getCluster();
 MySQL  s1:3306 ssl  JS > cluster.listRouters();
{
    "clusterName": "cluster1", 
    "routers": {
        "s1::system": {
            "hostname": "s1", 
            "lastCheckIn": "2023-06-14 20:51:01", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwXPort": "6448", 
            "version": "8.0.32"
        }, 
        "s2::system": {
            "hostname": "s2", 
            "lastCheckIn": "2023-06-14 20:51:01", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwXPort": "6448", 
            "version": "8.0.32"
        }
    }
}

如果某个应用服务器下线了，该服务器上的 MySQL Router 实例对应的信息不会自动从 InnoDB Cluster 的元数据中删除，可以使用 cluster.removeRouterMetadata('routerName') 进行删除。

 MySQL  s1:3306 ssl  JS > cluster.removeRouterMetadata('s2::system');
 MySQL  s1:3306 ssl  JS > cluster.listRouters();
{
    "clusterName": "cluster1", 
    "routers": {
        "s1::system": {
            "hostname": "s1", 
            "lastCheckIn": "2023-06-14 21:02:45", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwXPort": "6448", 
            "version": "8.0.32"
        }
    }
}