【博客文章2024】MySQL Innodb Cluster 8.0.36组复制的单主/多主模式切换

 [root@node0 mysql-software]# mysqlsh
MySQL Shell 8.0.36

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect root@node0:3306
Creating a session to 'root@node0:3306'
Please provide the password for 'root@node0:3306': *********
Save password for 'root@node0:3306'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 23
Server version: 8.0.36 MySQL Community Server - GPL
No default schema selected; type \use to set one.
 MySQL  node0:3306 ssl  JS > var cluster=getCluster('testCluster);

 MySQL  node0:3306 ssl  JS > cluster.status();
Cluster.status: The InnoDB Cluster topology type (Single-Primary) does not match the current Group Replication configuration (Multi-Primary). Please use .rescan() or change the Group Replication configuration accordingly. (RuntimeError)

MySQL  node0:3306 ssl  JS > cluster.rescan();
Rescanning the cluster...

Result of the rescanning operation for the 'testCluster' cluster:
{
    "name": "testCluster",
    "newTopologyMode": "Multi-Primary",
    "newlyDiscoveredInstances": [],
    "unavailableInstances": [],
    "updatedInstances": []
}

NOTE: The Cluster is not configured to use 'group_replication_view_change_uuid', which is required for InnoDB ClusterSet. Configuring it requires a full Cluster reboot.
Would you like 'group_replication_view_change_uuid' to be configured automatically? [Y/n]: Y
NOTE: The Cluster's group_replication_view_change_uuid is not set.
Generating and setting a value for group_replication_view_change_uuid...
NOTE: The Cluster must be completely taken OFFLINE and restarted (dba.rebootClusterFromCompleteOutage()) for the settings to be effective
Updating group_replication_view_change_uuid in the Cluster's metadata...
Updating group_replication_transaction_size_limit in the Cluster's metadata...
NOTE: The topology mode of the cluster changed to 'Multi-Primary'.
Updating topology mode in the cluster metadata...
Topology mode was successfully updated to 'Multi-Primary' in the cluster metadata.

MySQL  node0:3306 ssl  JS > dba.rebootClusterFromCompleteOutage('testCluster',{force:true});
Restoring the Cluster 'testCluster' from complete outage...

Cluster instances: 'node0.example.com:3306' (ONLINE), 'node1.example.com:3306' (ONLINE), 'node2.example.com:3306' (ONLINE), 'node3.example.com:3306' (ONLINE), 'node4.example.com:3306' (ONLINE), 'node5.example.com:3306' (ONLINE), 'node6.example.com:3306' (ONLINE)
Waiting for instances to apply pending received transactions...
Validating instance configuration at node0:3306...

This instance reports its own address as node0.example.com:3306

Instance configuration is suitable.
* Waiting for seed instance to become ONLINE...
node0.example.com:3306 was restored.
Validating instance configuration at node1.example.com:3306...

This instance reports its own address as node1.example.com:3306

Instance configuration is suitable.
Rejoining instance 'node1.example.com:3306' to cluster 'testCluster'...

The instance 'node1.example.com:3306' was successfully rejoined to the cluster.

Validating instance configuration at node2.example.com:3306...

This instance reports its own address as node2.example.com:3306

Instance configuration is suitable.
Rejoining instance 'node2.example.com:3306' to cluster 'testCluster'...

The instance 'node2.example.com:3306' was successfully rejoined to the cluster.

Validating instance configuration at node3.example.com:3306...

This instance reports its own address as node3.example.com:3306

Instance configuration is suitable.

Rejoining instance 'node3.example.com:3306' to cluster 'testCluster'...

The instance 'node3.example.com:3306' was successfully rejoined to the cluster.

Validating instance configuration at node4.example.com:3306...

This instance reports its own address as node4.example.com:3306

Instance configuration is suitable.
Rejoining instance 'node4.example.com:3306' to cluster 'testCluster'...

The instance 'node4.example.com:3306' was successfully rejoined to the cluster.

Validating instance configuration at node5.example.com:3306...

This instance reports its own address as node5.example.com:3306

Instance configuration is suitable.
Rejoining instance 'node5.example.com:3306' to cluster 'testCluster'...

The instance 'node5.example.com:3306' was successfully rejoined to the cluster.

Validating instance configuration at node6.example.com:3306...

This instance reports its own address as node6.example.com:3306

Instance configuration is suitable.
Rejoining instance 'node6.example.com:3306' to cluster 'testCluster'...

The instance 'node6.example.com:3306' was successfully rejoined to the cluster.

The Cluster was successfully rebooted.

[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3911526469 |           0 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3698559545 |           0 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3770380176 |           0 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |   872561124 |           0 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  1433196438 |           0 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  1729370740 |           0 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  1221594379 |           0 |
+--------+-------------+-------------+

[root@node0 myrouter]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1092
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select  group_replication_switch_to_single_primary_mode() ;
+---------------------------------------------------+
| group_replication_switch_to_single_primary_mode() |
+---------------------------------------------------+
| Mode switched to single-primary successfully.     |
+---------------------------------------------------+
1 row in set (0.02 sec)

mysql> select   * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST       | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 0f7a81ed-c950-11ee-8276-5254003b94bf | node5.example.com |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
| group_replication_applier | 0fa42649-c94e-11ee-8ac8-5254005d656b | node2.example.com |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 161cb29f-c94f-11ee-83b3-525400657e88 | node3.example.com |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 4393f274-c94d-11ee-9748-6c3c8c2bd1fc | node0.example.com |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 5787c895-c94f-11ee-83bb-52540066a748 | node4.example.com |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | 7f41e521-c950-11ee-8606-5254004bcbf4 | node6.example.com |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
| group_replication_applier | a94a5033-c94d-11ee-9b1a-52540008a1c1 | node1.example.com |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
+---------------------------+--------------------------------------+-------------------+-------------+--------------+-------------+----------------+----------------------------+
7 rows in set (0.01 sec)

[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3698559545 |           1 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3770380176 |           1 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  1433196438 |           1 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  1221594379 |           1 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3911526469 |           1 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3698559545 |           1 |
+--------+-------------+-------------+
[root@node0 myrouter]# mysql -h node0 -P 6447 -e "select  @@port,@@server_id,@@read_only ; " -p
Enter password:
+--------+-------------+-------------+
| @@port | @@server_id | @@read_only |
+--------+-------------+-------------+
|   3306 |  3770380176 |           1 |
+--------+-------------+-------------+