Backing Up and Restoring VMware Tanzu SQL with MySQL for VMs
Note: In v2.9 and later, MySQL for VMware Tanzu is named VMware Tanzu SQL with MySQL for VMs.
Page last updated:
Note: The backup and restore capability described in this topic restores a running service instance’s backup to a new instance. It is not intended to list or restore backups created by a deleted service instance. For more information about restoring a backup from a deleted service instance, see Manually Restoring From Backup.
Note: In Tanzu SQL for VMs v2.9.0 to v2.9.2, the Application Developer Backup and
Restore (ADBR) feature stored backup artifacts in the root directory in external storage.
As of v2.9.3, backup artifacts are organized under subfolders in external storage. They are now stored under
p.mysql > service-instance_GUID > yyyy > mm > dd.
Old backup artifacts stored in the root directory can still be accessed through the cf CLI using the
This topic describes how to back up and restore a physical backup to a VMware Tanzu SQL with MySQL for VMs service instance. Both operators and developers can use the procedures in this topic.
Operators can configure automated backups in the Tanzu SQL for VMs tile by following the procedures in Configuring Automated Backups.
Developers can manually take physical backups by following the procedure in this topic.
Developers can also create logical backups using
For more information about logical backups, see Backing Up and Restoring with mysqldump.
You can back up and restore a Tanzu SQL for VMs service instance using the Cloud Foundry Command Line Interface (cf CLI). The backup artifacts you create in this topic are physical backups. The following procedures do not create logical backup artifacts. Restoring a physical backup is faster than restoring a logical backup and has less downtime.
You might want to back up or restore a service instance in the following use cases:
- Disaster recovery
Automated backups do the following:
- Periodically create and upload backups for restoring all of the databases used by a service instance.
- Operate without locking apps out of the database. There is no downtime.
- Include a metadata file that contains the critical details for the backup, including the calendar time of the backup.
- Encrypt backups within the Tanzu SQL for VMs VM. Unencrypted data is never transferred outside the Tanzu SQL for VMs deployment.
- Run at scheduled time. Operators configure the default schedule in Ops Manager. Developers can change the backup schedule for an individual service instance using an arbitrary parameter. For more information, see Backup Schedule.
Automated backups fail if the service broker is unavailable, such as during an upgrade.
Note: You must configure backups in the Tanzu SQL for VMs tile. You cannot be disable this feature.
Note: As of v2.9, only the leader node is backed up for a leader-follower deployment.
Warning: If Tanzu SQL for VMs fails to upload a backup, the backup artifact remains on the persistent disk. This can cause the persistent disk to fill up faster. If the persistent disk is full, apps become inoperable. For instructions on how to troubleshoot persistent disk errors, see Persistent Disk is Full.
When Tanzu SQL for VMs runs a backup, it uploads two files with Unix epoch-timestamped filenames:
- The encrypted data backup file,
- A metadata file,
The metadata file contains information about the backup that looks similar to the following:
ibbackup_version = 2.4.5 end_time = 2017-04-24 21:00:03 lock_time = 0 binlog_pos = incremental = N encrypted = N server_version = 5.7.16-10-log start_time = 2017-04-24 21:00:00 tool_command = --user=admin --password=... --stream=tar tmp/ innodb_from_lsn = 0 innodb_to_lsn = 2491844 format = tar compact = N name = tool_name = innobackupex partial = N compressed = N uuid = fd13cf26-2930-11e7-871e-42010a000807 tool_version = 2.4.5
The most important entries in the metadata file are the following:
start_time: Use this to determine which transactions are captured in the backup.
server_version: Use this to determine potential incompatibilities when restoring an instance with the backup artifact.
The backup process does not interrupt the MySQL service.
However backups only reflect transactions that are completed before the
The metadata file sets
indicates that the backup is not compressed or encrypted.
However, the backup uploaded by Tanzu SQL for VMs is both compressed and encrypted.
This is a known issue.
Before you can manually back up or restore a service instance, you must have installed the Application Data Backup and Restore (ADBR) plugin for the Cloud Foundry Command Line Interface (cf CLI) tool.
To install the plugin, run:
cf install-plugin -r CF-Community "ApplicationDataBackupRestore"
To manually trigger a backup outside of the configured schedule:
Back up your service instance by running:
cf adbr backup SOURCE-INSTANCE
SOURCE-INSTANCEis the service instance you are backing up.
View the status of the backup process by running:
cf adbr get-status SOURCE-INSTANCE
$ cf adbr get-status myDB Getting status of service instance myDB in org my-org / space my-space as user... [Mon May 18 18:08:25 UTC 2020] Status: Backup was successful. Uploaded 3.2M
When restoring a service instance, you create a new service instance and then restore the backup to it. If you are restoring leader-follower or HA cluster service instance, you update the plan after restoring the data. Finally, you rebind and restage apps that use the service instance.
Before you begin the restore procedure below, review the following important concepts:
- The Topology of the Instance You Restore To
- When to Use a Different Restore Method
- The New Database Must Be Empty
You can only restore to a single node or a Multi‑Site Replication service instance. If you want a different topology, update the instance after restoring the data:
|To restore to…||First, restore to…||Then, update to…|
|HA cluster||Multi‑Site Replication||HA cluster|
|Multi‑Site Replication||Multi‑Site Replication||n/a|
|Single node||Single node||n/a|
Operators must ensure that the persistent disk in the single node plan is at least as large as the persistent disk of the largest leader-follower plan. Similarly, they must ensure that the persistent disk in the Multi‑Site Replication plan is at least as large as the persistent disk of the largest HA cluster plan. For information about persistent disk sizing recommendations, see Persistent Disk Usage.
There are some cases where you cannot use the procedure below:
|Do not use the procedure in this section to restore a backup…||Instead, see…|
|created with Tanzu SQL for VMs v2.8 or earlier||Restoring Tanzu SQL for VMs.|
|from a deleted service instance||Manually Restoring from Backup.|
|to a different foundation||Manually Restoring from Backup.|
Do not write anything to the database in the new service instance until after you have restored the backup to it. For example, the following actions write to the database:
- You create service keys.
- You configure the service instance for Multi‑Site Replication.
- Tanzu SQL for VMs takes an automatic backup.
If the database is not empty, then the adbr plugin fails to restore the data and you need to delete the service instance and create another.
To restore a service instance:
Create a new MySQL service instance to restore to by running:
cf create-service p.mysql NEW-PLAN DESTINATION-INSTANCE
NEW-PLANis the name of the service plan for your new service instance. The plan you choose depends on the service instance topology that you are restoring. If the topology that you are restoring is:
- Single node or leader-follower: Select a single node plan.
- Multi‑Site Replication or HA cluster: Select a Multi‑Site Replication plan.
DESTINATION-INSTANCEis a name that you choose to identify the service instance.
You restore the backup artifact to this service instance. For more information, see Create a Service Instance.
View the available backup artifacts for your service instance by running:
cf adbr list-backups SOURCE-INSTANCE
SOURCE-INSTANCEis the name of the service instance.
$ cf adbr list-backups myDB Getting backups of service instance myDB in org my-org / space my-space as user... Backup ID Time of Backup a41bf723-2538-4020-9d16-50cccb7b7c8d_1589825284 Mon May 18 18:08:04 UTC 2020
Backup IDfrom the above output.
Restore the service instance by running:
cf adbr restore DESTINATION-INSTANCE BACKUP-ID
BACKUP-IDis the ID you recorded in the above step.
$ cf adbr restore myTargetDB a41bf723-2538-4020-9d16-50cccb7b7c8d_1589825284
View the status of the restore process by running:
cf adbr get-status DESTINATION-INSTANCE
$ cf adbr get-status myTargetDB Getting status of service instance myTargetDB in org my-org / space my-space as user... [Fri Jun 5 22:29:24 UTC 2020] Status: Restore was successful
If the status is
Restore failed, see The New Database Must Be Empty above.
If you are restoring a leader-follower or HA cluster service instance, update the plan by running:
cf update-service DESTINATION-INSTANCE -p PLAN
PLANis the plan for the topology that you want to restore.
Determine if your app is bound to a service instance by running:
$ cf services Getting services in org my-org / space my-space as user... OK name service plan bound apps last operation myDB p.mysql db-small my-app create succeeded myTargetDB p.mysql db-small create succeeded
If an app is currently bound to a service instance, unbind it by running:
cf unbind-service APP-NAME SOURCE-INSTANCE
Warning: If a developer rebinds an app to the Tanzu SQL for VMs service after unbinding, they must also rebind any existing custom schemas to the app. When you rebind an app, stored code, programs, and triggers break. For more information about binding custom schemas, see Use Custom Schemas.
Update your app to bind to the new service instance by running:
cf bind-service APP-NAME DESTINATION-INSTANCE
Restage your app by running:
cf restage APP-NAME
If you get HTTP error codes when working with the adbr plugin, see Failed Backup or Restore with adbr Plugin in Troubleshooting On-Demand Instances.
It is particularly important to verify that automated backups are being taken according to schedule. A common cause of failure for automated backups is the persistent disk filling up.
Two ways to check that backups are being made:
adbr plugin is a quick way to list backups for a service instance.
The plugin lists the last five backups for a service instance.
The list includes both automated and manual backups.
List the backups for an instance using the
adbrplugin by running:
cf adbr list-backups SOURCE-INSTANCE
SOURCE-INSTANCEis the name of the service instance.
$ cf adbr list-backups myDB Getting backups of service instance myDB in org my-org / space my-space as admin... Backup ID Time of Backup f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596675600 Thu Aug 6 01:00:00 UTC 2020 f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596675420 Thu Aug 6 00:57:00 UTC 2020 f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596675240 Thu Aug 6 00:54:00 UTC 2020 f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596675000 Thu Aug 6 00:50:00 UTC 2020 f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596674700 Thu Aug 6 00:45:00 UTC 2020
You can use Healthwatch to confirm that automated backups are being taken on the schedule configured by the operator. For information about configuring the schedule for automated backups, see Configuring Automated Backups.
For each backup of every service instance,
Tanzu SQL for VMs emits a metric
last_successful_backupmetric through Healthwatch.
For details about the metric, see Hours Since Last Successful Backup in Monitoring and KPIs for VMware Tanzu SQL with MySQL for VMs.
A healthy backup metric shows a saw-tooth plot. The blue stepped line is the time elapsed since the last backup. The dotted red line represents the scheduled time when an automated backup is expected to occur. When a backup is taken, the time elapsed resets to zero.