If you are restoring a service instance to the same foundation and you still have the original service instance, then follow the instructions in Backing up and restoring VMware Tanzu SQL with MySQL for VMs.

This topic tells you how to manually restore a VMware Tanzu SQL with MySQL for VMs service instance from a backup in the following cases:

  • You have lost or deleted the service instance that backup came from.
  • You are restoring to a different foundation.

VMware recommends that you always configure a single instance plan to streamline the restore process for leader-follower and HA cluster plans.

Restoring Tanzu SQL for VMs from backup is a manual process primarily intended for disaster recovery or migrating data to a different foundation. Restoring a Tanzu SQL for VMs service instance replaces all of its data and running state.

To restore a Tanzu SQL for VMs instance from a backup:

  1. Identify and download the backup artifact
  2. Retrieve backup encryption key
  3. Create and prepare a new service instance for restore
  4. Restore the service instance
  5. Restage the service instance

Identify and download the backup artifact

The procedure you need to find and download the backup artifact depends on whether or not the service instance was deleted.

Follow the procedure for your situation:

If restoring a backup from a deleted service instance

If you are restoring a backup from a lost or deleted instance, you cannot follow the instructions in Backing up and restoring VMware Tanzu SQL with MySQL for VMs because you do not have the GUID for the service instance nor the timestamp for the backup.

The instructions on this section decribe how to find these things and then download the backup artifact.

  1. Find the GUID of the service instance by searching the broker logs. Try searching the broker log for entries about deleted deployments, such as:

    [on-demand-service-broker] 2020/08/21 23:48:04.821405 Request DELETE /v2/service_instances/6c1db434-29ef-47c4-9f22-59fe53676b07 Completed 202 in 545.989368ms | Start Time: 2020/08/21 23:48:04.275382
    or 
    [on-demand-service-broker] [8db9f496-e83f-4aa6-82f0-528ddbff4c0a] 2020/08/21 23:53:09.978376 BOSH task ID 348 status: done delete deployment for instance 6c1db434-29ef-47c4-9f22-59fe53676b07: Description: delete deployment service-instance_6c1db434-29ef-47c4-9f22-59fe53676b07 Result: /deployments/service-instance_6c1db434-29ef-47c4-9f22-59fe53676b07

    In the log entries example, the GUID of the service instance begins with 6c1db.

    For information about broker logs, see Access broker and instance logs and VMs.

  2. Log in to your backup storage system. Your backup storage system is whatever you configured in Configuring automated backups. For example, it might be an S3 bucket or a file system on a backup host (SCP).

  3. Identify and download the backup artifact, the tarfile. The name of the tarfile contains the GUID and an epoch timestamp. For example, 6c1db434-29ef-47c4-9f22-59fe53676b07_1598049440.tar.

  4. Record the backup ID, which is the name of the TAR file without the .tar extension. In the example, the backup ID is 6c1db434-29ef-47c4-9f22-59fe53676b07_1598049440.

  5. Untar the backup artifact. Keep the backup file with the tar.gpg extension.

    The file with the .txt extension contains metadata to help identify the backup, but this text file is not needed for the restore procedure.

If Restoring to a different foundation

If you have a backup of a service instance taken from one foundation, and you want to restore the backup to a different foundation, you cannot follow the instructions in Backing up and restoring VMware Tanzu SQL with MySQL for VMs.

Instead, follow these instructions to identify and download the backup artifact. These instructions are more straightforward than If Restoring a Backup from a Deleted Service Instance because you have access to the backed up service instance and therefore its GUID and a timestamp for the backup.

  1. Find the GUID of the service instance by running:

    cf service SERVICE-INSTANCE-NAME --guid

    Where SERVICE-INSTANCE-NAME is the service instance from which the back up was taken.

    For example:

    $ cf service my-instance --guid
6c1db434-29ef-47c4-9f22-59fe53676b07

  2. List the backups available and determine the one you want to download:

    cf adbr list-backups SERVICE-INSTANCE-NAME

    Where SERVICE-INSTANCE-NAME is the service instance from which the back up was taken.

    For example:

    $ cf adbr list-backups my-instance
Getting backups of service instance my-instance in org…
Backup ID                                         Time of Backup
6c1db434-29ef-47c4-9f22-59fe53676b07_1598049440   Fri Aug 21 22:37:20 UTC 2020

  3. If the service instance is running, download the backup tarfile. Your backup storage system is whatever you configured in Configuring automated backups. For example, it might be an S3 bucket or a file system on a backup host (SCP).

  4. Untar the backup artifact. Keep the backup file with the tar.gpg extension.

    The file with the .txt extension contains metadata to help identify the backup, but this text file is not needed for the restore procedure.

Retrieve backup encryption key

Each backup artifact has its own encryption key, stored in runtime CredHub.

To retrieve the backup encryption key:

  1. To find the GUID for the MySQL service broker VM, run:

    bosh deployments

  2. To SSH onto the broker VM, run:

    bosh -d pivotal-mysql-GUID ssh

    Where is GUID is the GUID you recorded in step 1.

    For example:

    $ bosh -d pivotal-mysql-f7f3ce3767943537c36a ssh

  3. Fetch the credentials to authenticate with the TAS for VMs CredHub:

    1. Go to the Tanzu Operations Manager Installation Dashboard.

    2. In the TAS for VMs tile, click the Credentials tab.

    3. Record the credentials for Credhub Admin Client Client Credentials.

  4. Set the path for the CredHub CLI and authenticate with runtime CredHub:

    export PATH=/var/vcap/packages/credhub-cli/bin:$PATH
credhub api https://credhub.service.cf.internal:8844 --ca-cert /var/vcap/jobs/adbr-api/config/credhub_ca.pem
credhub login --client-name CREDHUB-CLIENT --client-secret CREDHUB-CLIENT-SECRET

    Where:

    • CREDHUB-CLIENT is the identity of the credential
    • CREDHUB-CLIENT-SECRET is the password of the credential

    For example:

    $ export PATH=/var/vcap/packages/credhub-cli/bin:$PATH
$ credhub api https://credhub.service.cf.internal:8844 --ca-cert /var/vcap/jobs/adbr-api/config/credhub_ca.pem
$ credhub login --client-name credhub_admin_client --client-secret o2i30fj2fjvjoi3j

  5. To obtain the encryption key for the backup, run:

    credhub get -n /tanzu-mysql/backups/BACKUP-ID

    Where is BACKUP-ID is the name of the backup artifact you identified.

    For example:

    $ credhub get -n /tanzu-mysql/backups/6c1db434-29ef-47c4-9f22-59fe53676b07_1598049440
id: b918cda9-0c8b-4011-bbba-f78bdb5ceea4
name: /tanzu-mysql/backups/6c1db434-29ef-47c4-9f22-59fe53676b07_1598049440
type: password
value: NWjbvbB3pjOC
version_created_at: "2020-08-21T22:37:20Z"

  6. Record the value of the password. This is the backup encryption key that need when you restore the backup.

    In this example, it is NWjbvbB3pjOC. Use this key when you restore the backup.

Create and prepare a new service instance for restore

You can only restore single node and leader-follower backup artifacts to a single node service instance. Ensure that the persistent disk in the single node plan is at least as large as the persistent disk of your largest leader-follower.

For information about persistent disk sizing recommendations, see Persistent disk usage.

To prepare a new service instance for restore:

  1. Create a new MySQL service instance by running:

    cf create-service p.mysql NEW-PLAN NEW-INSTANCE-NAME

    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.
    • NEW-INSTANCE-NAME is the name of the new service instance.

    For more information, see Create a service instance.

  2. Monitor the status of the service instance creation by running:

    watch cf service NEW-INSTANCE-NAME

    Where NEW-INSTANCE-NAME is the name of the new service instance.

  3. Locate and record the GUID associated with your new service instance by running:

    cf service NEW-INSTANCE-NAME --guid

  4. From the Tanzu Operations Manager VM, find and record the BOSH instance GUID for your service instance by running:

    bosh -e BOSH-ENVIRONMENT -d service-instance_GUID instances

    Where GUID is the service instance GUID you previously recorded.

    For example:

    $ bosh -e my-env -d service-instance_12345678-90ab-cdef-1234-567890abcdef instances
 Deployment 'service-instance_12345678-90ab-cdef-1234-567890abcdef' 
    
 Instance                                    Process State  AZ             IPs
 mysql/d7ff8d46-c3e8-449f-aea7-5a05b0a1929c  running        us-central1-a  10.0.8.10
 1 instances

    The BOSH instance GUID is the value after mysql/.

  5. Copy the downloaded backup to the new service instance by running:

    bosh -e BOSH-ENVIRONMENT  -d service-instance_GUID \
  scp mysql-backup-STRING.tar.gpg \
  mysql/BOSH-INSTANCE-GUID:DESTINATION-PATH

    Where:

    • GUID is the service instance GUID.
    • mysql-backup-STRING.tar.gpg is the backup file you downloaded in Identify and download the backup artifact.
    • BOSH-INSTANCE-GUID is the BOSH instance GUID you recorded in the previous step.
    • DESTINATION-PATH is where the backup file saves on the BOSH VM.

    For example:

    $ bosh -e my-env -d service-instance_12345678-90ab-cdef-1234-567890abcdef \
    scp mysql-backup-1595910545-b42f275a-c355-49c3-9e33-bda6fcf2ffd0.tar.gpg \
    mysql/d7ff8d46-c3e8-449f-aea7-5a05b0a1929c:/tmp/

Restore the service instance

Restoring a service instance is destructive. VMware recommends that you only restore to a new and unused service instance.

You can restore a single node, leader-follower, HA cluster service, or Multi‑Site Replication instance using the restore utility. The restore utility is packaged with the VMware Tanzu SQL with MySQL for VMs tile.

The restore utility does the following:

  • Deletes any existing data.
  • Decrypts and unzips the backup artifact.
  • Restores the backup artifact into the MySQL data directory.

To restore a service instance:

  1. Use the BOSH CLI to SSH in to the newly created MySQL service instance by following the procedure in SSH into the BOSH Director VM.

  2. After securely logging in to MySQL, become root by running:

    sudo su

  3. Restore the backup artifact into the data directory by running:

    mysql-restore --encryption-key ENCRYPTION-KEY --restore-file RESTORE-FILE-PATH

    Where:

    • ENCRYPTION-KEY is the backup encryption key you recorded in Retrieve backup encryption key.
    • RESTORE-FILE-PATH is the full path on the BOSH VM where the backup artifact exists.

    For example:

    $ mysql-restore --encryption-key NWjbvbB3pjOC --restore-file /tmp/mysql-backup-1595910545-b42f275a-c355-49c3-9e33-bda6fcf2ffd0.tar.gpg

Restage the service instance

After you restore your single node, leader-follower, HA cluster service instance, or Multi‑Site Replication you must restage your new service instance. For Multi‑Site Replication plans, you must also re-establish replication between the leader and follower service instances.

To restage your service instance:

  1. If you are restoring a leader-follower service instance, update the plan by running:

    cf update-service NEW-INSTANCE-NAME -p LEADER-FOLLOWER-PLAN

  2. If you are restoring a HA cluster service instance, update the plan by running:

    cf update-service NEW-INSTANCE-NAME -p HA-CLUSTER-PLAN

  3. If you are restoring a Multi‑Site Replication service instance, you must re-establish replication:

    1. Create a follower Multi‑Site Replication service instance by following the procedure in Create Multi‑Site Replication Service Instances. Ensure that you only create a follower service instance in the secondary foundation.
    2. Configure replication between the leader service instance you restored and the follower instance by following the procedure in Configure Multi‑Site Replication.

  4. Determine if the app is currently bound to a MySQL service instance by running:

    cf services

  5. If the previous step shows that the app is currently bound to a MySQL instance, unbind it by running:

    cf unbind-service APP-NAME OLD-INSTANCE-NAME

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

    cf bind-service APP-NAME  NEW-INSTANCE-NAME

  7. Restage your app to make the changes take effect by running:

    cf restage APP-NAME

Your app must be running and able to access the restored data.

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.

check-circle-line exclamation-circle-line close-line
Scroll to top icon