Restoring MySQL for PCF

Overview
Retrieve Backup Encryption Key
Download the Backup Artifact
Restore the Service Instance
Restage the Service Instance

Warning: MySQL for PCF v2.6x is no longer supported because it has reached the End of General Support (EOGS) phase as defined by the Support Lifecycle Policy. To stay up to date with the latest software and security updates, upgrade to a supported version.

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:

Retrieve Backup Encryption Key
Download the Backup Artifact
Restore the Service Instance

Retrieve Backup Encryption Key

To retrieve the backup encryption key:

Find and record your CredHub credentials by following the procedure in Find the CredHub Credentials.
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.
Record the information needed to log in to the BOSH Director VM by following the procedure in Gather Credential and IP Address Information.
Log in to the Ops Manager VM by following the procedure in Log in to the Ops Manager VM with SSH.

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

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

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

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):

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

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.
Log in to the AWS CLI. For information about how to download and use the AWS CLI, see the AWS documentation.
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.
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.

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:

Create and Prepare a New Service Instance for Restore
Restore the service instance by following the procedure in Restore a Single Node or Leader-Follower Instance or Restore an HA Cluster Instance
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:

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.

For more information, see Create a Service Instance.

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.
Locate and record the GUID associated with your new service instance by running:
```
cf service NEW-INSTANCE-NAME --guid
```

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

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

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

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

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

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

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

Use the BOSH CLI to SSH in to the newly created MySQL service instance by following the procedure in BOSH SSH.
After securely logging in to MySQL, become root by running:
```
sudo su
```
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:

After securely logging in to MySQL, become root by running:
```
sudo su
```
Pause the galera-init job by running:
```
monit stop galera-init
```
Confirm that the galera-init job is listed as not monitored by running:
```
watch monit summary
```
Delete the existing MySQL data that is stored on disk by running:
```
rm -rf /var/vcap/store/mysql/*
```
Move the compressed backup file to the node using scp.

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 -

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.
Start the galera-init job by running:
```
monit start galera-init
```
Confirm that the galera-init job is listed as running by running:
```
watch monit summary
```

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.

Recreate your users and service bindings for your new service instance by running:
```
monit start galera-init
```
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:

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
```
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
```
Determine if the app is currently bound to a MySQL service instance by running:
```
cf services
```
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
```
Update your CF app to bind to the new service instance by running:
```
cf bind-service APP-NAME  NEW-INSTANCE-NAME
```
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 PCF 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.

Create a pull request or raise an issue on the source for this page in GitHub