【InnoDB Cluster】修改已有集群实例名称，成员实例名称和选项
文章目录
之前搭 InnoDB Cluster 时，修改的/etc/hosts命名不规范，以前都是用 连接符（-） ，这是参加工作以来跟老前辈学来的，好多类似的经验上的东西一直也没深究为什么这么做。那天搭建时想用 下划线（_） 命名，发现报错，搜了一下就用了，那里面说 英文句号（.） 也算是能用于主机名的特殊字符，遂把 下划线（_） 改成了 英文句号（.） ，然后就开始搭建了。刚创建集群就发现，这不对啊，域名应该在后面，我现在命名的是 ic.source ，应该改为 source.ic 。想来问题不大，以后可以再改，改个名称总归不会不支持吧！结果，还真不支持。。。
注意
笔者打开 mysqlsh 时首先执行了如下命令声明 cluster 变量：MySQL ic-source:3306 ssl JS > var cluster=dba.getCluster()
修改名称
如果你打算使用主机名作为实例的名称，则主机名这块，最关键的是 MySQL 的主机名（SELECT @@hostname;），而非操作系统当前的主机名。 当然，只要 MySQL 服务器（进程）一重启，就会读取操作系统当前的主机名作为 MySQL 的主机名（SELECT @@hostname;）。
如果你只是打算给实例起个名字，打个标识，则只要是符合命名规范的名称都可以使用。
修改已有集群实例名称
AdminAPI 目前提供的类方法只支持修改一部分选项，修改集群实例名称是支持的，因为它属于 MySQL InnoDB Cluster 的集群实例配置。
下面演示了将 InnoDB Cluster 实例名称从 testCluster 改为 myCluster 的过程。
MySQL ic-source:3306 ssl JS > cluster.status()
{
"clusterName": "testCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-source:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-replica1:3306": {
"address": "ic-replica1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-replica2:3306": {
"address": "ic-replica2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-source:3306": {
"address": "ic-source:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ic-source:3306"
}
MySQL ic-source:3306 ssl JS > cluster.setOption('clusterName','myCluster')
Setting the value of 'clusterName' to 'myCluster' in the Cluster ...
Successfully set the value of 'clusterName' to 'myCluster' in the Cluster: 'testCluster'.
MySQL ic-source:3306 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-source:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-replica1:3306": {
"address": "ic-replica1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-replica2:3306": {
"address": "ic-replica2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-source:3306": {
"address": "ic-source:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ic-source:3306"
}
MySQL ic-source:3306 ssl JS >
修改已有集群实例的成员实例名称
先介绍正确的修改方法：
- 修改成员服务器操作系统的主机名。
- 使用
hostname 新主机名的方式修改，该方式在 DNS 解析时优先级最高。想要持久化可以修改/etc/hosts或/etc/hostname文件的内容。 - 直接修改元数据库中的表。即修改
mysql_innodb_cluster_metadata.instances中的instance_name。 - 使用
cluster.setInstanceOption()方法修改label选项。
修改成员服务器操作系统的主机名
注意
为了弄清楚主机名的影响，我在以下示例中额外做了一些修改。
首先，我故意将主机名设置为 replica-3 ，而这并不包含在/etc/hosts文件中。
hostname replica-3
/etc/hosts文件内容中并没有定义 replica-3 主机名：
[root@datanode3 mysql]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.52.3 source source.ic ic-source
192.168.52.4 replica1 replica1.ic ic-replica1
192.168.52.6 replica2 replica2.ic ic-replica2
192.168.52.6 r2-persist r2-global r2-cnf r2-table
~
此时 InnoDB Cluster 仍然保持在线高可用状态：
MySQL ic-source:3306 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-source:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-replica1:3306": {
"address": "ic-replica1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-replica2:3306": {
"address": "ic-replica2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-source:3306": {
"address": "ic-source:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ic-source:3306"
}
然后，我在主机名为 replica3（同时也是当前 ic-replica2 成员） 的服务器上，持久化设置 group_replication_local_address 为 r2-persist:3306 ，并查看到在操作系统变更主机名后，MySQL 中的 hostname 变量并没有随之改变，仍保持启动时获取到的主机名状态。而且元数据库表mysql_innodb_cluster_metadata.instances中的addresses.grLocal却并没有随持久化设置 group_replication_local_address 而改变，证明 MySQL 并没有将 group_replication_local_address 同步给 InnoDB Cluster 元数据库。这很可能是一个 Bug ！
mysql>use mysql_innodb_cluster_metadata;
mysql> select @@hostname;
+-------------+
| @@hostname |
+-------------+
| ic-replica2 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@group_replication_local_address;
+-----------------------------------+
| @@group_replication_local_address |
+-----------------------------------+
| ic-replica2:3306 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> set persist group_replication_local_address='r2-persist:3306';
Query OK, 0 rows affected (0.01 sec)
mysql> set persist group_replication_local_address='r2-persist:3306';
Query OK, 0 rows affected (0.01 sec)
mysql> select @@group_replication_local_address;
+-----------------------------------+
| @@group_replication_local_address |
+-----------------------------------+
| r2-persist:3306 |
+-----------------------------------+
1 row in set (0.01 sec)
mysql> \! hostname
replica3
mysql> select * from instances;
+-------------+--------------------------------------+------------------+--------------------------------------+------------------+----------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+------------------+--------------------------------------+------------------+----------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 1 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | ic-source:3306 | f10abfbf-7c8b-11ea-a226-000c298d6cb9 | ic-source:3306 | {"mysqlX": "ic-source:33060", "grLocal": "ic-source:3306", "mysqlClassic": "ic-source:3306"} | {"server_id": 344590725, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_344590725"} | NULL |
| 2 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | ic-replica1:3306 | 5a877ac9-a0fc-11ea-ad7b-000c298656b1 | ic-replica1:3306 | {"mysqlX": "ic-replica1:33060", "grLocal": "ic-replica1:3306", "mysqlClassic": "ic-replica1:3306"} | {"joinTime": "2022-11-26 01:10:07.745", "server_id": 1310685405, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1310685405"} | NULL |
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | ic-replica2:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | ic-replica2:3306 | {"mysqlX": "ic-replica2:33060", "grLocal": "ic-replica2:3306", "mysqlClassic": "ic-replica2:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+------------------+--------------------------------------+------------------+----------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
3 rows in set (0.00 sec)
mysql> restart;
Query OK, 0 rows affected (0.00 sec)
重启后，在该成员上查看到 MySQL 的 hostname 变量已经被修改为 replica3 ， group_replication_local_address 变量变为 replica2:3306 。
mysql> select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 34
Current database: mysql_innodb_cluster_metadata
+------------+
| @@hostname |
+------------+
| replica3 |
+------------+
1 row in set (0.07 sec)
mysql> select @@group_replication_local_address;
+-----------------------------------+
| @@group_replication_local_address |
+-----------------------------------+
| r2-persist:3306 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select * from instances;
+-------------+--------------------------------------+------------------+--------------------------------------+------------------+----------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+------------------+--------------------------------------+------------------+----------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 1 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | ic-source:3306 | f10abfbf-7c8b-11ea-a226-000c298d6cb9 | ic-source:3306 | {"mysqlX": "ic-source:33060", "grLocal": "ic-source:3306", "mysqlClassic": "ic-source:3306"} | {"server_id": 344590725, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_344590725"} | NULL |
| 2 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | ic-replica1:3306 | 5a877ac9-a0fc-11ea-ad7b-000c298656b1 | ic-replica1:3306 | {"mysqlX": "ic-replica1:33060", "grLocal": "ic-replica1:3306", "mysqlClassic": "ic-replica1:3306"} | {"joinTime": "2022-11-26 01:10:07.745", "server_id": 1310685405, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1310685405"} | NULL |
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | ic-replica2:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | ic-replica2:3306 | {"mysqlX": "ic-replica2:33060", "grLocal": "ic-replica2:3306", "mysqlClassic": "ic-replica2:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+------------------+--------------------------------------+------------------+----------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
3 rows in set (0.00 sec)
与此同时，查看集群状态发现出现报错，需要rescan()：
MySQL ic-source:3306 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-source:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-replica1:3306": {
"address": "ic-replica1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-replica2:3306": {
"address": "ic-replica2:3306",
"instanceErrors": [
"ERROR: Metadata for this instance does not match hostname reported by instance (metadata=ic-replica2:3306, actual=replica3:3306). Use rescan() to update the metadata."
],
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-source:3306": {
"address": "ic-source:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ic-source:3306"
}
而直接rescan会报错，因为此时服务器因未包含 replica3 无法解析它。
MySQL ic-source:3306 ssl JS > cluster.rescan()
Rescanning the cluster...
Result of the rescanning operation for the 'myCluster' cluster:
{
"name": "myCluster",
"newTopologyMode": null,
"newlyDiscoveredInstances": [],
"unavailableInstances": [],
"updatedInstances": [
{
"host": "replica3:3306",
"id": 4,
"label": "ic-replica2:3306",
"member_id": "2737b324-a0f6-11ea-afd1-000c2988ff33",
"old_host": "ic-replica2:3306"
}
]
}
The instance 'replica3:3306' is part of the cluster but its reported address has changed. Old address: ic-replica2:3306. Current address: replica3:3306.
Cluster.rescan: Error opening session to 'replica3:3306': Unknown MySQL server host 'replica3' (-2) (RuntimeError)
所以我们需要手动添加 replica3 到/etc/hosts中。
#在/etc/hosts中加入一行，或找到该行在末尾添加
[root@ic-source ~]# vi /etc/hosts
192.168.52.6 replica3
接着再次rescan()即可成功：
MySQL ic-source:3306 ssl JS > cluster.rescan()
Rescanning the cluster...
Result of the rescanning operation for the 'myCluster' cluster:
{
"name": "myCluster",
"newTopologyMode": null,
"newlyDiscoveredInstances": [],
"unavailableInstances": [],
"updatedInstances": [
{
"host": "replica3:3306",
"id": 4,
"label": "ic-replica2:3306",
"member_id": "2737b324-a0f6-11ea-afd1-000c2988ff33",
"old_host": "ic-replica2:3306"
}
]
}
The instance 'replica3:3306' is part of the cluster but its reported address has changed. Old address: ic-replica2:3306. Current address: replica3:3306.
Updating instance metadata...
The instance metadata for 'replica3:3306' was successfully updated.
查看一下修改后集群的状态，作为参照。
MySQL ic-source:3306 ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-source:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-replica1:3306": {
"address": "ic-replica1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-source:3306": {
"address": "ic-source:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"replica3:3306": {
"address": "replica3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ic-source:3306"
}
此处为了简洁起见，只截取了关注的信息。
MySQL ic-source:3306 ssl JS > cluster.options()
"replica3:3306": [
{
"option": "localAddress",
"value": "r2-persist:3306",
"variable": "group_replication_local_address"
},
可以看到，重新扫描后并没有将 group_replication_local_address 变量随成员实例名称一并更新。
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-persist:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
然后我在全局范围将 group_replication_local_address 变量的运行时值修改为 r2-global:3306 。
mysql> set global group_replication_local_address='r2-global:3306';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@group_replication_local_address;
+-----------------------------------+
| @@group_replication_local_address |
+-----------------------------------+
| r2-global:3306 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
此时，查看集群选项，证明成员实例的localAddress选项等于 group_replication_local_address 的GLOBAL值，也即全局值、运行时值。一般情况下，如果未特别指定，GLOBAL值等于PERSIST值。
MySQL ic-source:3306 ssl JS > cluster.options()
"replica3:3306": [
{
"option": "localAddress",
"value": "r2-global:3306",
"variable": "group_replication_local_address"
},
而查看元数据库表的值，发现并没有变化。
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-persist:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
那么，我们再修改 group_replication_local_address 变量的永久（持久化）值为 replica2:3306 试试。
mysql> set persist group_replication_local_address='replica2:3306';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-persist:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
mysql>
还是没变。那重启呢？
mysql> restart;
Query OK, 0 rows affected (0.00 sec)
mysql> \c
mysql> select * from instances where instance_name='replica3:3306';
No connection. Trying to reconnect...
Connection id: 34
Current database: mysql_innodb_cluster_metadata
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-persist:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
也还是没变。。。而集群的成员选项localAddress却变成了新持久化的值 replica2:3306 。
{
"option": "localAddress",
"value": "replica2:3306",
"variable": "group_replication_local_address"
},
再次证明了 MySQL 并没有将 group_replication_local_address 同步给 InnoDB Cluster 元数据库。所以我觉得，这应该就是一个 Bug ！
如果我直接去修改 DATADIR/mysqld-auto.cnf 文件中的 group_replication_local_address 为 r2-cnf 呢？
[root@datanode3 mysql]# vi mysqld-auto.cnf
{"Version": 2, "mysql_static_variables": {"gtid_mode": {"Value": "ON", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1
669189433993003}}, "slave_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 166918943398
6835}}, "enforce_gtid_consistency": {"Value": "ON", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1669189433990437}},
"replica_parallel_workers": {"Value": "4", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1669189433986835}}, "group_re
plication_ssl_mode": {"Value": "REQUIRED", "Metadata": {"Host": "source", "User": "ic_config", "Timestamp": 1669396354907018}}, "group_
replication_group_name": {"Value": "f211a70b-6ce3-11ed-8310-000c298d6cb9", "Metadata": {"Host": "source", "User": "ic_config", "Timesta
mp": 1669396354898551}}, "group_replication_group_seeds": {"Value": "ic-replica1:3306,ic-source:3306", "Metadata": {"Host": "source", "
User": "ic_config", "Timestamp": 1669396354910294}}, "group_replication_ip_allowlist": {"Value": "AUTOMATIC", "Metadata": {"Host": "sou
rce", "User": "ic_config", "Timestamp": 1669385083640543}}, "group_replication_local_address": {"Value": "r2-cnf:3306", "Metadata": {"H
ost": "localhost", "User": "root", "Timestamp": 1669400174829630}}, "group_replication_member_weight": {"Value": "50", "Metadata": {"Ho
st": "source", "User": "ic_config", "Timestamp": 1669385083643700}}, "group_replication_start_on_boot": {"Value": "ON", "Metadata": {"H
ost": "source", "User": "ic_config", "Timestamp": 1669396354916102}}, "group_replication_bootstrap_group": {"Value": "OFF", "Metadata":
{"Host": "source", "User": "ic_config", "Timestamp": 1669396289286814}}, "group_replication_autorejoin_tries": {"Value": "3", "Metadat
a": {"Host": "source", "User": "ic_config", "Timestamp": 1669385083648696}}, "group_replication_recovery_use_ssl": {"Value": "ON", "Met
adata": {"Host": "source", "User": "ic_config", "Timestamp": 1669396354905448}}, "group_replication_view_change_uuid": {"Value": "f211b
7c5-6ce3-11ed-8310-000c298d6cb9", "Metadata": {"Host": "source", "User": "ic_config", "Timestamp": 1669396354900357}}, "group_replicati
on_exit_state_action": {"Value": "READ_ONLY", "Metadata": {"Host": "source", "User": "ic_config", "Timestamp": 1669385083642071}}, "gro
up_replication_communication_stack": {"Value": "MYSQL", "Metadata": {"Host": "source", "User": "ic_config", "Timestamp": 16693963549177
88}}, "group_replication_single_primary_mode": {"Value": "ON", "Metadata": {"Host": "source", "User": "ic_config", "Timestamp": 1669396
354903861}}, "group_replication_member_expel_timeout": {"Value": "5", "Metadata": {"Host": "source", "User": "ic_config", "Timestamp":
1669396354914411}}, "group_replication_transaction_size_limit": {"Value": "150000000", "Metadata": {"Host": "source", "User": "ic_confi
g", "Timestamp": 1669396354919408}}, "group_replication_enforce_update_everywhere_checks": {"Value": "OFF", "Metadata": {"Host": "sourc
e", "User": "ic_config", "Timestamp": 1669396354902128}}}, "mysql_dynamic_variables": {"server_id": {"Value": "1250867127", "Metadata":
{"Host": "localhost", "User": "root", "Timestamp": 1669189433995363}}, "super_read_only": {"Value": "ON", "Metadata": {"Host": "source
", "User": "ic_config", "Timestamp": 1669396354896290}}, "auto_increment_offset": {"Value": "2", "Metadata": {"Host": "source", "User":
"ic_config", "Timestamp": 1669396354922981}}, "auto_increment_increment": {"Value": "1", "Metadata": {"Host": "source", "User": "ic_co
nfig", "Timestamp": 1669396354921476}}, "group_replication_consistency": {"Value": "EVENTUAL", "Metadata": {"Host": "source", "User": "
ic_config", "Timestamp": 1669396354912041}}, "binlog_transaction_dependency_tracking": {"Value": "WRITESET", "Metadata": {"Host": "loca
lhost", "User": "root", "Timestamp": 1669189433989081}}}}
~
~
~
~
~
"mysqld-auto.cnf" 1L, 3838C written
改完需重启。重启后查看数据库可知， group_replication_local_address 成功地被修改为 r2-cnf ，而元数据库表依旧保持不变。
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-persist:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
mysql> select @@group_replication_local_address;
+-----------------------------------+
| @@group_replication_local_address |
+-----------------------------------+
| replica2:3306 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select @@group_replication_local_address;
No connection. Trying to reconnect...
Connection id: 27
Current database: mysql_innodb_cluster_metadata
+-----------------------------------+
| @@group_replication_local_address |
+-----------------------------------+
| r2-cnf:3306 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-persist:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
直接修改mysql_innodb_cluster_metadata.instances表中addresses.grLocal值为 r2-table:3306 。让我们看看会发生什么。
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-persist:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
mysql> update instances set addresses=json_replace(addresses,'$.grLocal','r2-table:3306') where instance_name='replica3:3306';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-table:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
重启一下呢？
mysql> select * from instances where instance_name='replica3:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica3:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-table:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+---------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
再看看 DATADIR/mysqld-auto.cnf 文件。
grep local_address mysqld-auto.cnf
group_replication_local_address 值还是刚刚设置的 r2-cnf 。
该 MySQL 成员实例上的 group_replication_local_address 值也还是刚刚设置的 r2-cnf 。
至此，没仔细看的小伙伴是不是有点懵？我们来梳理一下 group_replication_local_address 一共被赋予或可能出现几个值（省略端口号3306）。
- 初始值，等于成员实例的旧名称，ic-replica2 ；
- 第一次持久化赋予的值，r2-persist ；
- 修改主机名后期望出现的值，replica3 ；
SET GLOBAL给的值，r2-global ；cluster.options中成员实例选项localAddress值，实时同步，等于GLOBAL值；- 元数据库表
mysql_innodb_cluster_metadata.instances中addresses.grLocal值，该值不随着group_replication_local_address 变量的运行时值变化，为成员加入集群时的值； - DATADIR/mysqld-auto.cnf 文件中的值，等同于
SET PERSIST_ONLY，属于持久化的值。
直接修改元数据库中的表
直接修改mysql_innodb_cluster_metadata.instances表中instance_name。
mysql> update instances set instance_name='replica2.ic:3306' where instance_name='replica3:3306';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from instances where instance_name='replica2.ic:3306';
+-------------+--------------------------------------+---------------+--------------------------------------+------------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description |
+-------------+--------------------------------------+---------------+--------------------------------------+------------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| 4 | f465b0e3-6ce3-11ed-8310-000c298d6cb9 | replica3:3306 | 2737b324-a0f6-11ea-afd1-000c2988ff33 | replica2.ic:3306 | {"mysqlX": "replica3:33060", "grLocal": "r2-table:3306", "mysqlClassic": "replica3:3306"} | {"joinTime": "2022-11-26 01:12:34.768", "server_id": 1250867127, "recoveryAccountHost": "%", "recoveryAccountUser": "mysql_innodb_cluster_1250867127"} | NULL |
+-------------+--------------------------------------+---------------+--------------------------------------+------------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)
cluster.status()结果：
MySQL ic-source:33060+ ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-source:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-replica1:3306": {
"address": "ic-replica1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-source:3306": {
"address": "ic-source:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"replica2.ic:3306": {
"address": "replica3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ic-source:3306"
}
cluster.options()结果：
MySQL ic-source:33060+ ssl JS > cluster.options()
"replica2.ic:3306": [
{
"option": "autoRejoinTries",
"value": "3",
"variable": "group_replication_autorejoin_tries"
},
可见，成员实例名称已经随该元数据库表中相应值的改变而改变为 replica2.ic 了。这虽只是个名称，但 MySQL Shell 默认将它与其他选项配置得一样，如果不匹配，后续使用 MySQL Shell 可能存在潜在问题。所以最好还是将其设置为与其他选项保持一致。
使用cluster.setInstanceOption()方法修改label选项
使用cluster.setInstanceOption('旧成员实例名称','label','新成员实例名称')来修改成员实例名称。
MySQL ic-source:33060+ ssl JS > cluster.setInstanceOption('replica3:3306','label','replica2.ic')
Setting the value of 'label' to 'replica2.ic' in the instance: 'replica3:3306' ...
Successfully set the value of 'label' to 'replica2.ic' in the cluster member: 'replica3:3306'.
cluster.status()结果：
MySQL ic-source:33060+ ssl JS > cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-source:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-replica1:3306": {
"address": "ic-replica1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"ic-source:3306": {
"address": "ic-source:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
},
"replica2.ic": {
"address": "replica3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.31"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "ic-source:3306"
}
cluster.options()结果：
MySQL ic-source:33060+ ssl JS > cluster.options()
...
"tags": {
"global": [],
"ic-replica1:3306": [],
"ic-source:3306": [],
"replica2.ic": []
},
...
"replica2.ic": [
注意，这里有个问题，我故意拿 replica3:3306 作为cluster.setInstanceOption()方法的实例名称参数，却依然可以运行，证明旧的实例名称并没有完全失效。那是主机缓存的问题吗？详见 详解 InnoDB Cluster 主机名问题
修改已有集群实例的成员实例选项
先介绍正确的修改方法：
- 使用 Cluster 类提供的
setInstanceOption()方法。该方法仅支持修改它所支持的选项，而非options()方法列出的所有选项。 - 手动执行
SET PERSIST语句修改该实例的相应动态变量。静态变量可能无法在线修改，需要重启。该方法仅支持修改部分选项。 - 修改 DATADIR/mysqld-auto.cnf 文件中的相应内容，然后重启该 MySQL Server ，并执行
cluster.rescan()。该方法仅支持修改部分选项。 - 直接修改元数据库中的表
mysql_innodb_cluster_metadata.instances中的相应记录。该方法仅支持修改部分选项。
以下内容暂时不要看，没写完。
修改 DATADIR/mysqld-auto.cnf 文件
MySQL 数据目录下，执行：
vi mysqld-auto.cnf
#如果你只想修改实例名称，可以不加 g ；否则建议加 g ，全部修改以防止某些因变量值不匹配导致的异常。
:%s/ic-replica2/ic-replica2\.ic/g
修改后查看cluster.status()、cluster.options()和mysql_innodb_cluster_metadata.instances表均无变化。
在 ic-replica2 服务器上重启 MySQL 服务器实例，
mysql> restart;
或
systemctl restart mysqld
或
service mysqld restart
然后执行cluster.status()发现成员实例故障，
遵循报错提示cluster.rescan()，
手动执行SET PERSIST
例如，执行如下命令，修改 group_replication_local_address 变量：
mysql> set persist group_replication_local_address='ic-source.ic:3306';
cluster.options()显示已修改成功：
配置文件里修改了：
查数据库发现元数据没有同步修改，重启依旧如此。
证明这种方法虽然可以修改部分系统变量，但存在风险，因为这些变量原本是由 MySQL Shell API 自动配置的，被存入了元数据库中。如果修改后与元数据不匹配会导致 InnoDB Cluster 因部分节点存在故障而导致无法容错、高可用。
重要提示
主机名相关内容是在添加实例时定义的，如果仅修改部分变量中的主机名，会导致与其他变量不匹配而出现错误，尤其是复制、组复制相关的变量，比如 group_replication_local_address 。如需修改请直接修改 操作系统的主机名设置 。
无效的方法
修改之前，我参照了 MySQL Shell JS API 中的 Cluster 类方法 ，发现无法修改成员实例的地址标签。
MySQL ic-source:3306 ssl JS > cluster.setInstanceOption('ic-replica1:3306','localAddress','replica1:3306')
Cluster.setInstanceOption: Option 'localAddress' not supported. (ArgumentError)
MySQL ic-source:3306 ssl JS >
而后我又试了tag:<option>这种方式，发现并非定义中所说的与集群相关的“内置”和自定义标记（可能这里所谓的“内置”是指下面这两个隐藏的预定义标记？）。这里的option仅指 MySQL Shell JS API 8.0 文档中 该方法的描述里出现的已定义的选项 ，并非所有出现在cluster.options()中的选项。
英文原文注释
The following pre-defined tags are available:
- _hidden: bool - instructs the router to exclude the instance from the list of possible destinations for client applications.
- _disconnect_existing_sessions_when_hidden: bool - instructs the router to disconnect existing connections from instances that are marked to be hidden.
Note
注释
标记可以将自定义键/值对关联到集群，并将其存储在其元数据中。自定义标记名可以是以字母开头，后跟字母、数字和_的任何字符串。标记值可以是任何 JSON 值。如果值为空，则删除标记。
我做了一些tag:<option>尝试，发现不行，非预期效果，就仅仅是标记、注释，即便和已定义的 option 同名也无法修改。
而这些也可以通过 mysql 客户端使用mysql_innodb_cluster_metadata数据库中的表查到：
mysql> select * from clusters;
mysql> select * from instances;
而我真正想要修改的Cluster类中setOption或setInstanceOption中已定义的、支持的option 却没有被修改。而且将我刚刚定义的 tag 通过设置为null将其删除后，虽然通过cluster.options()查看不到了，但 tag 本身却被残留了。如果做得更好一点，空 tag 没有存在的意义，应自动删除。而目前的 MySQL 版本显然并没有这么处理。
下图中可以看出，集群的 tag 残留了，但实例的却并没有。而实际上实例的也残留了，可通过 MySQL 数据库中的表验证。
查看相应的表，发现无论是集群还是实例都有 tag 残留。
集群中的 tag 残留：
实例中的 tag 残留：
2908
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
