--Cluster creation and node addition after node read write made during
--off business hours for 3TB mysql database.
Scenario: both node of mysql innodb cluster rebooted by windows team patch by
mistake.
Split brain scenario came in to innodb cluster case. So one available mysql innodb
cluster node went into read only mode.
Resolution took more time to fix. So activated that available read only mode to
read write mode by breaking the group replication.
Mangement asked to create the cluster as like earlier at off business hours.
Here are the steps to create the INNODB cluster group replication at MYSQL DATABASE.
#createCluster #addInstance #rescan() #status
MySQL JS > \c adminlogin@DBCLUSTERNODEIP1:3306
Creating a session to 'adminlogin@DBCLUSTERNODEIP1:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 75944
Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL DBCLUSTERNODEIP1:3306 ssl JS > var cluster =dba.getCluster();
Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > var cluster =dba.getCluster();
Dba.getCluster: MySQL server has gone away (RuntimeError)
The global session got disconnected..
Attempting to reconnect to 'mysql://adminlogin@DBCLUSTERNODEIP1:3306'..
The global session was successfully reconnected.
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
TypeError: Cannot read property 'status' of undefined
MySQL DBCLUSTERNODEIP1:3306 ssl JS > dba.createCluster('prodclust');
A new InnoDB cluster will be created on instance 'DBCLUSTERNODEIP1:3306'.
Validating instance configuration at DBCLUSTERNODEIP1:3306...
This instance reports its own address as DBCLUSTERNODEIP1:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'DBCLUSTERNODEIP1:33061'. Use the localAddress option to override.
Creating InnoDB cluster 'prodclust' on 'DBCLUSTERNODEIP1:3306'...
Adding Seed Instance...
WARNING: User 'mysql_innodb_cluster_11'@'%' already existed at instance 'DBCLUSTERNODEIP1: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:prodclust>
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
TypeError: Cannot read property 'status' of undefined
MySQL DBCLUSTERNODEIP1:3306 ssl JS > var cluster =dba.getCluster();
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
{
"clusterName": "prodclust",
"defaultReplicaSet": {
"name": "default",
"primary": "DBCLUSTERNODEIP1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"DBCLUSTERNODEIP1:3306": {
"address": "DBCLUSTERNODEIP1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "DBCLUSTERNODEIP1:3306"
}
MySQL DBCLUSTERNODEIP1:3306 ssl JS > clus.addInstance('adminlogin@DBCLUSTERNODEIP2:3306');
ReferenceError: clus is not defined
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.addInstance('adminlogin@DBCLUSTERNODEIP2:3306');
Please provide the password for 'adminlogin@DBCLUSTERNODEIP2:3306': **************
Save password for 'adminlogin@DBCLUSTERNODEIP2:3306'? [Y]es/[N]o/Ne[v]er (default No): y
WARNING: A GTID set check of the MySQL instance at 'DBCLUSTERNODEIP2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
DBCLUSTERNODEIP2:3306 has the following errant GTIDs that do not exist in the cluster:
dfc8fe40-336a-11ed-a8ea-005056847352:49231601-49231871
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of DBCLUSTERNODEIP2: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
Cluster.addInstance: Cancelled (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
{
"clusterName": "prodclust",
"defaultReplicaSet": {
"name": "default",
"primary": "DBCLUSTERNODEIP1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"DBCLUSTERNODEIP1:3306": {
"address": "DBCLUSTERNODEIP1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "DBCLUSTERNODEIP1:3306"
}
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.addInstance('adminlogin@DBCLUSTERNODEIP3:3306');
Please provide the password for 'adminlogin@DBCLUSTERNODEIP3:3306': **************
Save password for 'adminlogin@DBCLUSTERNODEIP3:3306'? [Y]es/[N]o/Ne[v]er (default No): y
WARNING: A GTID set check of the MySQL instance at 'DBCLUSTERNODEIP3:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
DBCLUSTERNODEIP3:3306 has the following errant GTIDs that do not exist in the cluster:
dfc8fe40-336a-11ed-a8ea-005056847352:49231601-49231876
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of DBCLUSTERNODEIP3: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
NOTE: Group Replication will communicate with other members using 'DBCLUSTERNODEIP3:33061'. Use the localAddress option to override.
Validating instance configuration at DBCLUSTERNODEIP3:3306...
This instance reports its own address as DBCLUSTERNODEIP3: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_13'@'%' already existed at instance 'DBCLUSTERNODEIP1:3306'. It will be deleted and created again with a new password.
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: DBCLUSTERNODEIP3:3306 is being cloned from DBCLUSTERNODEIP1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: DBCLUSTERNODEIP3:3306 is shutting down...
* Waiting for server restart... ready
* DBCLUSTERNODEIP3:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 3.55 TB transferred in 5 hours 50 min 18 sec (168.95 MB/s)
Incremental state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: 'DBCLUSTERNODEIP3:3306' is being recovered from 'DBCLUSTERNODEIP1:3306'
* Distributed recovery has finished
Cluster.addInstance: DBCLUSTERNODEIP1:3306: MySQL server has gone away (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
Cluster.status: MySQL server has gone away (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > var cluster =dba.getCluster();
Dba.getCluster: MySQL server has gone away (RuntimeError)
The global session got disconnected..
Attempting to reconnect to 'mysql://adminlogin@DBCLUSTERNODEIP1:3306'..
The global session was successfully reconnected.
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
Cluster.status: MySQL server has gone away (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
Cluster.status: MySQL server has gone away (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > var cluster =dba.getCluster();
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
{
"clusterName": "prodclust",
"defaultReplicaSet": {
"name": "default",
"primary": "DBCLUSTERNODEIP1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"DBCLUSTERNODEIP1:3306": {
"address": "DBCLUSTERNODEIP1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "DBCLUSTERNODEIP1:3306"
}
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.rescan();
Rescanning the cluster...
Result of the rescanning operation for the 'prodclust' cluster:
{
"name": "prodclust",
"newTopologyMode": null,
"newlyDiscoveredInstances": [
{
"host": "DBCLUSTERNODEIP3:3306",
"member_id": "08538d8c-ed98-11ea-852d-005056844240",
"name": null,
"version": "8.0.20"
}
],
"unavailableInstances": []
}
A new instance 'DBCLUSTERNODEIP3:3306' was discovered in the cluster.
Would you like to add it to the cluster metadata? [Y/n]: y
Adding instance to the cluster metadata...
The instance 'DBCLUSTERNODEIP3:3306' was successfully added to the cluster metadata.
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
{
"clusterName": "prodclust",
"defaultReplicaSet": {
"name": "default",
"primary": "DBCLUSTERNODEIP1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"DBCLUSTERNODEIP1:3306": {
"address": "DBCLUSTERNODEIP1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"DBCLUSTERNODEIP3:3306": {
"address": "DBCLUSTERNODEIP3:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "DBCLUSTERNODEIP1:3306"
}
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.addInstance('adminlogin@DBCLUSTERNODEIP2:3306');
WARNING: A GTID set check of the MySQL instance at 'DBCLUSTERNODEIP2:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.
DBCLUSTERNODEIP2:3306 has the following errant GTIDs that do not exist in the cluster:
dfc8fe40-336a-11ed-a8ea-005056847352:49231601-49231871
WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of DBCLUSTERNODEIP2: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
NOTE: Group Replication will communicate with other members using 'DBCLUSTERNODEIP2:33061'. Use the localAddress option to override.
Validating instance configuration at DBCLUSTERNODEIP2:3306...
This instance reports its own address as DBCLUSTERNODEIP2: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...
ERROR: Unable to enable clone on the instance 'DBCLUSTERNODEIP3:3306': Recovery user 'mysql_innodb_cluster_11' not created by InnoDB Cluster
WARNING: User 'mysql_innodb_cluster_12'@'%' already existed at instance 'DBCLUSTERNODEIP1:3306'. It will be deleted and created again with a new password.
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: DBCLUSTERNODEIP2:3306 is being cloned from DBCLUSTERNODEIP3:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: DBCLUSTERNODEIP2:3306 is shutting down...
* Waiting for server restart... ready
* DBCLUSTERNODEIP2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 3.55 TB transferred in 5 hours 22 min 44 sec (183.33 MB/s)
Incremental state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: 'DBCLUSTERNODEIP2:3306' is being recovered from 'DBCLUSTERNODEIP1:3306'
* Distributed recovery has finished
Cluster.addInstance: DBCLUSTERNODEIP1:3306: MySQL server has gone away (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
Cluster.status: MySQL server has gone away (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > var cluster =dba.getCluster();
Dba.getCluster: MySQL server has gone away (RuntimeError)
The global session got disconnected..
Attempting to reconnect to 'mysql://adminlogin@DBCLUSTERNODEIP1:3306'..
The global session was successfully reconnected.
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
Cluster.status: MySQL server has gone away (RuntimeError)
MySQL DBCLUSTERNODEIP1:3306 ssl JS > var cluster =dba.getCluster();
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
{
"clusterName": "prodclust",
"defaultReplicaSet": {
"name": "default",
"primary": "DBCLUSTERNODEIP1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"DBCLUSTERNODEIP1:3306": {
"address": "DBCLUSTERNODEIP1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"DBCLUSTERNODEIP3:3306": {
"address": "DBCLUSTERNODEIP3:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "DBCLUSTERNODEIP1:3306"
}
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.rescan();
Rescanning the cluster...
Result of the rescanning operation for the 'prodclust' cluster:
{
"name": "prodclust",
"newTopologyMode": null,
"newlyDiscoveredInstances": [
{
"host": "DBCLUSTERNODEIP2:3306",
"member_id": "329fcb1a-c0dd-11eb-82b1-005056847352",
"name": null,
"version": "8.0.20"
}
],
"unavailableInstances": []
}
A new instance 'DBCLUSTERNODEIP2:3306' was discovered in the cluster.
Would you like to add it to the cluster metadata? [Y/n]: y
Adding instance to the cluster metadata...
The instance 'DBCLUSTERNODEIP2:3306' was successfully added to the cluster metadata.
MySQL DBCLUSTERNODEIP1:3306 ssl JS > cluster.status();
{
"clusterName": "prodclust",
"defaultReplicaSet": {
"name": "default",
"primary": "DBCLUSTERNODEIP1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"DBCLUSTERNODEIP1:3306": {
"address": "DBCLUSTERNODEIP1:3306",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"DBCLUSTERNODEIP2:3306": {
"address": "DBCLUSTERNODEIP2:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
},
"DBCLUSTERNODEIP3:3306": {
"address": "DBCLUSTERNODEIP3:3306",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.20"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "DBCLUSTERNODEIP1:3306"
}
MySQL DBCLUSTERNODEIP1:3306 ssl JS >
Note; Application Servers mysql router not able to connect means, Bootstrap of MYSQL Routers are required to connect back as like earlier.
No comments:
Post a Comment