InnoDBCluster搭建与管理全解析

### InnoDB Cluster搭建与管理全解析 #### 1. 注意事项 在开始搭建InnoDB集群之前，有一个重要的注意点需要牢记：JavaScript是区分大小写的。这意味着在使用变量、对象和方法时，必须确保拼写正确。例如，变量`abc`和`Abc`是不同的变量。 #### 2. 创建集群 接下来，我们要设置一个新的集群。可以使用`dba`对象中的`create_cluster()`方法来创建集群类的对象实例。不过，在这之前，需要先连接到想要作为主服务器的服务器。以下是创建集群的具体步骤： - **连接到主服务器**： ```plaintext MySQL Py > \connect root@localhost:3311 Creating a session to 'root@localhost:3311' Enter password: **** Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 12 Server version: 8.0.11 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. ``` - **创建集群**： ```plaintext MySQL localhost:3311 ssl Py > my_cluster = dba.create_cluster('MyCluster', {'multiMaster':False}) A new InnoDB cluster will be created on instance 'root@localhost:3311'. Validating instance at localhost:3311... 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 DESKTOP-JBL081L Instance configuration is suitable. Creating InnoDB cluster 'MyCluster' on 'root@localhost:3311'... Adding Seed Instance... Cluster successfully created. Use Cluster.add_instance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure. ``` 这里将集群命名为`MyCluster`，并使用变量`my_cluster`来存储`create_cluster()`方法返回的对象。同时，将集群设置为单主模式，即只有一个实例具有读写权限，其他实例为只读。 #### 3. 添加实例到集群 创建集群后，需要添加其他服务器实例来完成集群的搭建。使用之前保存的`my_cluster`对象的`add_instance()`方法来添加实例。以下是添加实例的示例： ```plaintext MySQL localhost:3311 ssl Py > my_cluster.add_instance('root@localhost:3312') 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. Please provide the password for 'root@localhost:3312': **** Adding instance to the cluster ... Validating instance at localhost:3312... 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 DESKTOP-JBL081L Instance configuration is suitable. The instance 'root@localhost:3312' was successfully added to the cluster. ``` 可以按照同样的方式添加其他实例，如`root@localhost:3313`和`root@localhost:3314`。`add_instance()`方法接受一个包含URI连接信息的字符串，格式为`<user>@<host>:<port>`，并且会提示输入实例的密码。 #### 4. 检查集群状态 集群创建并添加实例后，可以使用`my_cluster`对象的`status()`方法来获取集群的状态。以下是获取集群状态的示例： ```plaintext C:\idc_sandbox>mysqlsh --py MySQL Shell 8.0.11 Copyright (c) 2016, 2018, 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 '\?' for help; '\quit' to exit. MySQL Py > \connect root@localhost:3311 Creating a session to 'root@localhost:3311' Enter password: **** Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 55 Server version: 8.0.11 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL localhost:3311 ssl Py > my_cluster = dba.get_cluster('MyCluster') MySQL localhost:3311 ssl Py > my_cluster.status() { "clusterName": "MyCluster", "defaultReplicaSet": { "name": "default", "primary": "localhost:3311", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "localhost:3311": { "address": "localhost:3311", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "localhost:3312": { "address": "localhost:3312", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "localhost:3313": { "address": "localhost:3313", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "localhost:3314": { "address": "localhost:3314", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } } }, "groupInformationSourceMember": "mysql://root@localhost:3311" } ``` 输出结果是一个JSON文档，包含了集群的元数据，包括所有实例、它们的角色和状态。需要确保所有实例都处于在线状态。 #### 5. 故障转移 为了模拟故障转移场景，我们将故意杀死一个实例，例如运行在端口3311的实例。可以使用多种方法来杀死实例，如使用操作系统终止`mysqld`进程、使用shell或MySQL客户端的`shutdown` SQL命令，或者使用`dba`类。以下是故障转移的演示： ```plaintext MySQL localhost:3311 ssl Py > dba.kill_sandbox_instance(3311, {'sandboxDir':'c://idc_sandbox'}) The MySQL sandbox instance on this host in c://idc_sandbox\3311 will be killed Killing MySQL instance... Instance localhost:3311 successfully killed. MySQL localhost:3311 ssl Py > my_cluster.status() Traceback (most recent call last): File "<string>", line 1, in <module> SystemError: RuntimeError: Cluster.status: Unable to detect target instance state. Please check account privileges. MySQL localhost:3311 ssl Py > \connect root@localhost:3312 Creating a session to 'root@local ```