MySQL8.0 原生高可用之Innodb Cluster（二）
作者:快盘下载 人气:Innodb Cluster的搭建过程
一、mysql Shell的安装和配置二、MySQL Router的搭建和使用方法三、MySQL Innodb Cluster搭建过程1、原理图镇楼2、服务器信息3、具体搭建过程3.1 检查实例配置3.2 创建一个Innodb Cluster集群，并加入第一个节点3.3 加入其他节点四、简单测试4.1 MGR运行模式切换4.2 测试主动切换主库
一、MySQL Shell的安装和配置
MySQL shell是一个MySQL的一个高级客户端，可以用于管理Innodb Cluster集群，DBA通过MySQL Shell工具来直接或者间接进行运维工作。
关于这个工具的安装和使用方法，之前的文章中做过介绍，这里不再赘述： MySQL Shell的下载和部署
二、MySQL Router的搭建和使用方法
MySQL Router主要是提供给业务使用的，如果仅仅是DBA侧的运维，仅仅使用MySQL Shell就足够管理Innodb Cluster了。 MySQL Router相关的安装和介绍内容之前的文章中已经写过了，不再赘述： MySQL Router的安装和部署
三、MySQL Innodb Cluster搭建过程
1、原理图镇楼
关于Innodb的原理性介绍，请参考：
MySQL原生高可用方案之InnoDB Cluster(一)
2、服务器信息
节点一：10.69.2.203 1234 节点二：10.69.2.66 1234 节点三：10.69.6.119 1234 节点四：10.69.2.64 1234
其中，1234是MySQL实例的端口号
3、具体搭建过程
3.1 检查实例配置
使用 dba.checkInstanceConfiguration()命令检查实例的配置是否符合要求，首先我们检测一下节点一（10.69.2.203：1234）的配置：
MySQL 10.69.2.203:1234 ssl JS > dba.checkInstanceConfiguration() Validating MySQL instance at 10.69.2.203:1234 for use in an InnoDB cluster... ERROR: The account 'myadmin_common'@'10.%' is missing privileges required to manage an InnoDB cluster: GRANT BACKUP_ADMIN, CLONE_ADMIN, CREATE USER, FILE, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, SUPER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'myadmin_common'@'10.%' WITH GRANT OPTION; GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'myadmin_common'@'10.%' WITH GRANT OPTION; GRANT CREATE VIEW, EVENT, SHOW VIEW, TRIGGER ON mysql_innodb_cluster_metadata.* TO 'myadmin_common'@'10.%' WITH GRANT OPTION; GRANT CREATE VIEW, EVENT, SHOW VIEW, TRIGGER ON mysql_innodb_cluster_metadata_bkp.* TO 'myadmin_common'@'10.%' WITH GRANT OPTION; GRANT CREATE VIEW, EVENT, SHOW VIEW, TRIGGER ON mysql_innodb_cluster_metadata_previous.* TO 'myadmin_common'@'10.%' WITH GRANT OPTION; For more information, see the online documentation. Dba.checkInstanceConfiguration: The account 'myadmin_common'@'10.%' is missing privileges required to manage an InnoDB cluster. (RuntimeError)
上述命令的执行结果，可以看出，当前实例10.69.2.203的账号myadmin_common，缺少相关权限。这里的提示还是很友好的，给出了授权的命令，你只需要按照这个命令粘到MySQL实例中即可。
为了后期的执行顺利，我设置了一个更高权限的账号叫superdba，替代上面的账号myadmin_common，新的连接方法如下：
连接到某一个节点上： MySQL JS > connect superdba@10.69.2.203:1234 Creating a session to 'superdba@10.69.2.203:1234' Please provide the password for 'superdba@10.69.2.203:1234': ************ Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 14 Server version: 8.0.20 MySQL Community Server - GPL No default schema selected; type use <schema> to set one. MySQL 10.69.2.203:1234 ssl JS > MySQL 10.69.2.203:1234 ssl JS >
配置好账号权限，接着检查看看还有没有其他的问题：
MySQL 10.69.2.203:1234 ssl JS > dba.checkInstanceConfiguration()
...省略一些输出信息
NOTE: Some configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable | Current Value | Required Value | Note |
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32 | NONE | Update the server variable |
+-----------------+---------------+----------------+----------------------------+
NOTE: Please use the dba.configureInstance() command to repair these issues.
{
"config_errors": [
{
"action": "server_update",
"current": "CRC32",
"option": "binlog_checksum",
"required": "NONE"
}
],
"status": "error"
}
可以看到，这次它帮我们检测出来一些参数问题，binlog_checksum的值需要设置为None。 这个结果其实也容易理解，因为Innodb Cluster底层是基于MGR的，而binlog_checksum的配置为None是MGR要求的，所以我们需要修改这个变量为None。
修改完成之后，继续检测，看看还有没有其他的问题：
MySQL JS > dba.configureInstance('superdba@10.69.2.203:1234')
Please provide the password for 'superdba@10.69.2.203:1234': ************
Configuring MySQL instance at 10.69.2.203:1234 for use in an InnoDB cluster...
This instance reports its own address as 10.69.2.203:1234
The instance '10.69.2.203:1234' is valid to be used in an InnoDB cluster.
The instance '10.69.2.203:1234' is already ready to be used in an InnoDB cluster.
可以看到，所有的问题，都已经被我们修复了。 紧接着用这个方法检查其他的节点的配置情况，依次检查下面4个节点： 节点一：10.69.2.203 1234 节点二：10.69.2.66 1234 节点三：10.69.6.119 1234 节点四：10.69.2.64 1234
3.2 创建一个Innodb Cluster集群，并加入第一个节点
我们使用dba.createCluster('testCluster')命令创建一个集群：
MySQL 10.69.2.203:1234 ssl JS > var cluster = dba.createCluster('testCluster')
A new InnoDB cluster will be created on instance '10.69.2.203:1234'.
Disabling super_read_only mode on instance '10.69.2.203:1234'.
Validating instance configuration at 10.69.2.203:1234...
This instance reports its own address as 10.69.2.203:1234
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '10.69.2.203:12341'. Use the localAddress option to override.
Creating InnoDB cluster 'testCluster' on '10.69.2.203:1234'...
Adding Seed Instance...
### 提示通过addInstance()命令去添加其他的节点
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
### 提示至少需要3个节点
At least 3 instances are needed for the cluster to be able to withstand up to one server failure.
3.3 加入其他节点
我们尝试使用addInstance命令加入节点二：10.69.2.66
MySQL 10.69.2.203:1234 ssl JS > var cluster = dba.getCluster()
MySQL 10.69.2.203:1234 ssl JS > cluster.addInstance("superdba@10.69.2.66:1234")
### 提示你选择一种方式来对新节点进行完整数据恢复,克隆还是直接退出
Please select a recovery method [C]lone/[A]bort (default Abort): C
NOTE: Group Replication will communicate with other members using '10.69.2.66:12341'. Use the localAddress option to override.
Validating instance configuration at 10.69.2.66:1234...
This instance reports its own address as 10.69.2.66:1234
WARNING: The following tables do not have a Primary Key or equivalent column:
yeyz.t2, yeyz.t3
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.
Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
### 这里提示克隆完毕之后，可能会进行一个restart的重启命令
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: 10.69.2.66:1234 is being cloned from 10.69.2.64:1234
Segmentation fault # 报一个错误，但是其实已经克隆完毕了，新的实例正在重启中
我们将集群信息保存在cluster这个变量里面，并使用addInstance命令加入新节点。执行添加节点的中途会让你选择通过哪种方式来恢复新节点的数据，一般有Clone、Incremetal或者Abort，这里我们选择Clone即可，代表使用Clone Plugin来执行新节点的数据恢复 至此，新的实例10.69.2.66已经加入到集群中了。我们重新连接集群，查看现在的Innodb Cluster集群拓扑：
MySQL 10.69.2.203:1234 ssl JS > dba.getCluster().status()
{
"clusterName": "testCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.69.2.203:1234",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"10.69.2.203:1234": {
"address": "10.69.2.203:1234",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "10.69.2.203:1234"
}
上面的集群信息中，貌似只能看到节点一：10.69.2.203，看不到我们的节点二：10.69.2.66，其实它已经存在于集群中了，不信，你重新加入一下，Innodb Cluster会给你提示报错：
MySQL 10.69.2.203:1234 ssl JS > var cluster=dba.getCluster()
MySQL 10.69.2.203:1234 ssl JS > cluster.addInstance("superdba@10.69.2.66:1234")
Please provide the password for 'superdba@10.69.2.66:1234': ************
ERROR: Instance '10.69.2.66:1234' is part of the Group Replication group but is not in the metadata. Please use <Cluster>.rescan() to update the metadata.
### 提示这个节点已经在MGR的原信息中了，但是需要使用rescan来重新扫描一下集群
那我们就按照他的指示，rescan一下：
MySQL 10.69.2.203:1234 ssl JS > cluster.rescan()
Rescanning the cluster...
Result of the rescanning operation for the 'testCluster' cluster:
{
"name": "testCluster",
"newTopologyMode": null,
"newlyDiscoveredInstances": [
{
"host": "10.69.2.66:1234",
"member_id": "0f5765ad-d80d-11eb-a3f7-70e28405e217",
"name": null,
"version": "8.0.20"
}
],
"unavailableInstances": []
}
A new instance '10.69.2.66:1234' was discovered in the cluster.
Would you like to add it to the cluster metadata? [Y/n]: Y
Adding instance to the cluster metadata...
The instance '10.69.2.66:1234' was successfully added to the cluster metadata.
可以看到，相关信息已经扫描出来了，我们在交互式命令行中输入Y，表示将它加入到Innodb Cluster中来，并再次查看Innodb Cluster集群的拓扑信息：
MySQL 10.69.2.203:1234 ssl JS > cluster.status()
{
"clusterName": "testCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.69.2.64:1234",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"10.69.2.203:1234": {
"address": "10.69.2.203:1234",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"10.69.2.66:1234": {
"address": "10.69.2.66:1234",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "10.69.2.203:1234"
}
上述输出中，需要注意三点： 1、集群的MGR采用的是单主模式，10.69.2.203的Mode是R/W（读写节点）,10.69.2.66的Mode是R/O（只读节点） 2、集群的源信息存储在节点一（10.69.2.203:1234） 3、statusText中提示我们这个集群不能忍受任何节点的失败
目前集群中已经有2个节点了。再依次加入其它节点即可. 节点一：10.69.2.203 1234 节点二：10.69.2.66 1234 节点三：10.69.6.119 1234 节点四：10.69.2.64 1234 最终，我们得到的Innodb Cluster的拓扑是：
MySQL 10.69.2.203:1234 ssl JS > cluster.status()
{
"clusterName": "testCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.69.2.64:1234",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"10.69.2.64:1234": {
"address": "10.69.2.64:1234",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"10.69.2.203:1234": {
"address": "10.69.2.203:1234",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"10.69.2.66:1234": {
"address": "10.69.2.66:1234",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"10.69.6.119:1234": {
"address": "10.69.6.119:1234",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "10.69.2.203:1234"
}
可以看到： 1、整个集群只有一个R/w节点，其它节点都是R/O模式的，我们这个Innodb Cluster的底层MGR采用的单主模式，这也符合预期； 2、"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",也就是说，可以容忍一个节点宕机
四、简单测试
4.1 MGR运行模式切换
在InnoDB Cluster中可以很方便的切换MGR的运行模式，也就是从单主切换为双主，双主切换为单主，使用的命令分别是： cluster.switchToMultiPrimaryMode() cluster.switchToSinglePrimaryMode()
例如下面的过程（单主切换多主）:
MySQL 10.69.2.203:1234 ssl JS > cluster.switchToMultiPrimaryMode() Switching cluster 'testCluster' to Multi-Primary mode... Instance '10.69.2.66:1234' was switched from SECONDARY to PRIMARY. Instance '10.69.6.119:1234' was switched from SECONDARY to PRIMARY. Instance '10.69.2.64:1234' was switched from SECONDARY to PRIMARY. Instance '10.69.2.203:1234' remains PRIMARY. The cluster successfully switched to Multi-Primary mode.
切换前后，MGR的角色如下：
---切换前--- localhost.(none)>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 0f5765ad-d80d-11eb-a3f7-70e28405e217 | 10.69.2.66 | 1234 | ONLINE | SECONDARY | 8.0.20 | | group_replication_applier | 1a17875e-d815-11eb-81d8-6c92bf07cfcb | 10.69.6.203 | 1234 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 9a4ec981-da60-11eb-a4fe-70e28405e29b | 10.69.2.119 | 1234 | ONLINE | SECONDARY | 8.0.20 | | group_replication_applier | d44f9d8c-da60-11eb-9f96-70e28405e238 | 10.69.2.64 | 1234 | ONLINE | SECONDARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 4 rows in set (0.00 sec) ---切换后--- localhost.(none)>select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 0f5765ad-d80d-11eb-a3f7-70e28405e217 | 10.69.2.66 | 1234 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 1a17875e-d815-11eb-81d8-6c92bf07cfcb | 10.69.6.203 | 1234 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | 9a4ec981-da60-11eb-a4fe-70e28405e29b | 10.69.2.119 | 1234 | ONLINE | PRIMARY | 8.0.20 | | group_replication_applier | d44f9d8c-da60-11eb-9f96-70e28405e238 | 10.69.2.64 | 1234 | ONLINE | PRIMARY | 8.0.20 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 4 rows in set (0.00 sec)
当然，你可以使用cluster.switchToSinglePrimaryMode()来将多主模式切换为单主模式，但是这个过程中，主库的选择是随机的，不保证切换到原来单主模式的那个主库上。
4.2 测试主动切换主库
在MGR中使用命令setPrimaryInstance('10.69.6.119:1234')来讲MGR的主库切换为10.69.6.119
MySQL 10.69.2.66:1234 ssl JS > cluster.setPrimaryInstance('10.69.6.119:1234')
Setting instance '10.69.6.119:1234' as the primary instance of cluster 'testCluster'...
Instance '10.69.2.66:1234' was switched from PRIMARY to SECONDARY.
Instance '10.69.6.119:1234' was switched from SECONDARY to PRIMARY.
Instance '10.69.2.203:1234' remains SECONDARY.
Instance '10.69.2.64:1234' remains SECONDARY.
### 切完主库后还友情提示重新获取Innodb Cluster集群信息
WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using <Dba>.getCluster().
The instance '10.69.6.119:1234' was successfully elected as primary.
MySQL 10.69.2.66:1234 ssl JS > cluster.status()
{
"clusterName": "testCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.69.6.119:1234",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"10.69.2.203:1234": {
"address": "10.69.2.203:1234",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"10.69.2.64:1234": {
"address": "10.69.2.64:1234",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"10.69.2.66:1234": {
"address": "10.69.2.66:1234",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"10.69.6.119:1234": {
"address": "10.69.6.119:1234",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "10.69.2.66:1234"
}
InnoDB Cluster的输入命令后的提示信息还是很完善的，根据提示信息，能够解决90%的常见问题。 时间原因，其他的测试，后续有时间再做吧，今天就先到这里。
加载全部内容