MySQL InnoDB Cluster – Easy Recovering and provisioning
September 10, 2019
As the administrator of a cluster, among other tasks, you should be able to restore failed nodes and grow (or shrink) your cluster by adding (or removing) new nodes
Olivier DASINI
Up to MySQL 8.0.16, to perform these tasks you could:
- Use MySQL Enterprise Backup : see MySQL InnoDB Cluster – Recovering and provisioning with MySQL Enterprise Backup
- Use mysqldump : see MySQL InnoDB Cluster – Recovering and provisioning with mysqldump
Starting with MySQL 8.0.17, the easiest and more convenient method is to use the CLONE feature.
Note:
Regardless of the recovering and provisioning method, it is important to have a proven backup/restore procedure.I recommend reading the excellent blog post from my colleague Jesper – MySQL Backup Best Practices.
Context
Let’s make it as simple as possible 🙂
I’m using MySQL 8.0.17.
I running a MySQL InnoDB Cluster well setup.
So my main assumption is that you already know what is MySQL Group Replication & MySQL InnoDB Cluster.
Additionally you can read this tutorial and this article from my colleague lefred or this one on Windows Platform from my colleague Ivan.
Scenario 1 – Node Recovering
- A 3 nodes MySQL InnoDB Cluster – M1 / M2 / M3, in single primary mode
- MySQL Router is configured to enable R/W connections on 3306 and RO connections on 3307
- M1 is currently the primary (that is in Read/Write mode)
- M2 & M3 are currently the secondaries (that is Read Only mode)
- M1 crashed! :'(
- M2 & M3 are now the (new temporary) cluster
- Then…
The goal then is to rebuild M1 and put it back to the cluster.
So like I said before we have a 3 nodes MySQL 8.0.17 InnoDB Cluster up and running:
$ mysqlsh clusterAdmin@{mysqlRouterIP}:3306 --cluster
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M1:3306"
}Then node M1 crashed… (status is “MISSING“ & we have a relevant shellConnectError message) :
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M2:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003 (HY000): Can't connect to MySQL server on 'M1' (113)",
"status": "(MISSING)"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M2:3306"
}M1 was the primary.
The cluster initiated an automatic database failover to elect a new primary… blablabla
Anyway you already know the story ?
After a while M1 is finally fixed and ready to be part of the cluster again.
Node Recovery: Auto distributed recovery
Well I have a great news for you most of the time your only task will be to start the fixed MySQL instance.
Before starting the node (M1), status is Missing:
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M2:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M2:3306"
}Then MySQL InnoDB Cluster will automatically choose the relevant distributed recovery method.
During a certain period of time status is Recovering.
Details are available in recoveryStatusText field:
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M2:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/O",
"readReplicas": {},
"recovery": {
"state": "ON"
},
"recoveryStatusText": "Distributed recovery in progress",
"role": "HA",
"status": "RECOVERING",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M2:3306"
}The node eventually becomes online.
Status: Online.
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M2:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M2:3306"
}So you get it now, the node recovering process is handle by MySQL InnoDB Cluster using the automated distributed recovery.
That means, if your lucky, your only task (in addition to fixing the issue) is to (re)start the MySQL instance.
If you’re not lucky, you’ll probably need to remove and add the node again.
Node Recovery: Remove and add the node
The strategy here is to remove the node from the cluster metadata and then add it back – as a new node – to the cluster.
For example for node M3, remove the node with removeInstance():
cluster.removeInstance("clusterAdmin@M3:3306", {force:true}) force is a boolean. By default, set to false.
Indicating if the instance must be removed (even if only from metadata) in case it cannot be reached.
Useful if the instance is no longer reachable (RTFM).
Then add the node with addInstance():
cluster.addInstance("clusterAdmin@M3:3306")An alternative, that I prefer, is to use rescan() then addInstance().
Let’s see how to use it, assuming M3 crashed.
The current status is:
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M1:3306",
"ssl": "REQUIRED",
"status": "OK_PARTIAL",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M1:3306"
}Let’s (re)scan the cluster and remove the missing node (M3):
MySQL localhost JS> cluster.rescan()
Rescanning the cluster...
Result of the rescanning operation for the 'default' ReplicaSet:
{
"name": "default",
"newTopologyMode": null,
"newlyDiscoveredInstances": [],
"unavailableInstances": [
{
"host": "M3:3306",
"label": "M3:3306",
"member_id": "93303635-c8c0-11e9-9665-0242ac13000d"
}
]
}
The instance 'M3:3306' is no longer part of the ReplicaSet.
The instance is either offline or left the HA group. You can try to add it to the cluster again with the cluster.rejoinInstance('M3:3306') command or you can remove it from the cluster configuration.
Would you like to remove it from the cluster metadata? [Y/n]: Y
Removing instance from the cluster metadata...
The instance 'M3:3306' was successfully removed from the cluster metadata.The new cluster status is:
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M1:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M1:3306"
}Now it’s time to bring back M3, using the Clone distributed process:
MySQL localhost JS> cluster.addInstance("clusterAdmin@M3:3306")
WARNING: A GTID set check of the MySQL instance at 'M3:3306' determined
that it contains transactions that do not originate from the cluster, which
must be discarded before it can join the cluster.
M3:3306 has the following errant GTIDs that do not exist in the cluster:
93303635-c8c0-11e9-9665-0242ac13000d:1-2
WARNING: Discarding these extra GTID events can either be done manually or by completely
overwriting the state of M3: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
Validating instance at M3:3306...
This instance reports its own address as M3: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...
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: M3:3306 is being cloned from M1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY: |
NOTE: M3:3306 is shutting down...
* Waiting for server restart... ready
* M3:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 68.99 MB transferred in about 1 second (~inf TB/s)
Incremental distributed state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: 'M3:3306' is being recovered from 'M2:3306'
* Distributed recovery has finished
The instance 'M3:3306' was successfully added to the cluster.Here we go!
Our 3 nodes MySQL InnoDB Cluster can tolerate up to 1 failure again:
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M1:3306"
}Scenario 2 – Node Provisioning
- A 3 nodes MySQL InnoDB Cluster – M1 / M2 / M3 in single primary mode
- MySQL Router is configured to enable R/W connections on port 3306 and RO connections on port 3307
- M1 is currently the primary (that is Read/Write mode)
- M2 & M3 are currently the secondaries (that is Read Only mode)
The goal then is to add 2 new nodes: M4 & M5
So we have the 3 nodes MySQL 8.0.17 InnoDB Cluster that we used in the first part of this article. The cluster is up and running.
Actually adding new nodes is very close to what we have done previously.
The process is :
- Deploy the new MySQL instance preferably already configured for Group Replication
- Use the automatic distributed recovery process
Add node M4:
MySQL localhost JS> cluster.addInstance("clusterAdmin@M4:3306")
NOTE: A GTID set check of the MySQL instance at 'M4:3306' determined
that it is missing transactions that were purged from all cluster members.
Please select a recovery method [C]lone/[A]bort (default Abort): C
Validating instance at M4:3306...
This instance reports its own address as M4: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...
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: M4:3306 is being cloned from M3:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY: \
NOTE: M4:3306 is shutting down...
* Waiting for server restart... ready
* M4:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 116.24 MB transferred in 3 sec (38.75 MB/s)
Incremental distributed state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: 'M4:3306' is being recovered from 'M3:3306'
* Distributed recovery has finished
The instance 'M4:3306' was successfully added to the cluster.As you can see we use the Clone process.
The new cluster status is:
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M4:3306": {
"address": "M4:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M1:3306"
}Same process for node M5:
MySQL localhost JS> cluster.addInstance("clusterAdmin@M5:3306")
...Our final status is then:
MySQL localhost JS> cluster.status()
{
"clusterName": "pocCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "M1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to 2 failures.",
"topology": {
"M1:3306": {
"address": "M1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M2:3306": {
"address": "M2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M3:3306": {
"address": "M3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M4:3306": {
"address": "M4:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"M5:3306": {
"address": "M5:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "M1:3306"
}References
- MySQL InnoDB Cluster – Recovering and provisioning with MySQL Enterprise Backup
- MySQL InnoDB Cluster – Recovering and provisioning with mysqldump
- Tutoriel – Déployer MySQL 8.0 InnoDB Cluster (09-2019)
- MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster
- MySQL InnoDB Cluster – HowTo #2 – Validate an instance
Video:
Misc
Node 1 – Group Replication configuration variables
+-----------------------------------------------------+---------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------------+---------------------------------------------+
| group_replication_allow_local_lower_version_join | OFF |
| group_replication_auto_increment_increment | 7 |
| group_replication_autorejoin_tries | 0 |
| group_replication_bootstrap_group | OFF |
| group_replication_clone_threshold | 9223372036854775807 |
| group_replication_communication_debug_options | GCS_DEBUG_NONE |
| group_replication_communication_max_message_size | 10485760 |
| group_replication_components_stop_timeout | 31536000 |
| group_replication_compression_threshold | 1000000 |
| group_replication_consistency | EVENTUAL |
| group_replication_enforce_update_everywhere_checks | OFF |
| group_replication_exit_state_action | READ_ONLY |
| group_replication_flow_control_applier_threshold | 25000 |
| group_replication_flow_control_certifier_threshold | 25000 |
| group_replication_flow_control_hold_percent | 10 |
| group_replication_flow_control_max_quota | 0 |
| group_replication_flow_control_member_quota_percent | 0 |
| group_replication_flow_control_min_quota | 0 |
| group_replication_flow_control_min_recovery_quota | 0 |
| group_replication_flow_control_mode | QUOTA |
| group_replication_flow_control_period | 1 |
| group_replication_flow_control_release_percent | 50 |
| group_replication_force_members | |
| group_replication_group_name | bc39053e-c9e0-11e9-9797-0242ac13000b |
| group_replication_group_seeds | M2:33061,M3:33061 |
| group_replication_gtid_assignment_block_size | 1000000 |
| group_replication_ip_whitelist | AUTOMATIC |
| group_replication_local_address | M1:33061 |
| group_replication_member_expel_timeout | 0 |
| group_replication_member_weight | 50 |
| group_replication_message_cache_size | 1073741824 |
| group_replication_poll_spin_loops | 0 |
| group_replication_recovery_complete_at | TRANSACTIONS_APPLIED |
| group_replication_recovery_get_public_key | OFF |
| group_replication_recovery_public_key_path | |
| group_replication_recovery_reconnect_interval | 60 |
| group_replication_recovery_retry_count | 10 |
| group_replication_recovery_ssl_ca | |
| group_replication_recovery_ssl_capath | |
| group_replication_recovery_ssl_cert | |
| group_replication_recovery_ssl_cipher | |
| group_replication_recovery_ssl_crl | |
| group_replication_recovery_ssl_crlpath | |
| group_replication_recovery_ssl_key | |
| group_replication_recovery_ssl_verify_server_cert | OFF |
| group_replication_recovery_use_ssl | ON |
| group_replication_single_primary_mode | ON |
| group_replication_ssl_mode | REQUIRED |
| group_replication_start_on_boot | ON |
| group_replication_transaction_size_limit | 150000000 |
| group_replication_unreachable_majority_timeout | 0 |
+-----------------------------------------------------+---------------------------------------------+Some optional but useful parameters from my node1’s my.cnf:
[mysqld]
report_port = 3306
report_host = M1
plugin_load = group_replication.so
plugin-load-add = mysql_clone.soThanks for using MySQL!
Watch my videos on my YouTube channel and subscribe.
Thanks for using HeatWave & MySQL!
Cloud Solutions Architect at Oracle
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
[…] MySQL InnoDB Cluster – Easy Recovering and provisioning […]