mysql8.0.26-cluster 单主与多主

先配置三台服务器

系统版本
cat /etc/redhat-release 
Rocky Linux release 8.5 (Green Obsidian)

mysql版本  8.0.26

[root@zabbix-master1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.6 zabbix-master1
192.168.100.11 zabbix-master2
192.168.100.14 zabbix-master3



#下载 mysql-shell 
wget https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.28-1.el8.x86_64.rpm

[root@zabbix-master1 ~]# mysqlsh
MySQL Shell 8.0.28

Copyright (c) 2016, 2022, 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.

#我们依次输入cluster所有节点的地址  \c root@域名也可:Port
  MySQL  JS > \c root@192.168.100.6:3306
Creating a session to 'root@192.168.100.6:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 84
Please provide the password for 'root@192.168.100.6:3006': ************
Server version: 8.0.26 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.100.6:3306 ssl  JS > 

#三台机器都连接过数据库后，再连接各自MySQL执行下面操作
 MySQL  192.168.100.6:3306 ssl  JS > dba.configureInstance();   
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as zabbix-master1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+----------------------------+
| Variable                               | Current Value | Required Value | Note                       |
+----------------------------------------+---------------+----------------+----------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       | Update the server variable |
| replica_parallel_type                  | DATABASE      | LOGICAL_CLOCK  | Update the server variable |
| replica_preserve_commit_order          | OFF           | ON             | Update the server variable |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Configuring instance...
The instance 'zabbix-master1:3306' was configured to be used in an InnoDB cluster.

#检查节点配置实例状态是否为健康，用于加入cluster之前
MySQL  192.168.100.6:3306 ssl  JS > dba.checkInstanceConfiguration("root@192.168.100.6:3306");
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as zabbix-master1:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'zabbix-master1:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

#创建集群名称叫myCluster
MySQL  192.168.100.6:3306 ssl  JS > var cluster=dba.createCluster("myCluster");
A new InnoDB cluster will be created on instance '192.168.100.6:3306'.

Validating instance configuration at 192.168.100.6:3306...

This instance reports its own address as zabbix-master1:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'zabbix-master1:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'myCluster' on 'zabbix-master1:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

# 往集群里面添加节点
MySQL  192.168.100.6:3306 ssl  JS > cluster.addInstance("root@192.168.100.11:3306")

WARNING: A GTID set check of the MySQL instance at 'zabbix-master2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

zabbix-master2:3306 has the following errant GTIDs that do not exist in the cluster:
ccb17a91-983c-11ec-a058-000c299ab833:1-5

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of zabbix-master2:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

Please select a recovery method [C]lone/[A]bort (default Abort): C
Validating instance configuration at 192.168.100.11:3306...

This instance reports its own address as zabbix-master2:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'zabbix-master2:33061'. Use the localAddress option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: zabbix-master2:3306 is being cloned from zabbix-master1:3306
** Stage DROP DATA: Completed 
** Clone Transfer  
    FILE COPY  ============================================================    0%  In Progress
    PAGE COPY  ============================================================    0%  Not Started
    REDO COPY  ============================================================    0%  Not Started

NOTE: zabbix-master2:3306 is shutting down...

* Waiting for server restart... ready 
* zabbix-master2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.71 MB transferred in about 1 second (~72.71 MB/s)

State recovery already finished for 'zabbix-master2:3306'

The instance 'zabbix-master2:3306' was successfully added to the cluster.

# 往集群里面添加节点
MySQL  192.168.100.6:3306 ssl  JS > cluster.addInstance("root@192.168.100.14:3306")

WARNING: A GTID set check of the MySQL instance at 'zabbix-master3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

zabbix-master3:3306 has the following errant GTIDs that do not exist in the cluster:
02c8d10a-983f-11ec-a9f2-000c2929b6f0:1-4

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of zabbix-master3:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

Please select a recovery method [C]lone/[A]bort (default Abort): C
Validating instance configuration at 192.168.100.14:3306...

This instance reports its own address as zabbix-master3:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'zabbix-master3:33061'. Use the localAddress option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: zabbix-master3:3306 is being cloned from zabbix-master1:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: zabbix-master3:3306 is shutting down...

* Waiting for server restart... ready 
* zabbix-master3:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s)

State recovery already finished for 'zabbix-master3:3306'

The instance 'zabbix-master3:3306' was successfully added to the cluster.

MySQL  192.168.100.6:3306 ssl  JS > cluster.status();
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "zabbix-master1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "zabbix-master1:3306": {
                "address": "zabbix-master1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }, 
            "zabbix-master2:3306": {
                "address": "zabbix-master2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }, 
            "zabbix-master3:3306": {
                "address": "zabbix-master3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.26"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "zabbix-master1:3306"
}

#进入mysql 查询集群状态
mysql> select * from performance_schema.replication_group_members;

dba.checkInstanceConfiguration("root@hostname:3306")     #检查节点配置实例，用于加入cluster之前
 
dba.rebootClusterFromCompleteOutage('myCluster');        #重启 
 
dba.dropMetadataSchema();                                #删除schema
 
var cluster = dba.getCluster('myCluster')                #获取当前集群
 
cluster.checkInstanceState("root@hostname:3306")         #检查cluster里节点状态
 
cluster.rejoinInstance("root@hostname:3306")             #重新加入节点，我本地测试的时候发现rejoin一直无效，每次是delete后
 
addcluster.dissolve({force：true})                       #删除集群
 
cluster.addInstance("root@hostname:3306")                #增加节点
 
cluster.removeInstance("root@hostname:3306")             #删除节点
 
cluster.removeInstance('root@host:3306',{force:true})    #强制删除节点
 
cluster.dissolve({force:true})                           #解散集群
 
cluster.describe();                                      #集群描述

- ONLINE       - 节点状态正常。
- OFFLINE      - 实例在运行，但没有加入任何Cluster。
- RECOVERING   - 实例已加入Cluster，正在同步数据。
- ERROR        - 同步数据发生异常。
- UNREACHABLE  - 与其他节点通讯中断，可能是网络问题，可能是节点crash。
- MISSING      - 节点已加入集群，但未启动group replication

#如果查询节点状态为：
 "status": "(MISSING)"
#重新加入命令
shell.connect("root@node-1:3305");
cluster=dba.getCluster();
cluster.rejoinInstance("root@node-2:3305")
#如果rejoinInstance失败，提示remove重新添加如下：
cluster.removeInstance('root@node-1:3305');
cluster.addInstance('root@node-1:3305');

#切换到多主状态
MySQL  192.168.100.6:3306 ssl  JS > cluster.switchToMultiPrimaryMode()


Switching cluster 'myCluster' to Multi-Primary mode...

Instance 'zabbix-master3:3306' was switched from SECONDARY to PRIMARY.
Instance 'zabbix-master1:3306' remains PRIMARY.
Instance 'zabbix-master2:3306' was switched from SECONDARY to PRIMARY.

The cluster successfully switched to Multi-Primary mode.

#进入mysql 查询集群状态
mysql> select * from performance_schema.replication_group_members;