InnoDB Cluster Repair

Databases

MySQL

MySQL High Availability

InnoDB Cluster

"status": "(MISSING)"

Solution #1: Rebuild Cluster

ERROR: group_replication has stopped with an error

Solution #2: Kick problem instance from cluster and add again

Solution #3: Kick problem instance from cluster and add again

Bibliography

InnoDB Cluster Diagnostics

"status": "(MISSING)"

Solution #1: Rebuild Cluster

On Primary...

mysqlsh

\c icadmin@node1

var cluster = dba.getCluster()

Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (MYSQLSH 51314)

dba.checkInstanceConfiguration()

\c root@localhost

dba.configureInstance()

select option 3 "Ignore and continue" (assumes icadmin@node1 already exists)

\c icadmin@node1

dba.checkInstanceConfiguration()

var cluster = dba.createCluster('node1',{communicationStack@ "mysql"})

Dba.createCluster: dba.createCluster: Unable to create cluster. The instance 'node1:3306' has a populated Metadata schema and belongs to that Metadata. Use either dba.dropMetadataSchema() to drop the schema, or dba.rebootClusterFromCompleteOutage() to reboot the cluster from complete outage. (RuntimeError)

dba.dropMetadataSchema()

var cluster = dba.createCluster('node1',{communicationStack@ "mysql"})

A new InnoDB Cluster will be created on instance 'node1:3306'.
Validating instance configuration at node1:3306...
This instance reports its own address as node1:3306
Instance configuration is suitable.NOTE: Group Replication will communicate with other members using 'node1:3306'. Use the localAddress option to override.
* Checking connectivity and SSL configuration...
Creating InnoDB Cluster 'mycluster' on 'node1:3306'...
Adding Seed Instance...NOTE: User 'mysql_innodb_cluster_1790596319'@'%' already existed at instance 'node1:3306'. It will be deleted and created again with a new password.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.status()

{ "clusterName": "mycluster", "defaultReplicaSet": { "name": "default", "primary": "node1:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "ukbrc-llsql01.cde.local:3306": { "address": "node1:3306", "instanceErrors": [ "WARNING: Detected unused recovery accounts: mysql_innodb_cluster_2596080979, mysql_innodb_cluster_622647909. Use Cluster.rescan() to clean up." ], "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.44" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "node1:3306"}

cluster.rescan()

Rescanning the cluster...
Result of the rescanning operation for the 'mycluster' cluster:{ "metadataConsistent": true, "name": "mycluster", "newTopologyMode": null, "newlyDiscoveredInstances": [], "unavailableInstances": [], "updatedInstances": []}
Dropping unused recovery account: 'mysql_innodb_cluster_2596080979'@'%'Dropping unused recovery account: 'mysql_innodb_cluster_622647909'@'%'

cluster.status()

{ "clusterName": "mycluster", "defaultReplicaSet": { "name": "default", "primary": "node1:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "node1:3306": { "address": "node1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.44" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "node1:3306"}

cluster.addInstance('icadmin@node2:3306',{recoveryMethod: "clone"})

ERROR: RuntimeError: The instance 'node2:3306' is already part of another InnoDB ClusterCluster.addInstance: The instance 'node2:3306' is already part of another InnoDB Cluster (RuntimeError)

\c icadmin@node2

\sql

SELECT * FROM performance_schema.replication_group_members;

stop group_replication;

\js

dba.dropMetadataSchema()

Are you sure you want to remove the Metadata? [y/N]: yMetadata Schema successfully removed.

\c icadmin@node1

\js

var cluster = dba.getCluster()

cluster.status()

cluster.addInstance('icadmin@node2:3306',{recoveryMethod: "clone"})

WARNING: A GTID set check of the MySQL instance at 'node2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
node2:3306 has the following errant GTIDs that do not exist in the cluster:a0df7393-27ad-11f1-85e8-00505694acf6:1
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of node2: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.Clone based recovery selected through the recoveryMethod option
Validating instance configuration at node2:3306...
This instance reports its own address as node2:3306
Instance configuration is suitable.NOTE: Group Replication will communicate with other members using 'node2: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: node2:3306 is being cloned from node1:3306
** Stage DROP DATA: Completed
** Clone Transfer FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed
NOTE: node2:3306 is shutting down...
* Waiting for server restart... readynode2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed* Clone process has finished: 2.81 GB transferred in 14 sec (200.57 MB/s)
State recovery already finished for 'node2:3306'
The instance 'node2:3306' was successfully added to the cluster.

cluster.status()

{ "clusterName": "mycluster", "defaultReplicaSet": { "name": "default", "primary": "node1l:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "node1:3306": { "address": "node1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.44" }, "node2:3306": { "address": "node2:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.44" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "node1:3306"}

\c icadmin@node3

\sql

SELECT * FROM performance_schema.replication_group_members;

stop group_replication;

\js

dba.dropMetadataSchema()

WARNING: A GTID set check of the MySQL instance at 'node3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
node2:3306 has the following errant GTIDs that do not exist in the cluster:5adf7393-27ad-11f1-85e8-0050569e46a6:1
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of node3: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.Clone based recovery selected through the recoveryMethod option
Validating instance configuration at node3:3306...
This instance reports its own address as node3:3306
Instance configuration is suitable.NOTE: Group Replication will communicate with other members using 'node3: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: node3:3306 is being cloned from node2:3306
** Stage DROP DATA: Completed
** Clone Transfer FILE COPY ############################################################ 100% Completed PAGE COPY ############################################################ 100% Completed REDO COPY ############################################################ 100% Completed
NOTE: node3:3306 is shutting down...
* Waiting for server restart... readynode3:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed* Clone process has finished: 2.81 GB transferred in 14 sec (200.57 MB/s)
State recovery already finished for 'node2:3306'
The instance 'node3:3306' was successfully added to the cluster.

cluster.status()

{ "clusterName": "mycluster", "defaultReplicaSet": { "name": "default", "primary": "node1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "node1:3306": { "address": "node1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.44" }, "node2:3306": { "address": "node2:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.44" }, "node3:3306": { "address": "node3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.44" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "node1:3306"}

cluster.status({extended:3})

cluster.rescan()

Select Y to remove node from the cluster when prompted

ERROR: group_replication has stopped with an error

Solution #2: Kick problem instance from cluster and add again

On Primary...

mysqlsh

var cluster = dba.getCluster()

cluster.status()

cluster.status({extended:3})

cluster.rescan()

Select Y to remove node from the cluster when prompted

"node3:3306": { "address": "node3:3306", "instanceErrors": [ "ERROR: group_replication has stopped with an error." ], "memberRole": "SECONDARY", "memberState": "ERROR", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "(MISSING)", "version": "8.0.24" }

On problem node...

mysqlsh

\sql

STOP GROUP_REPLICATION;

RESET REPLICA ALL;

SET GLOBAL super_read_only = OFF;

DROP DATABASE mysql_innodb_cluster_metadata;

On Primary...

mysqlsh

var cluster = dba.getCluster()

cluster.addInstance('icadmin@node3:3306',{recoveryMethod: "clone"})

Select C to clone when prompted

cluster.status()

Solution #3: Kick problem instance from cluster and add again

On any node...

mysqlsh

var cluster = dba.getCluster()

cluster.status()

cluster.status({extended:3})

cluster.removeInstance('icadmin@node3:3306')

cluster.addInstance('icadmin@node3:3306',{recoveryMethod: "clone"})

cluster.rescan()

Bibliography

https://dba.stackexchange.com/questions/186268/how-to-resolve-innodb-cluster-conflicthttps://dba.stackexchange.com/questions/246099/relay-log-read-fail-relay-or-master-log-currupt-how-to-repairhttps://dba.stackexchange.com/questions/53893/mysql-relay-log-corrupted-how-do-i-fix-it-tried-but-failed
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-working-with-cluster.htmlhttps://dev.mysql.com/doc/mysql-shell/8.0/en/troubleshooting-innodb-cluster.html
https://ahelpme.com/software/mysql/recovery-of-mysql-8-cluster-instance-after-server-crash-and-corrupted-data-in-log-event/
https://forums.oracle.com/ords/apexds/post/mysql-innodb-cluster-node-stucked-on-recovering-2196

Page updated

Google Sites

Report abuse