MySQL如何修改组复制通信栈(Communication Stack)
2026-03-18 14:34 潇湘隐者 阅读(7) 评论(0) 收藏 举报之前总结过一篇文章MySQL组复制的通信栈Communication Stack, 下面介绍如果修改MySQL MGR通信栈类型.
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)
mysql>
MySQL mysqlu01:7306 ssl JS > var cluster=dba.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 > cluster=dba.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_stack='xcom';
set persist group_replication_local_address='192.168.9.200:33061';
set persist group_replication_group_seeds='192.168.9.201:33061,192.168.9.202:33061,192.168.9.200:33061';
mysqlu02(192.168.9.201)
set persist group_replication_communication_stack='xcom';
set persist group_replication_local_address='192.168.9.201:33061';
set persist group_replication_group_seeds='192.168.9.202:33061,192.168.9.200:33061,192.168.9.201:33061';
mysqlu03(192.168.9.202)
set persist group_replication_communication_stack='xcom';
set persist group_replication_local_address='192.168.9.202:33061';
set persist group_replication_group_seeds='192.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 > cluster=dba.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 > cluster=dba.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('icadmin@mysqlu02: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)
mysql>
MySQL mysqlu01:7306 ssl JS > cluster.rejoinInstance('icadmin@mysqlu03: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 > cluster=dba.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>
如上所示, 你去检查,发现上面操作会自动修改group_replication_communication_stack,group_replication_local_address等系统变量.
扫描上面二维码关注我
如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
浙公网安备 33010602011771号