Backing up and restoring VMware SQL with MySQL for Tanzu Application Service

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.

In VMware SQL with MySQL for TAS v2.9.0 to v2.9.2, the ApplicationDeveloperBackupRestore (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 adbr plug-in.

You can back up and restore a physical backup to a VMware SQL with MySQL for Tanzu Application Service service instance. Both operators and developers can use the procedures in this topic.

Operators can configure automated backups in the VMware SQL with MySQL for TAS 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 mysqldump. For more information about logical backups, see Backing up and restoring with mysqldump.

You can back up and restore a VMware SQL with MySQL for TAS 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
Troubleshooting
Testing

About backups

VMware SQL with MySQL for TAS includes an automated backup capability, which:

Creates and uploads physical backups for restoring all of the databases used by a service instance.
Includes a metadata file that contains the critical details for the backup, including the calendar time of the backup.
Encrypts the backup artifact on the service instance VM before uploading to offsite storage configured by the operator. Unencrypted data is never transferred outside the VMware SQL with MySQL for TAS deployment.
Run at a scheduled time. Operators configure the default schedule in Ops Manager. Developers can use an arbitrary parameter to override the default schedule on individual service instances. For more information, see Backup Schedule.

Developers can also manually trigger a backup of an individual service instance. For information about manually backing up a service instance, see Manually back up a service instance below.

Operators must configure storage and default schedules for automated backups in the VMware SQL with MySQL for TAS tile. You cannot deactivate this feature.

Backups fail if the service broker is unavailable (for example, during an upgrade).

Important If VMware SQL with MySQL for TAS fails to upload the backup for a service instance, the backup artifact remains on the persistent disk of that service instance. 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.

Backups use the Percona Xtrabackup tool, which does not lock schemas that use the default InnoDB transactional storage engine. Backups do briefly lock non-transactional operations, specifically DDL and MyISAM (non-InnoDB) storage engine operations. Developers should be aware of these backup DDL and MyISAM locks, and consider any impact on their running applications. For more information about backup locking behavior, see the Percona documentation.

About backup files

When VMware SQL with MySQL for TAS runs a backup, it uploads one file:

The encrypted data backup file, artifact

Prerequisite: adbr plug-in

Before you can manually back up or restore a service instance, you must have installed the ApplicationDataBackupRestore (adbr) plug-in for the Cloud Foundry Command Line Interface (cf CLI) tool.

To install the plug-in, run:

cf install-plugin -r CF-Community "ApplicationDataBackupRestore"

The procedures on this page assume that you are using the adbr plug-in v0.3.0 or later.

Manually back up a service instance

To manually trigger a backup outside of the configured schedule:

Back up your service instance by running:
```
cf adbr backup SOURCE-INSTANCE
```
Where SOURCE-INSTANCE is the service instance you are backing up.

View the status of the backup process by running:

cf adbr get-status SOURCE-INSTANCE

For example:

$ cf adbr get-status myDB
  Getting status of service instance myDB in org my-org / space my-space as user...
  [Fri May 28 18:08:25 UTC 2021] Status: Backup was successful. Uploaded 3.2M

Restore a service instance

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.

About restoring a service instance

Before you begin the restore procedure review the following important concepts:

The topology of the instance you restore to
Restoring to a non-empty database
When to use a different restore method

The topology of the instance you restore to

You can restore to service instances of any topology type. Whatever topology you choose, be sure the destination service instance has sufficient persistent disk to accommodate the restore’s data volume. For information about persistent disk sizing recommendations, see Persistent disk usage.

Restoring to a non-empty database

You can restore to either a newly-created empty database service instance, or to a running non-empty database service instance that is actively hosting data.

Restoring to a non-empty database is destructive; you lose any data in that database. This includes not just application data but ancillary configuration data such as:

Application bindings and service keys you have created.
Automatic backup configuration.
Any replication configuration if restoring to a Multi‑Site Replication service instance.

Before restoring, first confirm it is safe to overwrite the database. During the restore, the cf adbr cli plug-in asks you to provide manual confirmation or provide the --force or -f flag.

Provide manual confirmation:

cf adbr restore DESTINATION-INSTANCE BACKUP-ID
Restoring service instance DESTINATION-INSTANCE in org $ORG / space $SPACE as $USER...
This action will overwrite all data in this service instance.
Really restore the service instance DESTINATION-INSTANCE? [yN]: y
OK

Provide the --force or -f flag:

cf adbr restore DESTINATION-INSTANCE BACKUP-ID --force
Restoring service instance DESTINATION-INSTANCE in org $ORG / space $SPACE as $USER...
This action will overwrite all data in this service instance.
OK

When to use a different restore method

There are some cases where you cannot use the following procedure:

Do not use the procedure in this section to restore a backup…	Instead, see…
from a deleted service instance	Manually Restoring from Backup.
to a different foundation	Manually Restoring from Backup.

Restore a service instance

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
```
Where:
- NEW-PLAN is 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-INSTANCE is 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 --limit NUMBER

Where:

SOURCE-INSTANCE is the name of the service instance.
--limit NUMBER is an optional flag to specify how many backups to list. Without the flag, the command lists five backups.

For example:

$ cf adbr list-backups myDB --limit 1
  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   Fri May 28 18:08:04 UTC 2021

Record the Backup ID from the output.

Restore the service instance by running:

cf adbr restore DESTINATION-INSTANCE BACKUP-ID
Restoring service instance DESTINATION-INSTANCE in org $ORG / space $SPACE as $USER...
This action will overwrite all data in this service instance.
Really restore the service instance DESTINATION-INSTANCE? [yN]: y
OK

Where BACKUP-ID is the ID you recorded in the previous step.

For example:

$ 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

For example:

$ cf adbr get-status myTargetDB
  Getting status of service instance myTargetDB in org my-org / space my-space as user...
  [Mon May 31 22:29:24 UTC 2021] Status: Restore was successful

If the status is Restore failed, see Troubleshooting the adbr plug-in.

Determine if your app is bound to a service instance by running:

cf services

For example:

$ 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
```

Important If a developer rebinds an app to the VMware SQL with MySQL for TAS 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
```

Troubleshooting the adbr plug-in

If you get HTTP error codes when working with the adbr plug-in, see Failed backup or restore with adbr plug-in in Troubleshooting on-demand instances.

Monitor backups

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.

There are two ways to check that backups are being made:

Use the adbr plug-in to list backups
Use Healthwatch to confirm automated backups

Use the adbr plug-in to list backups

The adbr plug-in is a quick way to list backups for a service instance. The plug-in 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 adbr plug-in by running:

cf adbr list-backups SOURCE-INSTANCE --limit NUMBER

Where:

SOURCE-INSTANCE is the name of the service instance.
--limit NUMBER is an optional flag to specify how many backups to list. Without the flag, the command lists five backups.

For example:

$ 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   Wed Jun  2 01:00:00 UTC 2021
f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596675420   Wed Jun  2 00:57:00 UTC 2021
f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596675240   Wed Jun  2 00:54:00 UTC 2021
f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596675000   Wed Jun  2 00:50:00 UTC 2021
f4df63d3-ece1-4b53-99a4-4b55ad10af2f_1596674700   Wed Jun  2 00:45:00 UTC 2021

Use Healthwatch to confirm automated backups

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, VMware SQL with MySQL for TAS emits a metric called last_successful_backup.

Monitor the last_successful_backup metric through Healthwatch.

For details about the metric, see Hours since last successful backup in Monitoring and KPIs for VMware SQL with MySQL for Tanzu Application Service.

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.