MySQL 5.7使用MySQL Shell部署生产环境InnoDB集群

生产环境部署InnoDB集群
当在生产环境中工作时,组成InnoDB集群的MySQL服务器实例在多台主机上作为网络的一部分运行,而不是在单台机器上。在执行这些步骤之前 ,用户必须为每台作为服务器实例添加到集群中的机器安装所需的软件。

与沙盒部署不同的是,所有的实例都部署在本地的一台机器上,在生产部署中,你必须连接到每台机器并在本地运行MySQL Shell,然后在实例 上执行dba.configureLocalInstance()。这确保了任何配置更改都持久化到实例的mysql.cfg文件中。这还要求用户有访问服务器的权限和执 行MySQL Shell所需的权限。

用户权限
用于管理实例的用户帐户不一定是root帐户,但是除了MySQL管理员的权限(SUPER、GRANT OPTION、CREATE、DROP等)之外,还需要给用户分配 InnoDB集群元数据表的完全读写权限。给用户ic权限来管理InnoDB集群问题:

mysql> create user 'ic'@'%' identified by "123456";
Query OK, 0 rows affected (1.31 sec)

mysql> GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO ic@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT,CREATE USER ON *.* TO ic@'%' WITH  GRANT OPTION;
Query OK, 0 rows affected (1.00 sec)


mysql> GRANT SELECT ON performance_schema.* TO ic@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON *.* TO ic@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO ic@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO ic@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

如果只需要读操作(例如用于监控),则可以使用具有更多限制权限的帐户。给用户your_user权限来监控InnoDB集群问题:

mysql> GRANT SELECT ON performance_schema.global_status TO ic@'%';
Query OK, 0 rows affected (0.08 sec)

mysql> GRANT SELECT ON performance_schema.replication_applier_configuration TO ic@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON performance_schema.replication_applier_status TO ic@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO ic@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO ic@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON performance_schema.replication_connection_configuration TO ic@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON performance_schema.replication_connection_status TO ic@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON performance_schema.replication_group_member_stats TO ic@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON performance_schema.replication_group_members TO ic@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;


Query OK, 0 rows affected (0.07 sec)

对于每个实例重复上面的操作

配置主机名
组成集群的生产实例在不同的机器上运行,因此每台机器必须有唯一的主机名,并且能够解析集群中运行服务器实例的其他机器的主机名。如果 不是这样,你可以:
.配置每台机器,将每台机器的IP映射到主机名。详情请参阅操作系统文档。这是推荐的解决方案。
.设置DNS服务
.在每个实例的MySQL配置中配置report_host变量

下面对三台服务器进行配置

[root@localhost ~]# hostnamectl set-hostname mic1
[root@mic1 ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.18.13.238 mic3
10.18.13.237 mic2
10.18.13.236 mic1


[root@localhost ~]# hostnamectl set-hostname mic2
[root@mic2 ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.18.13.238 mic3
10.18.13.237 mic2
10.18.13.236 mic1


[root@localhost mysql]# hostnamectl set-hostname mic3
[root@localhost mysql]#
Last login: Fri Mar 28 09:43:16 2025 from 10.18.13.242

[root@mic3 ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.18.13.238 mic3
10.18.13.237 mic2
10.18.13.236 mic1

[root@mic1 ~]# ping mic3
PING mic3 (10.18.13.238) 56(84) bytes of data.
64 bytes from mic3 (10.18.13.238): icmp_seq=1 ttl=64 time=0.830 ms
64 bytes from mic3 (10.18.13.238): icmp_seq=2 ttl=64 time=0.418 ms
^C
--- mic3 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.418/0.624/0.830/0.206 ms
[root@mic1 ~]# ping mic2
PING mic2 (10.18.13.237) 56(84) bytes of data.
64 bytes from mic2 (10.18.13.237): icmp_seq=1 ttl=64 time=0.473 ms
64 bytes from mic2 (10.18.13.237): icmp_seq=2 ttl=64 time=0.307 ms
64 bytes from mic2 (10.18.13.237): icmp_seq=3 ttl=64 time=0.300 ms
^C
--- mic2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2000ms
rtt min/avg/max/mdev = 0.300/0.360/0.473/0.079 ms
[root@mic1 ~]# ping mic1
PING mic1 (10.18.13.236) 56(84) bytes of data.
64 bytes from mic1 (10.18.13.236): icmp_seq=1 ttl=64 time=0.097 ms
64 bytes from mic1 (10.18.13.236): icmp_seq=2 ttl=64 time=0.062 ms
^C
--- mic1 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 999ms
rtt min/avg/max/mdev = 0.062/0.079/0.097/0.019 ms

[mysql@mic1 mysql]$ vi my.cnf
report_host=10.18.13.236
[mysql@mic2 mysql]$ vi my.cnf
report_host=10.18.13.237
[mysql@mic3 mysql]$ vi my.cnf
report_host=10.18.13.238

[root@mic1 /]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!

[root@mic2 /]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!

[root@mic3 /]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!

详细日志
当使用生产部署时,为MySQL Shell配置详细的日志记录是很有用的,日志中的信息可以帮助您发现和解决在准备服务器实例作为InnoDB集群的 一部分时可能发生的任何问题。要用详细的日志级别启动MySQL Shell,使用–log-level选项:

[root@mic1 /]# mysqlsh --log-level=DEBUG3
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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.
 MySQL  JS >

建议使用DEBUG3,更多信息请参见–log-level。当设置了DEBUG3时,MySQL Shell日志文件包含这样的行:Debug: execute_sql(…)其中包含 作为每个AdminAPI调用的一部分执行的SQL查询。MySQL Shell生成的日志文件在unix系统中位于~/.mysqlsh/mysqlsh.log;在Microsoft Windows系统中,它位于%APPDATA%\MySQL\mysqlsh\mysqlsh.log中。

除了启用MySQL Shell日志级别之外,您还可以配置AdminAPI在MySQL Shell中每次调用API后提供的输出数量。要启用AdminAPI输出的数量,请 在MySQL Shell中发出:

 MySQL  JS > dba.verbose=2
2
 MySQL  JS >

这将启用AdminAPI调用的最大输出。可用的输出级别如下:
.默认值为0或OFF。这提供了最小输出,在不进行故障排除时是推荐的级别。

.1或ON添加每次对AdminAPI调用的详细输出。

.2在详细输出中添加调试输出,提供每次调用AdminAPI执行的全部信息。

检查实例配置
在服务器实例创建生产部署之前,需要使用dba.checkInstanceConfiguration()函数检查每个实例上的MySQL是否正确配置。下面演示了如何 在MySQL Shell中执行这个命令:

 MySQL  JS > dba.checkInstanceConfiguration('ic@mic1:3306')
Please provide the password for 'ic@mic1:3306': ******
Save password for 'ic@mic1:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
ERROR: The account 'ic'@'%' is missing privileges required to manage an InnoDB cluster:
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 'ic'@'%' 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 'ic'@'%' WITH GRANT  OPTION;
For more information, see the online documentation.
Dba.checkInstanceConfiguration: The account 'ic'@'%' is missing privileges required to manage an InnoDB cluster.  (RuntimeError)

上面提示缺少相关权限,在三个实例上执行上面的授权语句:

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

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

授权后再次执行检测实例的命令

 MySQL  JS > dba.checkInstanceConfiguration('ic@mic1:3306')
Please provide the password for 'ic@mic1:3306': ******
Save password for 'ic@mic1:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.236:3306

Checking whether existing tables comply with Group Replication requirements...
ERROR: The following tables do not have a Primary Key or equivalent column:
jycs.t_cs

Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables  that do not follow these requirements will be readable but not updateable when used with Group Replication. If your  applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a  PRIMARY KEY or PRIMARY KEY Equivalent.
If you can't change the tables structure to include an extra visible key to be used as PRIMARY KEY, you can make use of the  INVISIBLE COLUMN feature available since 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

Checking instance configuration...

NOTE: Some configuration options need to be fixed:
+----------------------------------+---------------+----------------+------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                           |
+----------------------------------+---------------+----------------+------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable and the config file |
| binlog_format                    | MIXED         | ROW            | Update the server variable and the config file |
| enforce_gtid_consistency         | OFF           | ON             | Update the config file and restart the server  |
| gtid_mode                        | OFF           | ON             | Update the config file and restart the server  |
| log_slave_updates                | OFF           | ON             | Update the config file and restart the server  |
| transaction_write_set_extraction | OFF           | XXHASH64       | Update the config file and restart the server  |
+----------------------------------+---------------+----------------+------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server: an option file is required.
NOTE: Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update+config_update",
            "current": "CRC32",
            "option": "binlog_checksum",
            "required": "NONE"
        },
        {
            "action": "server_update+config_update",
            "current": "MIXED",
            "option": "binlog_format",
            "required": "ROW"
        },
        {
            "action": "config_update+restart",
            "current": "OFF",
            "option": "enforce_gtid_consistency",
            "required": "ON"
        },
        {
            "action": "config_update+restart",
            "current": "OFF",
            "option": "gtid_mode",
            "required": "ON"
        },
        {
            "action": "config_update+restart",
            "current": "OFF",
            "option": "log_slave_updates",
            "required": "ON"
        },
        {
            "action": "config_update+restart",
            "current": "OFF",
            "option": "transaction_write_set_extraction",
            "required": "XXHASH64"
        }
    ],
    "status": "error"
}
 MySQL  JS >

检查实例给出了要修改的参数,下面对每个实例修改相关参数

[mysql@mic1 ~]$ vi /mysqlsoft/mysql/my.cnf
binlog_checksum=NONE
binlog_format = row
enforce_gtid_consistency=ON
gtid_mode=ON
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64

[mysql@mic2 ~]$ vi /mysqlsoft/mysql/my.cnf
binlog_checksum=NONE
binlog_format = row
enforce_gtid_consistency=ON
gtid_mode=ON
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64


[mysql@mic3 ~]$ vi /mysqlsoft/mysql/my.cnf
binlog_checksum=NONE
binlog_format = row
enforce_gtid_consistency=ON
gtid_mode=ON
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64

[root@mic1 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!


[root@mic2 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!


[root@mic3 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!

再次执行检测实例命令

 MySQL  JS > dba.checkInstanceConfiguration('ic@mic1:3306')
Please provide the password for 'ic@mic1:3306': ******
Save password for 'ic@mic1:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.236:3306

Checking whether existing tables comply with Group Replication requirements...
ERROR: The following tables do not have a Primary Key or equivalent column:
jycs.t_cs

Group Replication requires tables to use InnoDB and have a PRIMARY KEY or PRIMARY KEY Equivalent (non-null unique key). Tables  that do not follow these requirements will be readable but not updateable when used with Group Replication. If your  applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a  PRIMARY KEY or PRIMARY KEY Equivalent.
If you can't change the tables structure to include an extra visible key to be used as PRIMARY KEY, you can make use of the  INVISIBLE COLUMN feature available since 8.0.23: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

Checking instance configuration...

{
    "status": "error"
}

上面提示是说jycs.t_cs表没有主键,下面对jycs._t_cs表添加主键

mysql> ALTER TABLE jycs.t_cs MODIFY COLUMN id INT PRIMARY KEY;
Query OK, 0 rows affected (1.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次执行检测实例命令

 MySQL  JS > dba.checkInstanceConfiguration('ic@mic1:3306')
Please provide the password for 'ic@mic1:3306': ******
Save password for 'ic@mic1:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.236:3306

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 '10.18.13.236:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

检测通过,下面对另外两个实例执行检测命令

[root@mic2 ~]# mysqlsh --log-level=DEBUG3
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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.
 MySQL  JS > dba.checkInstanceConfiguration('ic@mic2:3306')
Please provide the password for 'ic@mic2:3306': ******
Save password for 'ic@mic2:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.237:3306

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 '10.18.13.237:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

[root@mic3 ~]# mysqlsh --log-level=DEBUG3
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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.
 MySQL  JS > dba.checkInstanceConfiguration('ic@mic3:3306')
Please provide the password for 'ic@mic3:3306': ******
Save password for 'ic@mic3:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.238:3306

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 '10.18.13.238:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

检测全部通过。

配置实例
如果在针对实例运行dba.checkInstanceConfiguration()生成的报告中发现了配置问题,请连接到计算机并重新配置服务器实例。AdminAPI提 供了dba.configureLocalInstance()函数来查找MySQL服务器的配置文件并修改它,以确保该实例是正确配置InnoDB集群的。或者,根据报告 中的信息手动更改实例的选项文件。无论您以何种方式更改配置,都可能需要重新启动MySQL以确保检测到配置更改。

推荐的方法是登录远程机器,以root用户运行MySQL Shell,然后连接到本地MySQL服务器:

[root@mic1 ~]# mysqlsh --log-level=DEBUG3

dba.configureLocalInstance()方法验证一个适合集群使用的用户,该用户用于集群成员之间的连接。默认情况下,root用户不能进行远程登 录,因此您有三个选项来继续配置:为root用户启用远程连接、创建新用户或以上两个选项都不使用。下面的示例演示了第二个选项,即为集群 创建一个新用户。

 MySQL  JS > dba.configureLocalInstance('ic@mic1:3306')
WARNING: This function is deprecated and will be removed in a future release of MySQL Shell, use dba.configureInstance()  instead.
Please provide the password for 'ic@mic1:3306': ******
Save password for 'ic@mic1:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.236:3306

The instance '10.18.13.236:3306' is valid to be used in an InnoDB cluster.
The instance '10.18.13.236:3306' is already ready to be used in an InnoDB cluster.


 MySQL  JS > dba.configureLocalInstance('ic@mic2:3306')
WARNING: This function is deprecated and will be removed in a future release of MySQL Shell, use dba.configureInstance()  instead.
Please provide the password for 'ic@mic2:3306': ******
Save password for 'ic@mic2:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.237:3306

The instance '10.18.13.237:3306' is valid to be used in an InnoDB cluster.
The instance '10.18.13.237:3306' is already ready to be used in an InnoDB cluster.




 MySQL  JS > dba.configureLocalInstance('ic@mic3:3306')
WARNING: This function is deprecated and will be removed in a future release of MySQL Shell, use dba.configureInstance()  instead.
Please provide the password for 'ic@mic3:3306': ******
Save password for 'ic@mic3:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.238:3306

The instance '10.18.13.238:3306' is valid to be used in an InnoDB cluster.
The instance '10.18.13.238:3306' is already ready to be used in an InnoDB cluster.

创建群集
准备好实例后,请使用dba。createCluster函数,用于创建集群。运行MySQL Shell的机器被用作集群的种子实例。seed实例会被复制到你添加 到集群中的其他实例上,使它们成为seed实例的副本。登录实例并在本地运行MySQL Shell。

[root@mic1 /]# mysqlsh --uri ic@mic1:3306
Please provide the password for 'ic@mic1:3306': ******
Save password for 'ic@mic1:3306'? [Y]es/[N]o/Ne[v]er (default No):
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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 'ic@mic1:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 8
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  mic1:3306  JS >

使用dba.createCluster(name)函数创建集群,并将返回的集群分配给一个名为cluster的变量:

 MySQL  mic1:3306  JS > var cluster = dba.createCluster('prodCluster')
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
A new InnoDB Cluster will be created on instance '10.18.13.236:3306'.

Validating instance configuration at mic1:3306...

This instance reports its own address as 10.18.13.236:3306

Instance configuration is suitable.
NOTE: TLS not available at '10.18.13.236:3306', assuming memberSslMode to be DISABLED
NOTE: Group Replication will communicate with other members using '10.18.13.236:33061'. Use the localAddress option to  override.

* Checking connectivity and SSL configuration...

WARNING: Instance '10.18.13.236:3306' cannot persist Group Replication configuration since MySQL version 5.7.26 does not  support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command  locally to persist the changes.
Creating InnoDB Cluster 'prodCluster' on '10.18.13.236: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.

 MySQL  mic1:3306  JS >

如果遇到与元数据不可访问相关的错误,可能是配置了环回网络接口。为了保证InnoDB集群的正确使用,请关闭环回接口。

要检查集群是否已创建,请使用集群实例的status()函数

 MySQL  mic1:3306  JS > cluster.status()
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
{
    "clusterName": "prodCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "10.18.13.236:3306",
        "ssl": "DISABLED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "10.18.13.236:3306": {
                "address": "10.18.13.236:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "5.7.26"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "10.18.13.236:3306"
}
 MySQL  mic1:3306  JS >

一旦服务器实例属于一个集群,重要的是只使用MySQL Shell和AdminAPI管理它们。不支持在实例添加到集群后手动更改组复制的配置。类似地 ,不支持在AdminAPI配置实例后修改对InnoDB集群至关重要的服务器变量,如server_uuid。

使用cluster. addinstance (instance)函数向集群添加更多实例,其中instance是连接到本地实例的URI类型字符串。这些实例必须已经配置 为集群使用。集群中至少需要3个实例才能容忍其中一个实例的故障。添加更多的实例可以提高对实例失败的容忍度。添加一个实例到集群问题 :


 MySQL  mic1:3306  JS > cluster.addInstance('ic@mic2:3306');
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
ERROR: Unable to connect to the target instance 'mic2:3306'. Please verify the connection settings, make sure the instance is  available and try again.
Cluster.addInstance: Could not open connection to 'mic2:3306': Can't connect to MySQL server on 'mic2:3306' (113) (MySQL Error  2003)
 MySQL  mic1:3306  JS > cluster.addInstance('ic@mic2:3306');
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely  overwrite the state of '10.18.13.237: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 mic2:3306...

This instance reports its own address as 10.18.13.237:3306

Instance configuration is suitable.
ERROR: RuntimeError: Cannot add an instance with the same server UUID (684e1f7d-6f47-11ef-a6d5-005056a3a162) of an active  member of the cluster '10.18.13.236:3306'. Please change the server UUID of the instance to add, all members must have a  unique server UUID.
Cluster.addInstance: Cannot add an instance with the same server UUID (684e1f7d-6f47-11ef-a6d5-005056a3a162) of an active  member of the cluster '10.18.13.236:3306'. Please change the server UUID of the instance to add, all members must have a  unique server UUID. (RuntimeError)

在${data_dir}/auto.cnf中的server-uuid值是一样的,要处理该问题只需要删除auto.cnf文件,就会自动创建和生成新的server-uuid值。

[root@mic2 mysql]# cat auto.cnf
[auto]
server-uuid=684e1f7d-6f47-11ef-a6d5-005056a3a162
[root@mic2 mysql]# rm -rf auto.cnf
[root@mic2 mysql]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[root@mic2 mysql]# cat auto.cnf
[auto]
server-uuid=c9f76f9e-0de1-11f0-9924-005056b9de82


[root@mic3 mysql]# rm -rf auto.cnf
[root@mic3 mysql]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[root@mic3 mysql]# cat auto.cnf
[auto]
server-uuid=ccdd7fe7-0de1-11f0-9436-005056b9890f

 MySQL  mic1:3306  JS > cluster.addInstance('ic@mic2:3306');
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely  overwrite the state of '10.18.13.237: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 mic2:3306...

This instance reports its own address as 10.18.13.237:3306

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

* Checking connectivity and SSL configuration...
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.

WARNING: Instance '10.18.13.237:3306' cannot persist Group Replication configuration since MySQL version 5.7.26 does not  support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command  locally to persist the changes.
Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: '10.18.13.237:3306' is being recovered from '10.18.13.236:3306'
* Distributed recovery has finished

WARNING: Instance '10.18.13.236:3306' cannot persist configuration since MySQL version 5.7.26 does not support the SET  PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the  changes.
The instance '10.18.13.237:3306' was successfully added to the cluster.

 MySQL  mic1:3306  JS > cluster.addInstance('ic@mic3:3306');
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely  overwrite the state of '10.18.13.238: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 mic3:3306...

This instance reports its own address as 10.18.13.238:3306

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

* Checking connectivity and SSL configuration...
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.

WARNING: Instance '10.18.13.238:3306' cannot persist Group Replication configuration since MySQL version 5.7.26 does not  support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command  locally to persist the changes.
Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: '10.18.13.238:3306' is being recovered from '10.18.13.236:3306'
* Distributed recovery has finished

WARNING: Instance '10.18.13.236:3306' cannot persist configuration since MySQL version 5.7.26 does not support the SET  PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the  changes.
WARNING: Instance '10.18.13.237:3306' cannot persist configuration since MySQL version 5.7.26 does not support the SET  PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the  changes.
The instance '10.18.13.238:3306' was successfully added to the cluster.

在这个阶段,服务器实例已经被添加到集群中,但是对InnoDB集群元数据的更改只在您当前连接的实例上进行。为了让集群中所有实例的配置更 改持久化,用户必须连接到每个实例,并在添加的每个实例上本地运行dba.configureLocalInstance()。这对于确保实例在离开集群时重新加 入集群是至关重要的。

要将InnoDB集群的元数据持久化到所有的实例中,请登录到你添加到集群中的每个实例,并在本地运行MySQL Shell。

  MySQL  mic1:3306  JS > dba.checkInstanceConfiguration('ic@mic1:3306')
Please provide the password for 'ic@mic1:3306': ******
Save password for 'ic@mic1:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.236:3306

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 '10.18.13.236:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}
 MySQL  mic1:3306  JS >

[root@mic2 ~]# mysqlsh --log-level=DEBUG3
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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.
 MySQL  JS > dba.checkInstanceConfiguration('ic@mic2:3306')
Please provide the password for 'ic@mic2:3306': ******
Save password for 'ic@mic2:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.237:3306

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 '10.18.13.237:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}




[root@mic3 ~]# mysqlsh --log-level=DEBUG3
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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.
 MySQL  JS > dba.checkInstanceConfiguration('ic@mic3:3306')
Please provide the password for 'ic@mic3:3306': ******
Save password for 'ic@mic3:3306'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 10.18.13.238:3306

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 '10.18.13.238:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

对添加到集群中的每个服务器实例重复此过程。类似地,如果你修改了集群的结构,例如改变了实例的数量,你需要对每个服务器实例重复这个 过程,以相应地更新集群中每个实例的InnoDB集群元数据。

要检查集群是否已创建,请使用集群实例的status()函数。

 MySQL  mic1:3306  JS > cluster.status()
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
{
    "clusterName": "prodCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "10.18.13.236:3306",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "10.18.13.236:3306": {
                "address": "10.18.13.236:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "5.7.26"
            },
            "10.18.13.237:3306": {
                "address": "10.18.13.237:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "5.7.26"
            },
            "10.18.13.238:3306": {
                "address": "10.18.13.238:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "5.7.26"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "10.18.13.236:3306"
}

下面来测试一个集群的同步功能
在主库上向jycs库的t_cs表插入记录

mysql> insert into t_cs values(6,'hi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_cs;
+----+------+
| id | name |
+----+------+
|  1 | cs   |
|  2 | ab   |
|  3 | cd   |
|  4 | ef   |
|  5 | gh   |
|  6 | hi   |
+----+------+
6 rows in set (0.00 sec)

查看第二、第三个实例是否已经同步

 MySQL  mic2:3306  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  mic2:3306  SQL > select * from jycs.t_cs;
+----+------+
| id | name |
+----+------+
|  1 | cs   |
|  2 | ab   |
|  3 | cd   |
|  4 | ef   |
|  5 | gh   |
|  6 | hi   |
+----+------+
6 rows in set (0.0006 sec)
 MySQL  mic2:3306  SQL >


 MySQL  mic3:3306  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  mic3:3306  SQL > select * from jycs.t_cs;
+----+------+
| id | name |
+----+------+
|  1 | cs   |
|  2 | ab   |
|  3 | cd   |
|  4 | ef   |
|  5 | gh   |
|  6 | hi   |
+----+------+
6 rows in set (0.0008 sec)
 MySQL  mic3:3306  SQL >

至此使用MySQL Shell部署生产环境InnoDB集群完成。

MySQL 5.7使用MySQL Shell 部署沙箱InnoDB集群

InnoDB集群
MySQL InnoDB集群为MySQL提供了一个完整的高可用解决方案。MySQL Shell包含AdminAPI,它允许您轻松配置和管理一组至少三个MySQL服务器 实例,以发挥InnoDB集群的功能。每个MySQL server实例运行MySQL Group Replication,它提供了在InnoDB集群中复制数据的机制,具有内置 的故障转移。AdminAPI消除了在InnoDB集群中直接使用组复制的需要,但更多信息请参见第17章,其中详细说明了组复制。MySQL Router可以根 据你部署的集群自动配置自己,透明地将客户端应用程序连接到服务器实例。在服务器实例出现意外故障时,集群将自动重新配置。在默认的单 主模式下,一个InnoDB集群有一个读写服务器实例–主。多个辅助服务器实例是主服务器的副本。当主节点故障时,从节点自动升级为主节点 。MySQL路由器检测到这一点并将客户端应用程序转发给新的主服务器。高级用户还可以配置一个集群以拥有多个主用服务器。

使用AdminAPI
MySQL Shell包含AdminAPI,可以通过dba全局变量及其相关方法访问。dba变量的方法使你能够部署、配置和管理InnoDB集群。例如,使用 dba.createCluster()方法创建一个InnoDB集群。MySQL Shell为AdminAPI提供在线帮助。要列出所有可用的dba命令,请使用dba.help()方法。要获得关于特定方法的在线帮助,请使用通用格 式object.help(’methodname’)。例如:

 MySQL  localhost:33060+  world_x  JS > dba.help('getCluster')
NAME
      getCluster - Returns an object representing a Cluster.

SYNTAX
      dba.getCluster([name][, options])

WHERE
      name: Parameter to specify the name of the Cluster to be returned.
      options: Dictionary with additional options.

RETURNS
      The Cluster object identified by the given name or the default Cluster.

DESCRIPTION
      If name is not specified or is null, the default Cluster will be
      returned.

      If name is specified, and no Cluster with the indicated name is found, an
      error will be raised.

      The options dictionary may contain the following attributes:

      - connectToPrimary: boolean value used to indicate if Shell should
        automatically connect to the primary member of the Cluster or not.
        Deprecated and ignored, Shell will attempt to connect to the primary by
        default and fallback to a secondary when not possible.


创建InnoDB集群
这里将介绍创建InnoDB集群的不同方法,对服务器实例的要求以及部署集群需要安装的软件。

部署场景
InnoDB集群支持以下部署场景:
.沙箱部署:如果你想在部署到生产环境之前测试一下InnoDB集群,它提供的沙盒特性可以让你在本地机器上快速设置集群。沙盒服务器实例是根 据需要配置创建的,你可以尝试使用InnoDB集群来熟悉所使用的技术

.生产环境部署:如果你想在一个完整的生产环境中使用InnoDB集群,你需要配置所需的机器数量,然后将服务器实例部署到这些机器上。生产环 境的部署可以让你充分利用InnoDB集群的高可用性特性。

InnoDB集群要求
在安装InnoDB集群的生产部署之前,请确保您打算使用的服务器实例满足以下要求。
.InnoDB集群使用组复制,因此您的服务器实例必须满足相同的要求。AdminAPI提供dba.checkInstanceConfiguration()方法来验证实例是否 满足组复制要求,并提供dba.configureLocalInstance()方法来配置实例以满足要求。
 MySQL  localhost:33060+  world_x  JS > dba.checkInstanceConfiguration()
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as mysqlcs: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 'mysqlcs:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}


[root@mysqlcs ~]# mysqlsh root@localhost:3307/
Please provide the password for 'root@localhost:3307': ******
Save password for 'root@localhost:3307'? [Y]es/[N]o/Ne[v]er (default No): n
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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:3307'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 26
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3307  JS > dba.checkInstanceConfiguration()
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3307 for use in an InnoDB cluster...

This instance reports its own address as mysqlcs:3307
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 'mysqlcs:3307' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}


[root@mysqlcs ~]# mysqlsh root@localhost:3308/
Please provide the password for 'root@localhost:3308': ******
Save password for 'root@localhost:3308'? [Y]es/[N]o/Ne[v]er (default No): n
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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:3308'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 22
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3308  JS > dba.checkInstanceConfiguration()
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
Validating local MySQL instance listening at port 3308 for use in an InnoDB cluster...

This instance reports its own address as mysqlcs:3308
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 'mysqlcs:3308' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

当使用沙盒部署时,实例被配置为自动满足这些需求。

。MySQL Shell用于在InnoDB集群中配置服务器的配置脚本需要使用Python 2.7版本。对于沙盒部署,需要在部署所用的单机上安装Python,而 生产环境部署则需要在每个服务器实例上安装Python
在Windows上,MySQL Shell包含Python,不需要用户配置。在Unix中,Python必须是shell环境的一部分。要检查系统是否正确配置了Python, 请执行以下命令:

[root@mysqlcs ~]# /usr/bin/env python
Python 2.7.5 (default, Nov  1 2018, 03:12:47)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-36.0.1)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> exit()
[root@mysqlcs ~]#

如果Python解释器启动,则不需要进一步操作。如果前面的命令失败,在/usr/bin/python和您选择的Python二进制文件之间创建一个软链接。

安装方法
安装InnoDB集群的方法取决于你打算使用的部署类型。对于沙盒部署,将InnoDB集群的组件安装到单机上。沙盒部署是在一台机器上进行的,因 此只需要在本地机器上安装一次。类似地,也不需要单独连接到实例进行配置,沙盒实例是本地的。对于生产环境部署,要将组件安装到要添加 到集群的每台机器上。生产部署使用多台远程主机运行MySQL server实例,因此需要使用SSH或Windows remote desktop等工具连接到每台机器 ,以执行安装组件和配置server实例等任务。InnoDB集群的安装方法如下:

.使用以下文档下载和安装组件
.MySQL服务器-参见第2章,安装和升级MySQL
.MySQL Shell -参见第19.3.1节“安装MySQL Shell”。
.MySQL路由器-参见安装MySQL路由器

.在Windows上,你可以使用MySQL安装程序进行沙盒部署

InnoDB集群的沙盒部署
这里介绍如何设置沙盒环境下的InnoDB集群部署。你可以使用MySQL Shell和AdminAPI来创建和管理你的InnoDB集群。

最初部署和使用MySQL的本地沙箱实例是探索InnoDB集群的好方法。在部署到生产服务器之前,你可以在本地完全测试InnoDB集群。MySQL Shell 内置了创建沙盒实例的功能,这些沙盒实例经过正确配置,可以在本地部署的场景中使用组复制。

沙盒实例只适合在本地机器上部署和运行,用于测试。在生产环境中,MySQL Server实例部署在网络上的各种主机上。

本教程展示了如何使用MySQL Shell创建一个由三个MySQL服务器实例组成的InnoDB集群。
.部署沙盒实例
.创建InnoDB沙箱集群
.向InnoDB集群添加实例
.持久化配置

部署沙盒实例
MySQL Shell包含添加dba全局变量的AdminAPI,它提供了管理沙盒实例的函数。在这个示例设置中,使用dba.deploySandboxInstance()创建 了三个沙盒实例。

通过执行下面的命令,从命令提示符启动MySQL Shell:

[root@localhost /]# mysqlsh
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, 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.
 MySQL  JS >

MySQL Shell除了原生的SQL模式外,还提供了JavaScript和Python两种脚本语言模式。在本指南中,MySQL Shell主要用于JavaScript模式。 MySQL Shell启动时默认是JavaScript模式。通过执行\js命令切换JavaScript模式,执行\py命令切换Python模式,执行\sql命令切换sql模式。 执行\js命令,确保你处于JavaScript模式,然后执行:

 MySQL  JS > dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3310

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ******

Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310') to connect to the instance.

 MySQL  JS >

要部署更多的沙盒服务器实例,请重复上述步骤,并选择不同的端口号。对于每个额外的沙盒实例执行:

 MySQL  JS > dba.deploySandboxInstance(3320)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3320

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ******

Deploying new MySQL instance...

Instance localhost:3320 successfully deployed and started.
Use shell.connect('root@localhost:3320') to connect to the instance.

 MySQL  JS > dba.deploySandboxInstance(3330)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3330

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ******

Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.
Use shell.connect('root@localhost:3330') to connect to the instance.

提示输入实例的root密码。


[root@localhost ~]# mysql -h 10.18.13.239 -P 3310 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@localhost /]# mysql -h 10.18.13.239 -P 3320 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.25 sec)

mysql> exit
Bye
[root@localhost /]# mysql -h 10.18.13.239 -P 3330 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

在JavaScript和Python模式中不需要用分号结束命令。

传递给deploySandboxInstance()的参数是MySQL服务器监听连接的TCP端口号。默认情况下,沙盒在Unix系统中创建在$HOME/mysqlsandboxes/ port目录下。对于Microsoft Windows系统,该目录是%userprofile% \MySQL\ MySQL -sandboxes\port。

[root@localhost ~]# ls -lrt
total 24
-rw-r--r--. 1 root root 2174 Oct 11  2017 initial-setup-ks.cfg
-rw-------. 1 root root 2126 Oct 12  2017 anaconda-ks.cfg
drwxr-xr-x. 2 root root    6 Jun 10  2019 Videos
drwxr-xr-x. 2 root root    6 Jun 10  2019 Templates
drwxr-xr-x. 2 root root    6 Jun 10  2019 Public
drwxr-xr-x. 2 root root    6 Jun 10  2019 Pictures
drwxr-xr-x. 2 root root    6 Jun 10  2019 Music
drwxr-xr-x. 2 root root    6 Jun 10  2019 Downloads
drwxr-xr-x. 2 root root    6 Jun 10  2019 Documents
drwxr-xr-x. 2 root root    6 Jun 10  2019 Desktop
drwxr-xr-x. 2 root root    6 Jun 17  2019 perl5
-rw-r--r--. 1 root root 1069 Aug 19  2020 viewmemory.c
-rwxrwxrwx. 1 root root 8784 Aug 19  2020 viewmemory
drwxr-xr-x. 3 root root   17 Mar 26 15:35 mysql-sandboxes


[root@localhost mysql-sandboxes]# pwd
/root/mysql-sandboxes
[root@localhost mysql-sandboxes]# ll
total 12
drwxr-xr-x. 5 root root 4096 Mar 26 15:35 3310
drwxr-xr-x. 5 root root 4096 Mar 26 15:48 3320
drwxr-xr-x. 5 root root 4096 Mar 26 15:48 3330

每个实例都有自己的密码。在本教程中,为所有沙盒定义相同的密码会使其更容易,但请记住在生产部署中为每个实例使用不同的密码。
要记住一点,一定要删除ssl相关的文件

 MySQL  JS > dba.stopSandboxInstance(3310)
The MySQL sandbox instance on this host in
3320 will be stopped

Please enter the MySQL root password for the instance 'localhost:3320': ******

Stopping MySQL instance...

Instance localhost:3310 successfully stopped.
 MySQL  JS > dba.stopSandboxInstance(3320)
The MySQL sandbox instance on this host in
3320 will be stopped

Please enter the MySQL root password for the instance 'localhost:3320': ******

Stopping MySQL instance...

Instance localhost:3320 successfully stopped.

 MySQL  JS > dba.stopSandboxInstance(3330)
The MySQL sandbox instance on this host in
3330 will be stopped

Please enter the MySQL root password for the instance 'localhost:3330': ******

Stopping MySQL instance...

Instance localhost:3330 successfully stopped.

 MySQL  JS > dba.startSandboxInstance(3330)

Starting MySQL instance...

Instance localhost:3330 successfully started.

 MySQL  JS > dba.startSandboxInstance(3320)

Starting MySQL instance...

Instance localhost:3320 successfully started.

[root@localhost mysql-sandboxes]# cd 3310/sandboxdata/
[root@localhost sandboxdata]# ll
total 110684
-rw-r-----. 1 root root       56 Mar 27 11:27 auto.cnf
-rw-------. 1 root root     1675 Mar 27 11:28 ca-key.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 ca.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 client-cert.pem
-rw-------. 1 root root     1675 Mar 27 11:28 client-key.pem
-rw-r-----. 1 root root    10335 Mar 27 11:37 error.log
-rw-r-----. 1 root root      356 Mar 27 11:37 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 27 11:37 ibdata1
-rw-r-----. 1 root root 50331648 Mar 27 11:37 ib_logfile0
-rw-r-----. 1 root root 50331648 Mar 27 11:27 ib_logfile1
-rw-r-----. 1 root root      169 Mar 27 11:27 localhost-bin.000001
-rw-r-----. 1 root root      169 Mar 27 11:37 localhost-bin.000002
-rw-r-----. 1 root root       46 Mar 27 11:28 localhost-bin.index
drwxr-x---. 2 root root     4096 Mar 27 11:27 mysql
drwxr-x---. 2 root root     8192 Mar 27 11:27 performance_schema
-rw-------. 1 root root     1679 Mar 27 11:28 private_key.pem
-rw-r--r--. 1 root root      451 Mar 27 11:28 public_key.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 server-cert.pem
-rw-------. 1 root root     1679 Mar 27 11:28 server-key.pem
drwxr-x---. 2 root root     8192 Mar 27 11:27 sys
[root@localhost sandboxdata]# rm -rf *pem
[root@localhost sandboxdata]# ll
total 110652
-rw-r-----. 1 root root       56 Mar 27 11:27 auto.cnf
-rw-r-----. 1 root root    10335 Mar 27 11:37 error.log
-rw-r-----. 1 root root      356 Mar 27 11:37 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 27 11:37 ibdata1
-rw-r-----. 1 root root 50331648 Mar 27 11:37 ib_logfile0
-rw-r-----. 1 root root 50331648 Mar 27 11:27 ib_logfile1
-rw-r-----. 1 root root      169 Mar 27 11:27 localhost-bin.000001
-rw-r-----. 1 root root      169 Mar 27 11:37 localhost-bin.000002
-rw-r-----. 1 root root       46 Mar 27 11:28 localhost-bin.index
drwxr-x---. 2 root root     4096 Mar 27 11:27 mysql
drwxr-x---. 2 root root     8192 Mar 27 11:27 performance_schema
drwxr-x---. 2 root root     8192 Mar 27 11:27 sys

[root@localhost mysql-sandboxes]# cd 3320/sandboxdata/
[root@localhost sandboxdata]# ll
total 110684
-rw-r-----. 1 root root       56 Mar 27 11:27 auto.cnf
-rw-------. 1 root root     1675 Mar 27 11:28 ca-key.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 ca.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 client-cert.pem
-rw-------. 1 root root     1675 Mar 27 11:28 client-key.pem
-rw-r-----. 1 root root    10335 Mar 27 11:37 error.log
-rw-r-----. 1 root root      356 Mar 27 11:37 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 27 11:37 ibdata1
-rw-r-----. 1 root root 50331648 Mar 27 11:37 ib_logfile0
-rw-r-----. 1 root root 50331648 Mar 27 11:27 ib_logfile1
-rw-r-----. 1 root root      169 Mar 27 11:27 localhost-bin.000001
-rw-r-----. 1 root root      169 Mar 27 11:37 localhost-bin.000002
-rw-r-----. 1 root root       46 Mar 27 11:28 localhost-bin.index
drwxr-x---. 2 root root     4096 Mar 27 11:27 mysql
drwxr-x---. 2 root root     8192 Mar 27 11:27 performance_schema
-rw-------. 1 root root     1679 Mar 27 11:28 private_key.pem
-rw-r--r--. 1 root root      451 Mar 27 11:28 public_key.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 server-cert.pem
-rw-------. 1 root root     1679 Mar 27 11:28 server-key.pem
drwxr-x---. 2 root root     8192 Mar 27 11:27 sys
[root@localhost sandboxdata]# rm -rf *pem
[root@localhost sandboxdata]# ll
total 110652
-rw-r-----. 1 root root       56 Mar 27 11:27 auto.cnf
-rw-r-----. 1 root root    10335 Mar 27 11:37 error.log
-rw-r-----. 1 root root      356 Mar 27 11:37 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 27 11:37 ibdata1
-rw-r-----. 1 root root 50331648 Mar 27 11:37 ib_logfile0
-rw-r-----. 1 root root 50331648 Mar 27 11:27 ib_logfile1
-rw-r-----. 1 root root      169 Mar 27 11:27 localhost-bin.000001
-rw-r-----. 1 root root      169 Mar 27 11:37 localhost-bin.000002
-rw-r-----. 1 root root       46 Mar 27 11:28 localhost-bin.index
drwxr-x---. 2 root root     4096 Mar 27 11:27 mysql
drwxr-x---. 2 root root     8192 Mar 27 11:27 performance_schema
drwxr-x---. 2 root root     8192 Mar 27 11:27 sys

[root@localhost mysql-sandboxes]# cd 3330/sandboxdata/
[root@localhost sandboxdata]# ll
total 110684
-rw-r-----. 1 root root       56 Mar 27 11:27 auto.cnf
-rw-------. 1 root root     1675 Mar 27 11:28 ca-key.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 ca.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 client-cert.pem
-rw-------. 1 root root     1675 Mar 27 11:28 client-key.pem
-rw-r-----. 1 root root    10335 Mar 27 11:37 error.log
-rw-r-----. 1 root root      356 Mar 27 11:37 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 27 11:37 ibdata1
-rw-r-----. 1 root root 50331648 Mar 27 11:37 ib_logfile0
-rw-r-----. 1 root root 50331648 Mar 27 11:27 ib_logfile1
-rw-r-----. 1 root root      169 Mar 27 11:27 localhost-bin.000001
-rw-r-----. 1 root root      169 Mar 27 11:37 localhost-bin.000002
-rw-r-----. 1 root root       46 Mar 27 11:28 localhost-bin.index
drwxr-x---. 2 root root     4096 Mar 27 11:27 mysql
drwxr-x---. 2 root root     8192 Mar 27 11:27 performance_schema
-rw-------. 1 root root     1679 Mar 27 11:28 private_key.pem
-rw-r--r--. 1 root root      451 Mar 27 11:28 public_key.pem
-rw-r--r--. 1 root root     1107 Mar 27 11:28 server-cert.pem
-rw-------. 1 root root     1679 Mar 27 11:28 server-key.pem
drwxr-x---. 2 root root     8192 Mar 27 11:27 sys
[root@localhost sandboxdata]# rm -rf *pem
[root@localhost sandboxdata]# ll
total 110652
-rw-r-----. 1 root root       56 Mar 27 11:27 auto.cnf
-rw-r-----. 1 root root    10335 Mar 27 11:37 error.log
-rw-r-----. 1 root root      356 Mar 27 11:37 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 27 11:37 ibdata1
-rw-r-----. 1 root root 50331648 Mar 27 11:37 ib_logfile0
-rw-r-----. 1 root root 50331648 Mar 27 11:27 ib_logfile1
-rw-r-----. 1 root root      169 Mar 27 11:27 localhost-bin.000001
-rw-r-----. 1 root root      169 Mar 27 11:37 localhost-bin.000002
-rw-r-----. 1 root root       46 Mar 27 11:28 localhost-bin.index
drwxr-x---. 2 root root     4096 Mar 27 11:27 mysql
drwxr-x---. 2 root root     8192 Mar 27 11:27 performance_schema
drwxr-x---. 2 root root     8192 Mar 27 11:27 sys

MySQL  JS > dba.startSandboxInstance(3330)

Starting MySQL instance...

Instance localhost:3330 successfully started.

 MySQL  JS > dba.startSandboxInstance(3320)

Starting MySQL instance...

Instance localhost:3320 successfully started.

 MySQL  JS > dba.startSandboxInstance(3310)

Starting MySQL instance...

Instance localhost:3320 successfully started.

创建沙箱InnoDB集群
下一步是创建InnoDB集群,同时连接到seed MySQL服务器实例。seed实例包含要复制到其他实例的数据。在这个例子中,沙盒实例是空的,因此 我们可以选择任何实例。

将MySQL Shell连接到种子实例,在本例中是端口3310的实例:

 MySQL  JS > \connect root@localhost:3310
Creating a session to 'root@localhost:3310'
Please provide the password for 'root@localhost:3310': ******
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 6
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3310  JS >

如果不禁用ssl就会出现如下错误:

 MySQL  JS > \connect root@localhost:3310
Creating a session to 'root@localhost:3310'
Please provide the password for 'root@localhost:3310': ******
MySQL Error 2026 (HY000): SSL connection error: error:14077102:SSL routines:SSL23_GET_SERVER_HELLO:unsupported protocol

上面报错信息是使用了SSL连接,协议不支持,下面使用–ssl-mode来禁用SSL连接。

 MySQL  JS > \connect root@localhost:3310 --ssl-mode=DISABLED
Creating a session to 'root@localhost:3310?ssl-mode=disabled'
Please provide the password for 'root@localhost:3310': ******
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No): n
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 13
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3310  JS >

\connect MySQL Shell命令是shell.connect()方法的快捷方式:

 MySQL  JS > shell.connect("root@localhost:3310?ssl-mode=DISABLED");
Creating a session to 'root@localhost:3310?ssl-mode=DISABLED'
Please provide the password for 'root@localhost:3310': ******
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No): n
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 15
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.

 MySQL  localhost:3310  JS >

连接之后,AdminAPI可以写入本地实例的选项文件。这与生产部署不同,在生产部署中,需要连接到远程实例并在实例上本地运行MySQL Shell 应用程序,然后AdminAPI才能写入实例的选项文件。

使用dba.createCluster()方法创建以当前连接实例为种子的InnoDB集群:

 MySQL  localhost:3310  JS > var cluster = dba.createCluster('testCluster')
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
A new InnoDB Cluster will be created on instance '127.0.0.1:3310'.

Validating instance configuration at localhost:3310...
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3310

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

* Checking connectivity and SSL configuration...

WARNING: Instance '127.0.0.1:3310' cannot persist Group Replication configuration since MySQL version 5.7.26 does not support  the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to  persist the changes.
Creating InnoDB Cluster 'testCluster' on '127.0.0.1:3310'...

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.

 MySQL  localhost:3310  JS >

createCluster()方法将InnoDB集群元数据部署到选中的实例上,并添加当前连接的实例作为种子实例。createCluster()方法返回创建的簇 ,在上面的例子中,this被赋值给cluster变量。传递给createCluster()方法的参数是这个InnoDB集群的符号名称,在本例中是testCluster 。

向InnoDB集群添加实例
下一步是向InnoDB集群添加更多的实例。在添加种子实例时,每个辅助实例都会重新执行由种子实例执行的任何事务。本教程使用之前在端口 3320和3330上创建的沙盒实例。

这个例子中的seed实例是最近创建的,所以它几乎是空的。因此,几乎没有数据需要从种子实例复制到辅助实例。在生产环境中,如果在seed实 例上有一个现有的数据库,可以使用MySQL Enterprise Backup之类的工具,在复制开始之前确保从服务器有匹配的数据。这避免了数据从主服 务器复制到辅助服务器时可能出现的长时间延迟。

添加第二个实例到InnoDB集群:

 MySQL  localhost:3310  JS > cluster.addInstance('root@localhost:3320')
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell

NOTE: The target instance '127.0.0.1:3320' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide  whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely  overwrite the state of '127.0.0.1:3320' 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'.


Please select a recovery method [I]ncremental recovery/[A]bort (default Incremental recovery):
Validating instance configuration at localhost:3320...
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3320

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

* Checking connectivity and SSL configuration...
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.

WARNING: Instance '127.0.0.1:3320' cannot persist Group Replication configuration since MySQL version 5.7.26 does not support  the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to  persist the changes.
Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: '127.0.0.1:3320' is being recovered from '127.0.0.1:3310'
* Distributed recovery has finished

WARNING: Instance '127.0.0.1:3310' cannot persist configuration since MySQL version 5.7.26 does not support the SET PERSIST  command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the  changes.
The instance '127.0.0.1:3320' was successfully added to the cluster.

记住,如果不禁用ssl,在向集群添加实例时总是会报错,比如:

 MySQL  localhost:3310  JS > cluster.addInstance('root@localhost:3320', {memberSslMode: 'DISABLED'})
WARNING: Option 'memberSslMode' is deprecated for this operation and it will be removed in a future release. This option is  not needed because the SSL mode is automatically obtained from the cluster. Please do not use it here.

WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell

NOTE: The target instance '127.0.0.1:3320' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide  whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely  overwrite the state of '127.0.0.1:3320' 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'.


Please select a recovery method [I]ncremental recovery/[A]bort (default Incremental recovery): I
Validating instance configuration at localhost:3320...
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3320

Instance configuration is suitable.
ERROR: RuntimeError: The cluster has TLS (encryption) enabled. To add the instance '127.0.0.1:3320' to the cluster either  disable TLS on the cluster, remove the memberSslMode option or use it with any of 'AUTO', 'REQUIRED', 'VERIFY_CA' or  'VERIFY_IDENTITY'.
Cluster.addInstance: The cluster has TLS (encryption) enabled. To add the instance '127.0.0.1:3320' to the cluster either  disable TLS on the cluster, remove the memberSslMode option or use it with any of 'AUTO', 'REQUIRED', 'VERIFY_CA' or  'VERIFY_IDENTITY'. (RuntimeError)

那怕你使用cluster.addInstance(‘root@localhost:3320’, {memberSslMode: ‘DISABLED’})命令,通过{memberSslMode: ‘DISABLED’}选项来禁 用ssl都不会起作用。

添加第三个实例:

 MySQL  localhost:3310  JS > cluster.addInstance('root@localhost:3330')
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell

NOTE: The target instance '127.0.0.1:3330' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide  whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely  overwrite the state of '127.0.0.1:3330' 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'.


Please select a recovery method [I]ncremental recovery/[A]bort (default Incremental recovery):
Validating instance configuration at localhost:3330...
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3330

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

* Checking connectivity and SSL configuration...
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.

WARNING: Instance '127.0.0.1:3330' cannot persist Group Replication configuration since MySQL version 5.7.26 does not support  the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to  persist the changes.
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 '127.0.0.1:3330'

WARNING: Instance '127.0.0.1:3310' cannot persist configuration since MySQL version 5.7.26 does not support the SET PERSIST  command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the  changes.
WARNING: Instance '127.0.0.1:3320' cannot persist configuration since MySQL version 5.7.26 does not support the SET PERSIST  command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the  changes.
The instance '127.0.0.1:3330' was successfully added to the cluster.

至此,您已经创建了一个包含三个实例的集群:一个主实例和两个从实例。

只有当实例是沙盒实例时,才能在addInstance()中指定localhost。这一点同样适用于执行createCluster()函数后的addInstance()函数 。

持久化配置
一旦沙盒实例被添加到集群中,InnoDB集群所需的配置必须持久化到每个实例的选项文件中。连接到每个实例并发出 dba.configureLocalInstance(instance)。

 MySQL  JS > \connect root@localhost:3310
Creating a session to 'root@localhost:3310'
Please provide the password for 'root@localhost:3310': ******
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 43
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3310  JS >

执行dba.configureLocalInstance(instance):

 MySQL  localhost:3310  JS > dba.configureLocalInstance('root@localhost:3310')
WARNING: This function is deprecated and will be removed in a future release of MySQL Shell, use dba.configureInstance()  instead.
Please provide the password for 'root@localhost:3310': ******
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
The instance '127.0.0.1:3310' belongs to an InnoDB cluster.
Sandbox MySQL configuration file at: /root/mysql-sandboxes/3310/my.cnf
Persisting the cluster settings...
The instance '127.0.0.1:3310' was configured for use in an InnoDB cluster.

The instance cluster settings were successfully persisted.

如果连接到实例时没有发出dba.configureLocalInstance(),则配置不会持久化到选项文件中。这并不会阻止实例最初加入集群,例如在停止后 ,但它确实意味着实例不能自动重新加入集群。

重复连接到集群中添加的每个沙盒实例并持久化配置的过程。在本例中,我们在3310、3320和3330端口添加了沙盒实例。因此,对端口3320和 3330执行以下命令:

 MySQL  localhost:3310  JS > \disconnect
 MySQL  JS > \connect root@localhost:3320
Creating a session to 'root@localhost:3320'
Please provide the password for 'root@localhost:3320': ******
Save password for 'root@localhost:3320'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 36
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3320  JS > dba.configureLocalInstance('root@localhost:3320')
WARNING: This function is deprecated and will be removed in a future release of MySQL Shell, use dba.configureInstance()  instead.
Please provide the password for 'root@localhost:3320': ******
Save password for 'root@localhost:3320'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
The instance '127.0.0.1:3320' belongs to an InnoDB cluster.
Sandbox MySQL configuration file at: /root/mysql-sandboxes/3320/my.cnf
Persisting the cluster settings...
The instance '127.0.0.1:3320' was configured for use in an InnoDB cluster.

The instance cluster settings were successfully persisted.
 MySQL  localhost:3320  JS > \disconnect


 MySQL  JS > \connect root@localhost:3330
Creating a session to 'root@localhost:3330'
Please provide the password for 'root@localhost:3330': ******
Save password for 'root@localhost:3330'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 25
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3330  JS > dba.configureLocalInstance('root@localhost:3330')
WARNING: This function is deprecated and will be removed in a future release of MySQL Shell, use dba.configureInstance()  instead.
Please provide the password for 'root@localhost:3330': ******
Save password for 'root@localhost:3330'? [Y]es/[N]o/Ne[v]er (default No):
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
The instance '127.0.0.1:3330' belongs to an InnoDB cluster.
Sandbox MySQL configuration file at: /root/mysql-sandboxes/3330/my.cnf
Persisting the cluster settings...
The instance '127.0.0.1:3330' was configured for use in an InnoDB cluster.

The instance cluster settings were successfully persisted.

要检查集群是否已创建,请使用集群实例的status()函数。

 MySQL  localhost:3330  JS > cluster.status()
WARNING: Support for AdminAPI operations in MySQL version 5.7 is deprecated and will be removed in a future release of MySQL  Shell
{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "127.0.0.1:3310",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "127.0.0.1:3310": {
                "address": "127.0.0.1:3310",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "5.7.26"
            },
            "127.0.0.1:3320": {
                "address": "127.0.0.1:3320",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "5.7.26"
            },
            "127.0.0.1:3330": {
                "address": "127.0.0.1:3330",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "5.7.26"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "127.0.0.1:3310"
}

下面来测试一个集群的同步功能
在主库上创建jycs库并创建一个test表

 MySQL  JS > \connect root@localhost:3310
Creating a session to 'root@localhost:3310'
Please provide the password for 'root@localhost:3310': ******
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 47
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3310  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  localhost:3310  SQL > show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.0009 sec)
 MySQL  localhost:3310  SQL > create database jycs;
Query OK, 1 row affected (0.0038 sec)
 MySQL  localhost:3310  SQL > show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| jycs                          |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
6 rows in set (0.0009 sec)
 MySQL  localhost:3310  SQL > use jycs
Default schema set to `jycs`.
Fetching global names, object names from `jycs` for auto-completion... Press ^C to stop.
 MySQL  localhost:3310  jycs  SQL >  create table test(id int(20),name varchar(50));
Query OK, 0 rows affected (0.0150 sec)
 MySQL  localhost:3310  jycs  SQL > show tables;
+----------------+
| Tables_in_jycs |
+----------------+
| test           |
+----------------+
1 row in set (0.0009 sec)
;
 MySQL  localhost:3310  jycs  SQL > insert into test values(1,'jy');
ERROR: 3098 (HY000): The table does not comply with the requirements by an external plugin.

上面报错是因为MySQL Group Replication (MGR) 等插件要求所有表必须包含主键(Primary Key),若表未定义主键,执行 DML 操作时会触发 此错误。

下面修改test表将id列作为主键。

 MySQL  localhost:3310  jycs  SQL > ALTER TABLE test MODIFY COLUMN id INT PRIMARY KEY;
Query OK, 0 rows affected (0.0255 sec)

Records: 0  Duplicates: 0  Warnings: 0
 MySQL  localhost:3310  jycs  SQL > insert into test values(1,'jy');
Query OK, 1 row affected (0.0048 sec)

 MySQL  localhost:3310  jycs  SQL > \js
Switching to JavaScript mode...
 MySQL  localhost:3310  jycs  JS > \disconnect

登录第二个实例,查看jycs库与test表是否存在

 MySQL  JS > \connect root@localhost:3320
Creating a session to 'root@localhost:3320'
Please provide the password for 'root@localhost:3320': ******
Save password for 'root@localhost:3320'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 40
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3320  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  localhost:3320  SQL > show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| jycs                          |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
6 rows in set (0.0008 sec)
 MySQL  localhost:3320  SQL > use jycs
Default schema set to `jycs`.
Fetching global names, object names from `jycs` for auto-completion... Press ^C to stop.
 MySQL  localhost:3320  jycs  SQL > show tables
                                 -> ;
+----------------+
| Tables_in_jycs |
+----------------+
| test           |
+----------------+
1 row in set (0.0006 sec)

 MySQL  localhost:3320  jycs  SQL > select * from test;
+----+------+
| id | name |
+----+------+
|  1 | jy   |
+----+------+
1 row in set (0.0012 sec)


 MySQL  localhost:3320  jycs  SQL > \js
Switching to JavaScript mode...
 MySQL  localhost:3320  jycs  JS > \disconnect

登录第三个实例,查看jycs库与test表是否存在

 MySQL  JS > \connect root@localhost:3330
Creating a session to 'root@localhost:3330'
Please provide the password for 'root@localhost:3330': ******
Save password for 'root@localhost:3330'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 29
Server version: 5.7.26-log Source distribution
No default schema selected; type \use  to set one.
 MySQL  localhost:3330  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  localhost:3330  SQL > show databases
                           -> ;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| jycs                          |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
6 rows in set (0.0009 sec)
 MySQL  localhost:3330  SQL > use jycs
Default schema set to `jycs`.
Fetching global names, object names from `jycs` for auto-completion... Press ^C to stop.
 MySQL  localhost:3330  jycs  SQL > show tables;
+----------------+
| Tables_in_jycs |
+----------------+
| test           |
+----------------+
1 row in set (0.0007 sec)
 MySQL  localhost:3330  jycs  SQL > select * from test;
+----+------+
| id | name |
+----+------+
|  1 | jy   |
+----+------+
1 row in set (0.0013 sec)

 MySQL  localhost:3330  jycs  SQL > \js
Switching to JavaScript mode...
 MySQL  localhost:3330  jycs  JS > \disconnect
 MySQL  JS >

至此使用MySQL Shell部署沙箱InnoDB集群完成。