Backing Up and Restoring VMware Tanzu SQL [MySQL for VMs]

Note: In v2.9 and later, MySQL for VMware Tanzu is named VMware Tanzu SQL [MySQL for VMs].

Page last updated:

This topic describes how to back up and restore a VMware Tanzu SQL [MySQL for VMs] service instance. Both operators and developers can use the procedures in this topic.

Operators can configure backups in the VMware Tanzu SQL tile by following the procedures in Configuring Backups.

Additionally, developers can create logical backups using mysqldump. For information, see Backing Up and Restoring with mysqldump.

Overview

You can back up and restore a VMware Tanzu SQL 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 backup or restore a service instance in the following use cases:

  • Disaster recovery
  • Troubleshooting
  • Testing

Prerequisite: adbr Plugin

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

To install the plugin, run:

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

Manually Back Up a Service Instance

To manually trigger a backup outside of the configured schedule:

  1. Back up your service instance by running:

    cf adbr backup SOURCE-INSTANCE
    

    Where SOURCE-INSTANCE is the service instance you are backing up.

  2. 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...
      [Mon May 18 18:08:25 UTC 2020] Status: Backup was successful. Uploaded 3.2M

Restore a Service Instance

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…
Leader-Follower Single node Leader-Follower
HA cluster Multi‑Site Replication HA cluster

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 VMware Tanzu SQL v2.8 or earlier Restoring VMware Tanzu SQL.
from a deleted service instance Manually Restoring from Backup.
to a different foundation Manually Restoring from Backup.

To restore a service instance:

  1. 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.

  2. View the available backup artifacts for your service instance by running:

    cf adbr list-backups SOURCE-INSTANCE
    

    Where SOURCE-INSTANCE is the name of the service instance.

    For example:

    $ 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

    Record the Backup ID from the above output.

  3. Restore the service instance by running:

    cf adbr restore DESTINATION-INSTANCE BACKUP-ID
    

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

    For example:

    $ cf adbr restore myTargetDB a41bf723-2538-4020-9d16-50cccb7b7c8d_1589825284
  4. 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...
      [Fri Jun 5 22:29:24 UTC 2020] Status: Restore was successful 
  5. If you are restoring a leader-follower or HA cluster service instance, update the plan by running:

    cf update-service DESTINATION-INSTANCE -p PLAN
    

    Where PLAN is the plan for the topology that you want to restore.

  6. 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
    

  7. 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 VMware Tanzu SQL 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.

  8. Update your app to bind to the new service instance by running:

    cf bind-service APP-NAME DESTINATION-INSTANCE
    
  9. Restage your app by running:

    cf restage APP-NAME
    

Monitor Automated Backups

It is 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:

Use the adbr Plugin

The 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.

  1. List the backups for an instance using the adbr plugin by running:

    cf adbr list-backups SOURCE-INSTANCE
    

    Where SOURCE-INSTANCE is the name of the service instance.

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

Use Healthwatch

For each backup of every service instance, VMware Tanzu SQL emits a metric called last_successful_backup.

  1. 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 Tanzu SQL [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.

    A plot of the last successful backup metric. When the metric increases linearly until the backup is made. The metric falls back to zero and then increases again.