Mysql – Cannot rejoin or add instance to MySQL InnoDB cluster

group-replicationinnodbMySQLmysql-clustermysql-innodb-cluster

I have a MySQL InnoDB cluster running where I had added three nodes, one is having the Primary role and the others are Secondary. Today morning, the two secondary nodes were removed from the cluster after I restored a backup in a test database on the primary server and then deleted the backup before it was replicated to the other two secondary nodes. Now, when I want to rejoin or add those secondary databases back to the cluster, they cannot be added. Below are the screenshots of the error messages I get.

When I want to rejoinInstance(), I get the below error:

MySQL  pdb1-mod:3306 ssl  JS > cluster.rejoinInstance('root@ddb-mod')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Rejoining instance to the cluster ...

Please provide the password for 'root@ddb-mod:3306': ************
Cluster.rejoinInstance: The instance 'ddb-mod:3306' does not belong to the ReplicaSet: 'default'. 
(RuntimeError)  

When I want to add it through the AddInstance() method, I get the following error:

 MySQL  pdb1-mod:3306 ssl  JS > cluster.addInstance('root@ddb-mod')
 Please provide the password for 'root@ddb-mod': ************
 The safest and most convenient way to provision a new instance is through
 automatic clone provisioning, which will completely overwrite the state of
 'ddb-mod:3306' with a physical snapshot from an existing cluster member. To use
 this method by default, set the 'recoveryMethod' option to 'clone'.

 The incremental distributed 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'.

 Incremental distributed state recovery was selected because it seems to be safely usable.
 Validating instance at ddb-mod:3306...

 This instance reports its own address as ddb-mod:3306

 Instance configuration is suitable.
 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...

 WARNING: User 'mysql_innodb_cluster_622884765'@'%' already existed at instance 'pdb1-mod:3306'. It 
 will be deleted and created again with a new password.
 ERROR: Unable to start Group Replication for instance 'ddb-mod:3306'. Please check the MySQL server 
 error log for more information.
 Cluster.addInstance: Group Replication failed to start: MySQL Error 3092 (HY000): The server is not 
 configured properly to be an active member of the group. Please see more details on error log. 
 (RuntimeError)  

I googled it many times but did not get enough help from there.
Any help is appreciated in advance. Thank you.

Best Answer

This has been fixed in MySQL Shell 8.0.23: https://dev.mysql.com/doc/relnotes/mysql-shell/8.0/en/news-8-0-23.html

An instance that belongs to an InnoDB Cluster is identified by its server UUID. If the UUID changed after the instance had left the cluster, for example because you used MySQL Enterprise Backup to restore from a backup, then the instance could not be rejoined to the cluster. Now, if the cluster encounters this situation, it checks the metadata to see if the instance can be identified using its host and port. If found, the metadata is updated based on the options used for the rejoin operation. This check is executed during the Cluster.rejoinInstance() and Cluster.rescan() operations.

Additionally, a check is executed to verify the serverId of all the instances is registered in the metadata as an instance attribute. If it is not, the metadata is updated accordingly. This check is executed on add, rejoin and rescan operations. (Bug #26649039)

Please upgrade MySQL Shell to 8.0.23.