Restoring MySQL for PCF

Page last updated:

This topic describes how to manually restore a MySQL for Pivotal Cloud Foundry service instance from a backup.

You can create a backup by following the procedures in Backing up MySQL for Pivotal Cloud Foundry

Overview

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

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

To restore a MySQL for Pivotal Cloud Foundry instance from an offsite backup:

  1. Retrieve Backup Encryption Key
  2. Download the Backup Artifact
  3. Restore 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:8844 \
--ca-cert=/var/tempest/workspaces/default/root_ca_certificate

    Where BOSH-DIRECTOR 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:

    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 manifest for the service instance deployment by specifying the deployment name as service-instance_GUID and a filename for the manifest. For example: 

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

  2. Record the following properties from the download 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

Restore the Service Instance

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

To restore the downloaded backup to a new service instance for a single node, leader-follower, or highly available (HA) cluster plan:

  1. Create and Prepare a New Service Instance for Restore
  2. Restore the service instance by following the procedure in Restore a Single Node or Leader-Follower Instance or Restore an HA Cluster Instance
  3. Restage the Service Instance

Create and Prepare a New Service Instance for Restore

You can only restore 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 SERVICE-PLAN NEW-INSTANCE-NAME

    Where:

    • SERVICE-PLAN is the name of the service plan for your new service instance.
    • NEW-INSTANCE-NAME is the name of the new service instance.

    If you are using an HA cluster plan, you can only restore backup artifacts to an HA service instance.

    If you are using a single node or leader-follower plan, you can only restore backup artifacts to a single node service instance. The persistent disk size of this single node plan must be three times larger than the maximum size of the data developers need. For more information, see Persistent Disk Usage.

    2. For more information, see Create a Service Instance.

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

    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

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

  4. From the Ops Manager VM, find and record the new instance name and GUID from BOSH by running:

    bosh -e BOSH-ENVIRONMENT  -d BOSH-DEPLOYMENT instances

    For example: 

    bosh -e my-env -d service-instance_12345678-90ab-cdef-1234-567890abcdef instances

  5. If you created a HA cluster service instance to restore to, modify the deployment manifest to scale the instances to one:

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

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

    2. Redeploy the deployment manifest by running:

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

      For example:

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

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

    bosh -e BOSH-ENVIRONMENT   \
-d BOSH-DEPLOYMENT scp mysql-backup-TIMESTAMP.tar.gpg BOSH-INSTANCE:DESTINATION-PATH

    Where:

    • BOSH-INSTANCE is mysql/INSTANCE-GUID. For example, mysql/d7ff8d46-c3e8-449f-aea7-5a05b0a1929c.
    • DESTINATION-PATH is where the backup file saves on the BOSH VM. For example, /tmp/.

Restore a Single Node or Leader-Follower 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 or leader-follower service instance using the restore utility. The restore utility is packaged with the MySQL for Pivotal Cloud Foundry 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 single node or leader-follower 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.

Restore an HA Cluster Instance

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

To restore an HA cluster:

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

    sudo su

  2. Pause the galera-init job by running:

    monit stop galera-init

  3. Confirm that the galera-init job is listed as not monitored by running:

    watch monit summary

  4. Delete the existing MySQL data that is stored on disk by running:

    rm -rf /var/vcap/store/mysql/*

  5. Move the compressed backup file to the node using scp.

  6. Decrypt and expand the file and send the output to TAR by running:

    gpg --decrypt mysql-backup.tar.gpg | tar -C /var/vcap/store/mysql -xvf -

  7. Change the owner of the data directory by running:

     chown -R vcap:vcap /var/vcap/store/mysql

    MySQL expects the data directory to be owned by a particular user.

  8. Start the galera-init job by running:

    monit start galera-init

1.1. Confirm that the galera-init job is listed as running by running:

```
watch monit summary
```

  1. Delete MySQL users and service bindings from the database by running:

    DELETE FROM mysql.user WHERE User NOT IN ('mysql.session', 'mysql.sys');


DELETE FROM mysql.db WHERE User NOT IN ('mysql.session', 'mysql.sys');


DELETE FROM mysql.procs_priv WHERE User NOT IN ('mysql.session', 'mysql.sys');


DELETE FROM mysql.columns_priv WHERE User NOT IN ('mysql.session', 'mysql.sys');


DELETE FROM mysql.proxies_priv WHERE User NOT IN ('mysql.session', 'mysql.sys');

DELETE FROM mysql.tables_priv WHERE User NOT IN ('mysql.session', 'mysql.sys');


DROP TABLE IF EXISTS cf_metadata.bindings;

    Note: If you do not delete users and service bindings from the database before restaging your app, the backup artifact includes users and bindings from the previous service instance. These users and bindings are not valid for the new service instance.

  2. Recreate your users and service bindings for your new service instance by running:

    monit start galera-init

  3. Confirm that the galera-init job is listed as not monitored by running:

    watch monit summary

Restage the Service Instance

After you restore your single node, leader-follower, or HA cluster service instance, you must restage your new service instance.

To restage your service instance:

  1. If you restored to a service instance with a single-node plan but want a leader-follower plan, 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 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. Redeploy the deployment manifest by running:

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

      For example:

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

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

    cf services

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

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

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

  6. Restage your CF 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 Pivotal Platform 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.