Restoring MySQL for VMware Tanzu

Note: Pivotal Platform is now part of VMware Tanzu. In v2.8 and later, MySQL for Pivotal Platform is named MySQL for VMware Tanzu.

Page last updated:

This topic describes how to manually restore a MySQL for VMware Tanzu service instance from a backup.

You can create a backup by following the procedures in Backing up MySQL for VMware Tanzu

Overview

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

Restoring MySQL for VMware Tanzu from backup is a manual process primarily intended for disaster recovery. Restoring a MySQL for VMware Tanzu service instance replaces all of its data and running state.

To restore a MySQL for VMware Tanzu instance from an offsite backup:

1. Retrieve Backup Encryption Key
2. Download the Backup Artifact
3. Create and Prepare a New Service Instance for Restore
4. Restore the Service Instance
5. Restage the Service Instance

Retrieve Backup Encryption Key

To retrieve the backup encryption key:

1. Find and record your CredHub credentials by following the procedure in Find the CredHub Credentials.

2. Find the GUID for the service instance that you want to restore by running:

   cf service SERVICE-INSTANCE-NAME --guid
   

   Record the output.

   Note: If you do not know the name of the service instance, run cf services to see a listing of all service instances in the space. The service instances are listed in the name column.

3. Record the information needed to log in to the BOSH Director VM by following the procedure in Gather Credential and IP Address Information.

4. Log in to the Ops Manager VM by following the procedure in Log in to the Ops Manager VM with SSH.

5. Set the API target of the CredHub CLI as your CredHub server by running:

   credhub api https://BOSH-DIRECTOR-IP:8844 \
   --ca-cert=/var/tempest/workspaces/default/root_ca_certificate
   

   Where BOSH-DIRECTOR-IP is the IP address of the BOSH Director VM.
   For example:

   $ credhub api https://10.0.0.5:8844 \
     --ca-cert=/var/tempest/workspaces/default/root_ca_certificate

6. Log in to CredHub by running:

   credhub login \
   --client-name=CREDHUB-CLIENT-NAME \
   --client-secret=CREDHUB-CLIENT-SECRET
   

   Where:

   • CREDHUB-CLIENT-NAME is the value you recorded for BOSH_CLIENT in Find the CredHub Credentials in step 1.
   • CREDHUB-CLIENT-SECRET is the value you recorded for BOSH_CLIENT_SECRET in Find the CredHub Credentials in step 1.
     

   For example:

   $ credhub login \
       --client-name=credhub \
       --client-secret=abcdefghijklm123456789

7. Use the CredHub CLI to retrieve the backup encryption key by running:

   credhub get \
   -n /p-bosh/service-instance_GUID/backup_encryption_key
   

   For example:

   $ credhub get \
   -n /p-bosh/service-instance_70d30bb6-7f30-441a-a87c-05a5e4afff26/backup_encryption_key

8. Copy the backup encryption key next to value in the output. You use this key when you restore the backup.
   

   For example:

   id: d6e5bd10-3b60-4a1a-9e01-c76da688b847
   name: /p-bosh/service-instance_70d30bb6-7f30-441a-a87c-05a5e4afff26/backup_encryption_key
   type: password
   value: UMexamplePPlCNWMdVMcNv7RC3Wi10
   version_created_at: 2018-04-02T23:16:09Z

Download the Backup Artifact

These instructions assume that you are using an Amazon S3 bucket as your backup destination. If you are using a different backup destination, see the documentation for your backup provider to download the backup.

Note: You can also use the AWS Management Console to download backups.

To download the backup artifact from an Amazon S3 bucket using the command line interface (CLI):

1. From the Ops Manager VM, download the deployment manifest file by running:

   bosh -e BOSH-ENVIRONMENT -d service-instance_GUID \
    manifest > PATH-TO-MANIFEST.yml
   

   Where GUID is the service instance GUID you recorded in the above step. For example:

   $ bosh -e my-env -d service-instance_12345678-90ab-cdef-1234-567890abcdef \
        manifest > ./manifest.yml

2. Record the following properties from the deployment manifest:

   • properties.service-backup.destinations[0].config.bucket_name: This is the bucket where the backups are uploaded.
   • properties.service-backup.destinations[0].config.bucket_path: This is the path within the bucket above.

3. Log in to the AWS CLI. For information about how to download and use the AWS CLI, see the AWS documentation.

4. List the available backups for the instance by running:

   aws s3 ls \
   --recursive s3://BUCKET-NAME/BUCKET-PATH/service-instance_GUID/
   

   Where:

   • BUCKET-NAME is the bucket where the backups are uploaded recorded in the previous step.
   • BUCKET-PATH is the path within the bucket recorded in the previous step.

   The artifacts are sorted by time.

5. Select the most recent backup file or an older backup you want to restore from. The backups are timestamped in the filename and have a .gpg extension.

6. Download the selected backup by running:

   aws s3 cp \
   s3://BUCKET-NAME/BUCKET-PATH/service-instance_GUID/YEAR/MONTH/DATE/mysql-backup-TIMESTAMP.tar.gpg \
   ./a-local-path/mysql-backup-TIMESTAMP.tar.gpg
   

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 you are restoring. If the topology you are restoring is:
     • Single node or leader-follower: Select a single node plan.
     • HA cluster: Select a HA cluster plan.
     • Multi‑Site Replication: 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. If you created a HA cluster service instance to restore to, modify the deployment manifest to scale the instances to one:

   1. From the Ops Manager VM, download the deployment manifest file by running:

      bosh -e BOSH-ENVIRONMENT -d service-instance_GUID \
       manifest > PATH-TO-MANIFEST.yml
      

      Where GUID is the service instance GUID you recorded in the above step. For example:

      $ bosh -e my-env -d service-instance_12345678-90ab-cdef-1234-567890abcdef \
         manifest > ./manifest.yml

   2. Update the instance group for your new service instance as follows:

      instance_groups:
      - name: mysql
        ...
        instances: 1    # Scale instances to 1
        ...
      

   3. Redeploy the deployment manifest by running:

      bosh -e BOSH-ENVIRONMENT -d service-instance_GUID \
       deploy PATH-TO-MANIFEST.yml
      

      For example:

      $ bosh -e my-env -d service-instance_12345678-90ab-cdef-1234-567890abcdef \
        deploy ./manifest.yml

5. From the Ops 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 recorded in the above step.
   
   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/.

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

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

   Where:

   • GUID is the service instance GUID.
   • 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-TIMESTAMP.tar.gpg \
       mysql/d7ff8d46-c3e8-449f-aea7-5a05b0a1929c:/tmp/

Restore the Service Instance

Warning: 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 MySQL for VMware Tanzu 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 BOSH SSH.

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 above.
   • RESTORE-FILE-PATH is the full path on the MySQL VM where the backup artifact exists.

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 created a HA cluster service instance to restore to, modify the downloaded deployment manifest to scale the instances to three:

   1. Update the instance group for your new service instance as follows:

      instance_groups:
      - name: mysql
        ...
        instances: 3    # Scale instances to 3
        ...
      

   2. From the Ops Manager VM, redeploy the deployment manifest by running:

      bosh -e BOSH-ENVIRONMENT -d service-instance_GUID  deploy PATH-TO-MANIFEST.yml
      

      For example:

      bosh -e my-env -d service-instance_12345678-90ab-cdef-1234-567890abcdef deploy ./manifest.yml
      

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 should be running and able to access the restored data.

Warning: If a developer rebinds an app to the MySQL for VMware Tanzu 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.