InnoDB-Cluster ist offenbar die Alternative zum Galera-Cluster und existiert nur für MySQL (nicht für MariaDB).
Es wird eine aktuelle MySQL-Version 8 und die MySQL-Shell (ich habe hier die LTS-Version verwendet) benötigt.
So wird die MySQL-Shell benutzt, um die Konfiguration der künftigen Cluster-Nodes zu prüfen:
root@inno1:~# mysqlsh
MySQL Shell 8.4.3
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
MySQL SQL > \js
Switching to JavaScript mode...
MySQL JS > dba.checkInstanceConfiguration('root@192.168.131.32:3306')
Please provide the password for 'root@192.168.131.32:3306': *********
Save password for 'root@192.168.131.32:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Validating MySQL instance at inno2:3306 for use in an InnoDB Cluster...
This instance reports its own address as inno2: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...
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 |
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
| server_id | 1 | <unique ID> | Update read-only variable and restart the server |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
Some variables need to be changed, but cannot be done dynamically on the server.
NOTE: Please use the dba.configureInstance() command to repair these issues.
{
"config_errors": [
{
"action": "server_update",
"current": "COMMIT_ORDER",
"option": "binlog_transaction_dependency_tracking",
"required": "WRITESET"
},
{
"action": "server_update+restart",
"current": "OFF",
"option": "enforce_gtid_consistency",
"required": "ON"
},
{
"action": "server_update+restart",
"current": "OFF",
"option": "gtid_mode",
"required": "ON"
},
{
"action": "server_update+restart",
"current": "1",
"option": "server_id",
"required": "<unique ID>"
}
],
"status": "error"
}
Sind die Werte angepasst und die Datenbank durchgestartet worden, sollte das Ergebnis so aussehen:
MySQL JS > dba.checkInstanceConfiguration('root@192.168.131.32:3306')
Validating MySQL instance at inno2:3306 for use in an InnoDB Cluster...
This instance reports its own address as inno2: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 'inno2:3306' is valid for InnoDB Cluster usage.
{
"status": "ok"
}
Um jetzt ein Cluster zu erzeugen, loggt man sich mit der MySQL-Shell auf dem ersten Node ein, ruft db.configureInstance() auf und erzeugt dann mit dba.createCluster() das Cluster:
root@inno1:~# mysqlsh -u root -h 192.168.131.31
MySQL Shell 8.4.3
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@192.168.131.31'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 12 (X protocol)
Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)
No default schema selected; type \use <schema> to set one.
MySQL 192.168.131.31:33060+ ssl SQL > \js
Switching to JavaScript mode...
MySQL 192.168.131.31:33060+ ssl JS > dba.configureInstance('inno1:3306')
Please provide the password for 'root@inno1:3306': *********
Save password for 'root@inno1:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring local MySQL instance listening at port 3306 for use in an InnoDB Cluster...
This instance reports its own address as inno1: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.
The instance 'inno1:3306' is valid for InnoDB Cluster usage.
Successfully enabled parallel appliers.
MySQL 192.168.131.31:33060+ ssl JS > cluster = dba.createCluster('mycluster')
A new InnoDB Cluster will be created on instance 'inno1:3306'.
Validating instance configuration at 192.168.131.31:3306...
This instance reports its own address as inno1:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'inno1:3306'. Use the localAddress option to override.
* Checking connectivity and SSL configuration...
Creating InnoDB Cluster 'mycluster' on 'inno1: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.
<Cluster:mycluster>
Weitere Nodes/Instanzen werden dann wie folgt dem Cluster hinzugefügt:
MySQL 192.168.131.31:33060+ ssl JS > cluster.addInstance('root@inno2:3306')
NOTE: The target instance 'inno2:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'inno2:3306' 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'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at inno2:3306...
This instance reports its own address as inno2:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'inno2:3306'. Use the localAddress option to override.
* Checking connectivity and SSL configuration...
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: inno2:3306 is being cloned from inno1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: inno2:3306 is shutting down...
* Waiting for server restart... ready
* inno2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.70 MB transferred in 2 sec (37.35 MB/s)
State recovery already finished for 'inno2:3306'
The instance 'inno2:3306' was successfully added to the cluster.
Den aktuellen Cluster Status kann man sich jetzt z. B. so ansehen:
MySQL 192.168.131.31:33060+ ssl JS > cluster = dba.getCluster('mycluster')
<Cluster:mycluster>
MySQL 192.168.131.31:33060+ ssl JS > cluster.status();
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "inno1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"inno1:3306": {
"address": "inno1:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.39"
},
"inno2:3306": {
"address": "inno2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.39"
},
"inno3:3306": {
"address": "inno3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.0.39"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "inno1:3306"
}
Übrigens muß der entsprechende Cluster-Account seit MySQL8 so eingerichtet werden
CREATE USER 'root'@'%' IDENTIFIED BY 'a-password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
da GRANT-Rechte nicht mehr im ersten Schritt mit eingerichtet werden können.