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:

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 take manually take physical backups by following the procedure in this topic. Developers can also create logical backups using mysqldump. For information logical backups, see Backing Up and Restoring with mysqldump.

Overview

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
  • Troubleshooting
  • Testing

About Backups

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.

About Backup Files and Metadata

When Tanzu SQL for VMs runs a backup, it uploads two files with Unix epoch-timestamped filenames:

  • The encrypted data backup file, mysql-backup-TIMESTAMP.tar.gpg
  • A metadata file, mysql-backup-TIMESTAMP.txt

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

Note: The metadata file sets compressed encrypted as N, which 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.

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

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

  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
    

Troubleshooting the adbr Plugin

If you get HTTP error codes when working with the adbr plugin, see Failed Backups with adbr Plugin 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.

Two ways to check that backups are being made:

Use the adbr Plugin to List Backups

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 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, Tanzu SQL for VMs 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 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.

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