LATEST VERSION: 2.1 - CHANGELOG

Backing Up and Restoring On-Demand MySQL for PCF

This topic describes how to enable and configure automated backups for the MySQL for Pivotal Cloud Foundry (PCF) service and how to manually restore a MySQL service instance from a backup.

About Automated Backups

Automated backups do the following:

  • Periodically create and upload backups suitable for restoring all of the databases used by the service instance.
  • Operate without locking apps out of the database; no downtime.
  • Include a metadata file that contains the critical details of the backup, including the calendar time of the backup.
  • Encrypt backups within the MySQL for PCF VM; unencrypted data is never transported outside the MySQL for PCF deployment.

About Backup Files and Metadata

When MySQL for PCF runs a backup, it uploads two files with Unix epoch-timestamped filenames of the form mysql-backup-TIMESTAMP:

  • The encrypted data backup file mysql-backup-TIMESTAMP.tar.gpg
  • A metadata file mysql-backup-TIMESTAMP.txt

The metadata file contains information about the backup and looks like this:

ibbackup_version = 2.4.5
end_time = 2017-04-24 21:00:03
lock_time = 0
binlog_pos =
incremental = N
encrypted = N
server_version = 5.7.16-10-log
start_time = 2017-04-24 21:00:00
tool_command = --user=admin --password=... --stream=tar tmp/
innodb_from_lsn = 0
innodb_to_lsn = 2491844
format = tar
compact = N
name =
tool_name = innobackupex
partial = N
compressed = N
uuid = fd13cf26-2930-11e7-871e-42010a000807
tool_version = 2.4.5

Within this file, the most important items are the start_time and the server_version entries.

The backup process does not interrupt MySQL service, but backups only reflect transactions that completed before their start_time.

Note: Although both compressed and encrypted show as N in this file, the backup uploaded by MySQL for PCF is both compressed and encrypted. This is a known issue.

About Enabling Automated Backups

You can configure MySQL for PCF to automatically back up its databases to external storage.

  • How and where: There are four options for how automated backups transfer backup data and where the data saves to:

    • Option 1: MySQL for PCF runs an SCP command that secure-copies backups to a VM or physical machine operating outside of PCF. The operator provisions the backup machine separately from their PCF installation. This is the fastest option.
    • Option 2: MySQL for PCF runs an Amazon S3 client that saves backups to an S3 bucket, a Ceph storage cluster, or another S3-compatible endpoint certified by Pivotal.
    • Option 3: MySQL for PCF runs an GCS SDK that saves backups to an Google Cloud Storage bucket.
    • Option 4: MySQL for PCF runs an Azure SDK that saves backups to an Azure storage account.
  • When: Backups follow a schedule that you specify with a cron expression.

  • What: Each MySQL instance backs up its entire MySQL data directory, consistent to a specific point in time.

To enable and configure automated backups, follow the procedures below according to the option you choose for external storage.

Option 1: Back Up with SCP

SCP enables the operator to use any desired storage solution on the destination VM.

To back up your database using SCP, complete the following procedures:

Create a Public and Private Key Pair

MySQL for PCF accesses a remote host as a user with a private key for authentication. Pivotal recommends that this user and key pair be solely for MySQL for PCF.

  1. Determine the remote host that you will be using to store backups for MySQL for PCF. Ensure that the MySQL service instances will be able to access the remote host.

    Note: Pivotal recommends using a VM outside the PCF deployment for the destination of SCP backups. As a result you may need to enable public IPs for the MySQL VMs.

  2. (Recommended) Create a new user for MySQL for PCF on the destination VM.

  3. (Recommended) Create a new public and private key pair for authenticating as the above user on the destination VM.

Configure Backups in Ops Manager

Use Ops Manager to configure MySQL for PCF to backup using SCP.

  1. In Ops Manager, open the MySQL for PCF tile > Backups pane.

  2. Select the SCP radio button. SCP Backup Configuration Form

  3. Fill in the fields as follows:

  • Username Enter the user that you created above.
  • Private Key Enter the private key that you created above. The public key should be stored for ssh and scp access on the destination VM.
  • Hostname Enter the IP or DNS entry that should be used to access the destination VM.
  • Destination Directory Enter the directory in which MySQL for PCF should upload backups.
  • Cron Schedule Enter a cron schedule, using standard cron syntax, to take backups of each service instance.
  • Fingerprint Enter the fingerprint of the destination VMs public key. This helps to detect any changes to the destination VM.
  • Enable Email Alerts Select to receive email notifications when a backup failure occurs. Also verify that you done the following:
    • Added all users who need to be notified about failed backups to System org and System space
    • Configured Email Notifications in Elastic Runtime, see Configure Email Notifications for your IaaS: AWS, Azure, GCP, OpenStack, or vSphere.

Option 2: Back Up to Ceph or S3

To back up your database on Ceph or Amazon S3, complete the following procedures:

Create a Policy and Access Key

MySQL for PCF accesses your S3 store through a user account. Pivotal recommends that this account be solely for MySQL for PCF. You need to apply a minimal policy that lets the user account upload backups to your S3 store.

  1. Create a policy for your MySQL for PCF user account. Policy creation varies across S3 providers, but MySQL for PCF needs permission to list and upload to buckets. To use buckets that do not already exist, it additionally needs permission to create buckets.
    For example, in AWS create a new custom policy (IAM > Policies > Create Policy > Create Your Own Policy) and paste in the following permissions:

    {
    "Version": "2012-10-17",
    "Statement": [
    {
      "Sid": "ServiceBackupPolicy",
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:ListBucketMultipartUploads",
        "s3:ListMultipartUploadParts",
        "s3:CreateBucket",
        "s3:PutObject"
      ],
      "Resource": [
        "arn:aws:s3:::MY_BUCKET_NAME/*",
        "arn:aws:s3:::MY_BUCKET_NAME"
      ]
    }
    ]
    }
    

  2. (Recommended) Create a new user for MySQL for PCF and record its Access Key ID and Secret Access Key, the user credentials.

  3. Attach the policy you created to the AWS user account that MySQL for PCF will use to access S3 (IAM > Policies > Policy Actions > Attach).

Configure Backups in Ops Manager

Use Ops Manager to connect MySQL for PCF to your S3 account.

  1. In Ops Manager, open the MySQL for PCF tile > Backups pane.

  2. Select the Ceph or Amazon S3 radio button.
    S3 Backup Configuration Form

  3. Fill in the fields as follows:

  • Access Key ID and Secret Access Key Enter the S3 Access Key ID and Secret Access Key from above.
  • Endpoint URL Enter the S3 compatible endpoint URL for uploading backups. URL must start with http:// or https://. The default is https://s3.amazonaws.com
  • Region Choose the region in which your bucket is located. If you are not using S3 leave it as “V2 Signature - N/A”
  • S3 Signature Version If your bucket’s region support v4 signatures, select “V4 Signature”
  • Bucket Name Enter the bucket name in which to upload.
  • Bucket Path Enter the path in the bucket to store backups.
  • Cron Schedule Enter a cron schedule, using standard cron syntax, to take backups of each service instance.
  • Enable Email Alerts Select to receive email notifications when a backup failure occurs. Also verify that you done the following:
    • Added all users who need to be notified about failed backups to System org and System space
    • Configured Email Notifications in Elastic Runtime, see Configure Email Notifications for your IaaS: AWS, Azure, GCP, OpenStack, or vSphere.

Option 3: Back Up to GCS

To back up your database on Google Cloud Storage (GCS), complete the following procedures:

Create a Policy and Access Key

MySQL for PCF accesses your GCS store through a service account. Pivotal recommends that this account be solely for MySQL for PCF. You need to apply a minimal policy that lets the user account upload backups to your GCS store.

  1. Create a service account for MySQL for PCF. MySQL for PCF needs permission to list and upload to buckets. To use buckets that do not already exist, it additionally needs permission to create buckets. In the GCS console, create a new service account (IAM and Admin > Service Accounts > Create Service Account).

  2. Enter a unique name in the Service account name field, such as MySQL-for-PCF.

  3. In the Roles dropdown, grant the MySQL-for-PCF service account the Storage Admin role.

  4. Check the Furnish a new private key box so that a new key is created and downloaded.

  5. Click Create and take note of the name and location of the service account JSON file that is downloaded.

Configure Backups in Ops Manager

Use Ops Manager to connect MySQL for PCF to your GCS account.

  1. In Ops Manager, open the MySQL for PCF tile > Backups pane.

  2. Select the GCS radio button. GCS Backup Configuration Form

  3. Fill in the fields as follows:

  • Project ID Enter the Project ID for the Google Cloud project that you are using.
  • Bucket Name Enter the bucket name in which to upload.
  • Service Account JSON Enter the contents of the service account json file that you downloaded when creating a service account above.
  • Cron Schedule Enter a cron schedule, using standard cron syntax, to take backups of each service instance.
  • Enable Email Alerts Select to receive email notifications when a backup failure occurs. Also verify that you done the following:
    • Added all users who need to be notified about failed backups to System org and System space
    • Configured Email Notifications in Elastic Runtime, see Configure Email Notifications for your IaaS: AWS, Azure, GCP, OpenStack, or vSphere.

Option 4: Back Up to Azure Storage

Complete the following steps to back up your database to your Azure Storage account.

  1. In Ops Manager, open the MySQL for PCF tile > Backups pane.

  2. Select the Azure radio button. Azure Backup Configuration Form

  3. Fill in the fields as follows:

  • Account Enter the Account name for the Microsoft Azure account that you are using.
  • Azure Storage Access Key Enter one of the storage access keys that can be used to access the Azure Storage account.
  • Container Name Enter the container name that backups should be uploaded to.
  • Destination Directory Enter the directory in which backups should be uploaded to inside of the Container.
  • Blob Store Base URL By default, backups are sent to the public Azure blob store. To use an on-premise blob store, enter the hostname of the blob store.
  • Cron Schedule Enter a cron schedule, using standard cron syntax, to take backups of each service instance.
  • Enable Email Alerts Select to receive email notifications when a backup failure occurs. Also verify that you done the following:
    • Added all users who need to be notified about failed backups to System org and System space
    • Configured Email Notifications in Elastic Runtime, see Configure Email Notifications for your IaaS: AWS, Azure, GCP, OpenStack, or vSphere.

Disable Automated Backups

To disable MySQL for PCF automated backups:

  1. In Ops Manager, open the MySQL for PCF tile > Backups pane.

  2. Select Disable Backups.
    Disable Backups

  3. Click Save.

  4. Return to the Ops Manager Installation Dashboard and click Apply Changes.

Manual Backup

MySQL for PCF 2.0 disables remote admin access to MySQL databases. To access your MySQL database to perform a manual backup, you must create a service key for each service instance you want to back up.

This backup acquires a global read lock on all tables, but does not hold it for the entire duration of the dump.

Perform the following steps to back up your MySQL for PCF data manually:

  1. Use the Cloud Foundry Command Line Interface (cf CLI) to target the Cloud Controller of your PCF deployment with cf api api.YOUR-SYSTEM-DOMAIN. For example:
    $ cf api api.sys.cf-example.com
    For more information about installing and using the cf CLI, see the cf CLI documentation.
  2. Log in:
    $ cf login
  3. Create a service key for the MySQL service instance. Run the following command: cf create-service-key SERVICE-INSTANCE-NAME SERVICE-KEY-NAME -c '{"permissions":"read-only"}'

    Where:

    • SERVICE-INSTANCE-NAME: Enter the name of the existing MySQL service instance that contains the data you want to back up.
    • SERVICE-KEY-NAME: Choose a name for the new service key.

    For example:

    $ cf create-service-key mysql-spring spring-key \
    -c '{"permissions":"read-write"}'
    Creating service key spring-key for service instance mysql-spring as admin...
    OK
    

  4. After creating the service key, retrieve its information. Run the following command: cf service-key SERVICE-INSTANCE-NAME SERVICE-KEY-NAME

    Where:

    • SERVICE-INSTANCE-NAME: Enter the name of the MySQL service instance you created a service key for.
    • SERVICE-KEY-NAME: Enter the name of the newly created service key.

    For example:

    $ cf service-key mysql-spring spring-key
    Getting key spring-key for service instance mysql-spring as admin...

    { "hostname": "10.10.10.5", "jdbcUrl": "jdbc:mysql://10.10.10.5:3306/cf_e2d148a8_1baa_4961_b314_2431f57037e5?user=abcdefghijklm\u0026password=123456789", "name": "cf_e2d148a8_1baa_4961_b314_2431f57037e5", "password": "123456789", "port": 3306, "uri": "mysql://abcdefghijklm:123456789@10.10.10.5:3306/cf_e2d148a8_1baa_4961_b314_2431f57037e5?reconnect=true", "username": "abcdefghijklm" }

  5. Examine the output and record the following values:

    • hostname: The MySQL for PCF proxy IP address
    • password: The password for the user that can be used to perform backups of the service instance database
    • username: The username for the user that can be used to perform backups of the service instance database
  6. Use mysqldump to back up the data for your service instance. Run the following command: mysqldump -u USERNAME -p PASSWORD -h MYSQL-PROXY-IP --all-databases --single-transaction > BACKUP-NAME.sql

    Where:

    • USERNAME: Enter the username retrieved from the output of cf service-key.
    • PASSWORD: Enter the password retrieved from the output of cf service-key.
    • MYSQL-PROXY-IP: Enter the value of hostname retrieved from the output of cf service-key.
    • BACKUP-NAME: Enter a name for the backup file.

    For example:

    $ mysqldump -u abcdefghijklm \
    -p 123456789 \
    -h 10.10.10.8 \
    --all-databases \
    --single-transaction > spring-db-backup.sql

Restore a Service Instance from Backup

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

To restore a MySQL for PCF instance from an offsite backup, download the backup and restore to a new instance by following these procedures:

Download the Backups

These instructions assume that you are using S3 as your backup destination. If you are using one of the other backup options, steps 6 and 8 are different.

  1. Run cf service to determine the GUID associated with the service instance that you want to restore.

    $ cf service my-instance --guid
    12345678-90ab-cdef-1234-567890abcdef
    

  2. Follow the Advanced Troubleshooting instructions to log into the Ops Manager VM, where you can run the BOSH CLI.

  3. Download the manifest for the service instance deployment using the GUID from above.

    $ bosh download manifest service-instance_12345678-90ab-cdef-1234-567890abcdef ./manifest.yml
    

  4. Inspect the downloaded manifest and record the properties.cf-mysql-backup.symmetric_key and backup destination bucket. You will use this later to decrypt the backup.

  5. Install and log in to the AWS CLI.

  6. List the available backups for the instance.

    $ aws s3 ls --recursive s3://$BUCKET_NAME/service-instance_12345678-90ab-cdef-1234-567890abcdef
    

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

  8. Download the selected backup.

    $ aws s3 cp s3://$BUCKET_NAME/$BUCKET_PATH/YEAR/MONTH/DATE/mysql-backup-1489168980-0.tar.gpg ./a-local-path/mysql-backup-1489168980-0.tar.gpg
    

    Note: You can also log in to AWS and download S3 backups from a browser.

Restore the Service Instance

These instructions assume you have a backup downloaded. To restore the backup to a new service instance:

  1. Create a new service instance to restore to.

    $ cf create-service p.mysql db-small NEW-SERVICE-INSTANCE
    

  2. Prepare the service instance for restoration.

    1. Determine the GUID associated with your service instance:
      $ cf service NEW-SERVICE-INSTANCE --guid
      
    2. Download the manifest for the service instance deployment using the GUID from above.
      $ bosh download manifest service-instance_GUID ./manifest.yml
      
    3. Set the BOSH deployment to new downloaded manifest.
      $ bosh deployment ./manifest.yml
      
    4. Use the BOSH CLI to ssh in to the newly created MySQL service instance.
      $ bosh ssh mysql
      
    5. After securely logging into MySQL, run sudo su to become root.
      $ sudo su
      
    6. Stop all running processes.
      $ monit stop all
      
    7. Run watch with monit summary and wait until all jobs show as not monitored. This means they have stopped.
      $ watch monit summary
      
    8. Delete the existing MySQL database stored on disk.
      $ rm -rf /var/vcap/store/mysql/data/*
      

      WARNING: This is a destructive action and should only be run on a new and unused service instance.

    9. Exit the MySQL VM.
  3. Restore the backup to the new service instance:

    1. Copy the downloaded backup to the new service instance.
      $ bosh scp mysql-backup-TIMESTAMP.tar.gpg BOSH-INSTANCE:DESTINATION-PATH
      
      • 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/.
    2. Use the BOSH CLI to ssh in to the newly created MySQL service instance.
      $ bosh ssh mysql
      
    3. After securely logging into MySQL, run sudo su to become root.
      $ sudo su
      
    4. Run gpg --decrypt to decrypt the backup file. When prompted, enter the GPG decryption passphrase from the service instance manifest’s properties.cf-mysql-backup.symmetric_key value, which you recorded earlier.
      $ gpg --decrypt /tmp/mysql-backup.tar.gpg > /tmp/mysql-backup.tar
      
    5. Expand the backup into the data directory for the MySQL server.
      $ tar xvf /tmp/mysql-backup.tar --directory=/var/vcap/store/mysql/data/
      
    6. Modify the data directory owner to vcap or the mysql server cannot access the data.
      $ chown -R vcap:vcap /var/vcap/store/mysql
      
    7. Restart the BOSH jobs to resume the MySQL server.
      $ monit start all
      
    8. Use monit to ensure all processes start successfully; all processes should be listed as running.
      $ watch monit summary
      
    9. Delete old service binding created users. User tables get backed up along with databases, so any users that existed in the backup are transferred over during restoration. To secure the database, determine and delete all users that have randomly generated usernames:
      $ /var/vcap/packages/percona-server/bin/mysql -uadmin -p
      
      mysql> select USER from mysql.user; +----------------------------------+ | USER | +----------------------------------+ | 9b930c5813e049ca863b4acbf4000a8c | | admin | | e82b9b479bf64eb5854c93065810c95b | | mysql.sys | +----------------------------------+ 4 rows in set (0.00 sec)
      mysql> drop user 9b930c5813e049ca863b4acbf4000a8c;
      mysql> drop user e82b9b479bf64eb5854c93065810c95b;
    10. Exit the MySQL VM.
  4. Update your CF app to bind to the new service instance.

    $ cf bind-service my-app NEW-SERVICE-INSTANCE
    

  5. Start your CF app.

    $ cf start my-app
    

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

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