Restoring MySQL for PCF
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
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 forBOSH_CLIENT
in Find the CredHub Credentials in step 1.CREDHUB-CLIENT-SECRET
is the value you recorded forBOSH_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.
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 instancesThe 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/
For more information, see Create a Service Instance.
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 asnot 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 asrunning
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 asnot 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:
Update the instance group for your new service instance as follows:
instance_groups: - name: mysql ... instances: 3 # Scale instances to 3 ...
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.