InnoDB Cluster で過去のバイナリログが存在しない状況での復旧方法 missing transactions that were purged from all cluster members. (RuntimeError)

MySQL
覚書

既存のクラスターのどれか一つに入る

 MySQL  db02.luis.local:33060+ ssl  JS > \c [email protected]
Creating a session to '[email protected]'
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 2145 (X protocol)
Server version: 8.0.23 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL  db01.luis.local:33060+ ssl  JS > c = dba.getCluster()
<Cluster:main>
MySQL  db01.luis.local:33060+ ssl  JS > c.status()
{
"clusterName": "main",
"defaultReplicaSet": {
"name": "default",
"primary": "db03.luis.local:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.",
"topology": {
"db01.luis.local:3306": {
"address": "db01.luis.local:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.23"
},
"db02.luis.local:3306": {
"address": "db02.luis.local:3306",
"instanceErrors": [
"WARNING: Instance is NOT a PRIMARY but super_read_only option is OFF.",
"WARNING: server_uuid for instance has changed from its last known value. Use cluster.rescan() to update the metadata."
],
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)",
"version": "8.0.23"
},
"db03.luis.local:3306": {
"address": "db03.luis.local:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.23"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db03.luis.local:3306"
}

db02 を rejoin しても過去のバイナリログがないためリカバリができない

 MySQL  db01.luis.local:33060+ ssl  JS > c.rejoinInstance('[email protected]')
ERROR: A GTID set check of the MySQL instance at 'db02.luis.local:3306' determined that it is missing transactions that were purged from all cluster members.
Cluster.rejoinInstance: The instance 'db02.luis.local:3306' is missing transactions that were purged from all cluster members. (RuntimeError)

db02 をクラスターから外す force: true

 MySQL  db01.luis.local:33060+ ssl  JS > c.removeInstance('[email protected]', {force: true})
NOTE: db02.luis.local:3306 is reachable but has state OFFLINE
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.
NOTE: The recovery user name for instance 'db02.luis.local:3306' does not match the expected format for users created automatically by InnoDB Cluster. Skipping its removal.
NOTE: Transaction sync was skipped
NOTE: The instance 'db02.luis.local:3306' is OFFLINE, Group Replication stop skipped.
ERROR: Instance 'db02.luis.local:3306' failed to leave the cluster: db02.luis.local:3306: Slave channel 'group_replication_applier' does not exist.
The instance 'db02.luis.local:3306' was successfully removed from the cluster.
MySQL  db01.luis.local:33060+ ssl  JS > c.status()
{
"clusterName": "main",
"defaultReplicaSet": {
"name": "default",
"primary": "db03.luis.local:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"db01.luis.local:3306": {
"address": "db01.luis.local:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.23"
},
"db03.luis.local:3306": {
"address": "db03.luis.local:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.23"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db03.luis.local:3306"
}

db02 を再度入れる

 MySQL  db01.luis.local:33060+ ssl  JS > c.addInstance('[email protected]')
NOTE: A GTID set check of the MySQL instance at 'db02.luis.local:3306' determined that it is missing transactions that were purged from all cluster members.
NOTE: The target instance 'db02.luis.local:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether clone based recovery is safe to use.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'db02.luis.local:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
Please select a recovery method [C]lone/[A]bort (default Abort): C
Validating instance configuration at db02.luis.local:3306...
This instance reports its own address as db02.luis.local:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db02.luis.local: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: db02.luis.local:3306 is being cloned from db01.luis.local:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY  ############################################################  100%  Completed
PAGE COPY  ############################################################  100%  Completed
REDO COPY  ############################################################  100%  Completed
NOTE: db02.luis.local:3306 is shutting down...
* Waiting for server restart... ready
* db02.luis.local:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 1.94 GB transferred in 17 sec (113.86 MB/s)
State recovery already finished for 'db02.luis.local:3306'
The instance 'db02.luis.local:3306' was successfully added to the cluster.
MySQL  db01.luis.local:33060+ ssl  JS > c.status()
{
"clusterName": "main",
"defaultReplicaSet": {
"name": "default",
"primary": "db03.luis.local:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db01.luis.local:3306": {
"address": "db01.luis.local:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.23"
},
"db02.luis.local:3306": {
"address": "db02.luis.local:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.23"
},
"db03.luis.local:3306": {
"address": "db03.luis.local:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.23"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db03.luis.local:3306"
}

一月何も記事を書いてなかったので Tips です。

コメントをキャンセル