MySQL如何修改组复制通信栈(Communication Stack)

MGR通信栈修改为XCOM查看当前MySQL MGR/InnoDB Cluster的通信栈类型/通信栈协议mysql SELECT MEMBER_HOST - ,MEMBER_PORT - ,MEMBER_COMMUNICATION_STACK - FROM performance_schema.replication_group_members - ORDER BY MEMBER_HOST; ------------------------------------------------------ | MEMBER_HOST | MEMBER_PORT | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------ | mysqlu01 | 7306 | MySQL | | mysqlu02 | 7306 | MySQL | | mysqlu03 | 7306 | MySQL | ------------------------------------------------------ 3 rows in set (0.00 sec) mysqlMySQL mysqlu01:7306 ssl JS var clusterdba.getCluster() MySQL mysqlu01:7306 ssl JS cluster.status() { clusterName: gsp_cluster, defaultReplicaSet: { name: default, primary: mysqlu01:7306, ssl: REQUIRED, status: OK, statusText: Cluster is ONLINE and can tolerate up to ONE failure., topology: { mysqlu01:7306: { address: mysqlu01:7306, memberRole: PRIMARY, mode: R/W, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.4.5 }, mysqlu02:7306: { address: mysqlu02:7306, memberRole: SECONDARY, mode: R/O, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.4.5 }, mysqlu03:7306: { address: mysqlu03:7306, memberRole: SECONDARY, mode: R/O, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.4.5 } }, topologyMode: Single-Primary }, groupInformationSourceMember: mysqlu01:7306 } MySQL mysqlu01:7306 ssl JS 注意,直接修改InnoDB Cluster的通信栈协议是不行的. 如下所示:MySQL mysqlu01:7306 ssl JS clusterdba.rebootClusterFromCompleteOutage(gsp_cluster,{switchCommunicationStack:XCOM}) Restoring the Cluster gsp_cluster from complete outage... Cluster instances: mysqlu01:7306 (ONLINE), mysqlu02:7306 (ONLINE), mysqlu03:7306 (ONLINE) Dba.rebootClusterFromCompleteOutage: The Cluster is ONLINE (RuntimeError)首先,需要停止InnoDB Cluster中各个节点,然后启动MySQL服务,设置MySQL数据库的下面变量.如下所示:mysqlu01(192.168.9.200)set persist group_replication_communication_stackxcom; set persist group_replication_local_address192.168.9.200:33061; set persist group_replication_group_seeds192.168.9.201:33061,192.168.9.202:33061,192.168.9.200:33061;mysqlu02(192.168.9.201)set persist group_replication_communication_stackxcom; set persist group_replication_local_address192.168.9.201:33061; set persist group_replication_group_seeds192.168.9.202:33061,192.168.9.200:33061,192.168.9.201:33061;mysqlu03(192.168.9.202)set persist group_replication_communication_stackxcom; set persist group_replication_local_address192.168.9.202:33061; set persist group_replication_group_seeds192.168.9.200:33061,192.168.9.201:33061,192.168.9.202:33061;重新配置和恢复集群, 如下,如果没有指定参数localAddress,则会报Automatically generated port for localAddress falls out of valid range. The port must be an integer between 1 and 65535. Please use the localAddress option to manually set a valid value错误.MySQL mysqlu01:7306 ssl JS clusterdba.rebootClusterFromCompleteOutage(gsp_cluster,{switchCommunicationStack:XCOM}) Restoring the Cluster gsp_cluster from complete outage... Cluster instances: mysqlu01:7306 (OFFLINE), mysqlu02:7306 (OFFLINE), mysqlu03:7306 (OFFLINE) Waiting for instances to apply pending received transactions... NOTE: The instance mysqlu01:7306 is running auto-rejoin process, which will be cancelled. NOTE: Cancelling active GR auto-initialization at mysqlu01:7306 Validating instance configuration at mysqlu01:7306... This instance reports its own address as mysqlu01:7306 Instance configuration is suitable. Dba.rebootClusterFromCompleteOutage: Automatically generated port for localAddress falls out of valid range. The port must be an integer between 1 and 65535. Please use the localAddress option to manually set a valid value. (ArgumentError)修改变量/参数后重新执行,如下所示MySQL mysqlu01:7306 ssl JS clusterdba.rebootClusterFromCompleteOutage(gsp_cluster,{switchCommunicationStack:XCOM,localAddress:mysqlu01:33061}) Restoring the Cluster gsp_cluster from complete outage... Cluster instances: mysqlu01:7306 (OFFLINE), mysqlu02:7306 (OFFLINE), mysqlu03:7306 (OFFLINE) WARNING: The value used for localAddress only applies to the current session instance (seed). If the values generated automatically for other rejoining Cluster members are not valid, please use Cluster.rejoinInstance() with the localAddress option. Waiting for instances to apply pending received transactions... Validating instance configuration at mysqlu01:7306... This instance reports its own address as mysqlu01:7306 Instance configuration is suitable. * Waiting for seed instance to become ONLINE... mysqlu01:7306 was restored. Updating instance metadata... The instance metadata for mysqlu01:7306 was successfully updated. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of mysqlu02:7306 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 mysqlu02:7306... This instance reports its own address as mysqlu02:7306 Instance configuration is suitable. WARNING: mysqlu02:7306: ArgumentError: Automatically generated port for localAddress falls out of valid range. The port must be an integer between 1 and 65535. Please use the localAddress option to manually set a valid value. NOTE: Unable to rejoin instance mysqlu02:7306 to the Cluster but the dba.rebootClusterFromCompleteOutage() operation will continue. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of mysqlu03:7306 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 mysqlu03:7306... This instance reports its own address as mysqlu03:7306 Instance configuration is suitable. WARNING: mysqlu03:7306: ArgumentError: Automatically generated port for localAddress falls out of valid range. The port must be an integer between 1 and 65535. Please use the localAddress option to manually set a valid value. NOTE: Unable to rejoin instance mysqlu03:7306 to the Cluster but the dba.rebootClusterFromCompleteOutage() operation will continue. The Cluster was successfully rebooted. Cluster:gsp_cluster此时MySQL Innodb Cluster已经启动了,但是除了节点mysqlu01外, 节点mysqlu02/mysqlu03都处于MISSING状态,此时需要将这些节点重新加入集群.MySQL mysqlu01:7306 ssl JS cluster.rejoinInstance(icadminmysqlu02:7306) The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of mysqlu02:7306 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 mysqlu02:7306... This instance reports its own address as mysqlu02:7306 Instance configuration is suitable. Rejoining instance mysqlu02:7306 to cluster gsp_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: mysqlu02:7306 is being recovered from mysqlu01:7306 * Distributed recovery has finished The instance mysqlu02:7306 was successfully rejoined to the cluster.注意:加入InnoDB Cluster,还是用端口7306, 而不是33061.此时可以检查通信栈类型mysql SELECT MEMBER_HOST - ,MEMBER_PORT - ,MEMBER_COMMUNICATION_STACK - FROM performance_schema.replication_group_members - ORDER BY MEMBER_HOST; ------------------------------------------------------ | MEMBER_HOST | MEMBER_PORT | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------ | mysqlu01 | 7306 | XCom | | mysqlu02 | 7306 | XCom | ------------------------------------------------------ 2 rows in set (0.00 sec) mysql SHOW VARIABLES LIKE group_replication_local_address; ------------------------------------------------- | Variable_name | Value | ------------------------------------------------- | group_replication_local_address | mysqlu01:33061 | ------------------------------------------------- 1 row in set (0.00 sec) mysqlMySQL mysqlu01:7306 ssl JS cluster.rejoinInstance(icadminmysqlu03:7306) The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of mysqlu03:7306 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 mysqlu03:7306... This instance reports its own address as mysqlu03:7306 Instance configuration is suitable. Rejoining instance mysqlu03:7306 to cluster gsp_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: mysqlu03:7306 is being recovered from mysqlu01:7306 * Distributed recovery has finished The instance mysqlu03:7306 was successfully rejoined to the cluster. MySQL mysqlu01:7306 ssl JS MGR通信栈修改为MySQL上面操作将MGR的通信栈修改成了XCOM,然后我们将MGR的通信栈修改为MySQL.mysql SELECT MEMBER_HOST - ,MEMBER_PORT - ,MEMBER_COMMUNICATION_STACK - FROM performance_schema.replication_group_members - ORDER BY MEMBER_HOST; ------------------------------------------------------ | MEMBER_HOST | MEMBER_PORT | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------ | mysqlu01 | 7306 | XCOM | | mysqlu02 | 7306 | XCOM | | mysqlu03 | 7306 | XCOM | ------------------------------------------------------ 3 rows in set (0.00 sec) mysql步骤1: 关闭MySQL InnoDB Cluster中各个节点,然后重启各个节点的MySQL服务.步骤2. 用命令rebootClusterFromCompleteOutage重新配置和恢复集群MySQL mysqlu01:7306 ssl JS dba.rebootClusterFromCompleteOutage(gsp_cluster, {switchCommunicationStack: mysql}) Restoring the Cluster gsp_cluster from complete outage... Cluster instances: mysqlu01:7306 (OFFLINE), mysqlu02:7306 (OFFLINE), mysqlu03:7306 (OFFLINE) Waiting for instances to apply pending received transactions... NOTE: The instance mysqlu01:7306 is running auto-rejoin process, which will be cancelled. NOTE: Cancelling active GR auto-initialization at mysqlu01:7306 Validating instance configuration at mysqlu01:7306... This instance reports its own address as mysqlu01:7306 Instance configuration is suitable. NOTE: User mysql_innodb_cluster_1% already existed at instance mysqlu01:7306. It will be deleted and created again with a new password. * Waiting for seed instance to become ONLINE... mysqlu01:7306 was restored. Updating instance metadata... The instance metadata for mysqlu01:7306 was successfully updated. NOTE: The instance mysqlu02:7306 is running auto-rejoin process, which will be cancelled. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of mysqlu02:7306 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 mysqlu02:7306... This instance reports its own address as mysqlu02:7306 Instance configuration is suitable. Rejoining instance mysqlu02:7306 to cluster gsp_cluster... NOTE: Cancelling active GR auto-initialization at mysqlu02:7306 Re-creating recovery account... NOTE: User mysql_innodb_cluster_201% already existed at instance mysqlu01:7306. It will be deleted and created again with a new password. Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background. State recovery already finished for mysqlu02:7306 The instance mysqlu02:7306 was successfully rejoined to the cluster. The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of mysqlu03:7306 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 mysqlu03:7306... This instance reports its own address as mysqlu03:7306 Instance configuration is suitable. Rejoining instance mysqlu03:7306 to cluster gsp_cluster... Re-creating recovery account... NOTE: User mysql_innodb_cluster_202% already existed at instance mysqlu01:7306. It will be deleted and created again with a new password. 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: mysqlu03:7306 is being recovered from mysqlu02:7306 * Distributed recovery has finished The instance mysqlu03:7306 was successfully rejoined to the cluster. The Cluster was successfully rebooted. Cluster:gsp_cluster MySQL mysqlu01:7306 ssl JS clusterdba.getCluster() Cluster:gsp_cluster MySQL mysqlu01:7306 ssl JS cluster.status() { clusterName: gsp_cluster, defaultReplicaSet: { name: default, primary: mysqlu01:7306, ssl: REQUIRED, status: OK, statusText: Cluster is ONLINE and can tolerate up to ONE failure., topology: { mysqlu01:7306: { address: mysqlu01:7306, memberRole: PRIMARY, mode: R/W, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.4.5 }, mysqlu02:7306: { address: mysqlu02:7306, memberRole: SECONDARY, mode: R/O, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.4.5 }, mysqlu03:7306: { address: mysqlu03:7306, memberRole: SECONDARY, mode: R/O, readReplicas: {}, replicationLag: applier_queue_applied, role: HA, status: ONLINE, version: 8.4.5 } }, topologyMode: Single-Primary }, groupInformationSourceMember: mysqlu01:7306 } MySQL mysqlu01:7306 ssl JS 通信栈协议修改为MySQL时,不用设置相关MySQL系统变量. 此时MEMBER_COMMUNICATION_STACK的值为MySQL.如下所示:mysql SELECT MEMBER_HOST - ,MEMBER_PORT - ,MEMBER_COMMUNICATION_STACK - FROM performance_schema.replication_group_members - ORDER BY MEMBER_HOST; ------------------------------------------------------ | MEMBER_HOST | MEMBER_PORT | MEMBER_COMMUNICATION_STACK | ------------------------------------------------------ | mysqlu01 | 7306 | MySQL | | mysqlu02 | 7306 | MySQL | | mysqlu03 | 7306 | MySQL | ------------------------------------------------------ 3 rows in set (0.00 sec) mysql mysql SHOW VARIABLES LIKE group_replication_local_address; ------------------------------------------------ | Variable_name | Value | ------------------------------------------------ | group_replication_local_address | mysqlu01:7306 | ------------------------------------------------ 1 row in set (0.01 sec) mysql