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 Tanzu SQL for VMs 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 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 mysqldump. 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
- Troubleshooting
- Testing
About backups
Tanzu SQL for VMs 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 Tanzu SQL for VMs deployment.
- Run at a scheduled time. Operators configure the default schedule in Tanzu Operations 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 Tanzu SQL for VMs tile. You cannot deactivate this feature.
Backups fail if the service broker is unavailable (for example, during an upgrade).
Important If Tanzu SQL for VMs 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 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.
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 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-INSTANCEWhere
SOURCE-INSTANCEis the service instance you are backing up. -
View the status of the backup process by running:
cf adbr get-status SOURCE-INSTANCEFor 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 you restore 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
- When to use a different restore method
- The new database must be empty
The topology of the instance you restore to
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 |
| Multi‑Site Replication | Multi‑Site Replication | n/a |
| Single node | Single node | n/a |
You 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.
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… |
|---|---|
| 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. |
The new database must be empty
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 plug-in fails to restore the data and you need to delete the service instance and create another.
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-INSTANCEWhere:
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 --limit NUMBERWhere:
SOURCE-INSTANCEis the name of the service instance.--limit NUMBERis 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 IDfrom the output. -
Restore the service instance by running:
cf adbr restore DESTINATION-INSTANCE BACKUP-IDWhere is
BACKUP-IDis the ID you recorded in the revious 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-INSTANCEFor 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 The new database must be empty. -
If you are restoring a leader-follower or HA cluster service instance, update the plan by running:
cf update-service DESTINATION-INSTANCE -p PLANWhere
PLANis the plan for the topology that you want to restore. -
Determine if your app is bound to a service instance by running:
cf servicesFor 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 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
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
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
adbrplug-in by running:cf adbr list-backups SOURCE-INSTANCE --limit NUMBERWhere:
SOURCE-INSTANCEis the name of the service instance.--limit NUMBERis 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, Tanzu SQL for VMs emits a metric called last_successful_backup.
-
Monitor the
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.
