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 \useto 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 \useto 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 \useto 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 \useto 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 \useto 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 \useto 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 \useto 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集群完成。