LATEST VERSION: 2.1 - CHANGELOG

Backing Up MySQL for Pivotal Cloud Foundry

This topic describes how to enable, configure, and use backups in MySQL for Pivotal Cloud Foundry (PCF).

Overview

Automated backups have the following features:

  • Periodically create and upload backup artifacts suitable for restoring the complete set of database instances allocated in the service
  • No locks, no downtime
  • The only effect on the serving systems is the amount of I/O required to copy the database and log files off of the VM
  • Includes a metadata file that contains the critical details of the backup artifact, including the effective calendar time of the backup
  • Backup artifacts are encrypted within the MySQL for PCF cluster of VMs; unencrypted data is never transported outside of the MySQL for PCF deployment

Enable Automated Backups

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

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

    • MySQL for PCF runs an scp command that secure-copies backup files to a VM or physical machine operating outside of PCF. The operator provisions the backup machine separately from their PCF installation. This is the most efficient option.
    • MySQL for PCF runs an S3 client that saves backups to an Amazon S3 bucket, Ceph storage cluster, or other S3-compatible endpoint certified by Pivotal.
  • When: Backups follow a schedule that you specify with a cron expression.

  • What: You can back up just the primary node, or all nodes in the cluster.

To enable automated backups and configure them for options above, perform the following steps:

  1. Navigate to the MySQL for Pivotal Cloud Foundry tile on the Ops Manager Installation Dashboard.
  2. Click Backups.
  3. Under Backups, click Enable Backups. Enable backups
  4. For Cron Schedule, enter a cron schedule for the backups. The syntax is similar to traditional cron, with additional features such as @every 1d, which specifies daily backups. See the cron Go library documentation for more information.
  5. If you want to back up all nodes, select the Back up all nodes checkbox.
  6. To enable backups using Ceph or AWS, continue to the Ceph or AWS section. To enable backups using SCP, continue to the SCP section.

Ceph or AWS

To back up your database on Ceph or Amazon Web Services (AWS) S3, perform the following steps:

  1. Select Ceph or Amazon S3.
    Configure backups s3
  2. Enter your S3 Endpoint URL. For instance, https://s3.amazonaws.com.
  3. Enter your S3 Bucket Name. Do not include an s3:// prefix, a trailing /, or underscores. If the bucket does not already exist, it will be created automatically.
  4. For Bucket Path, specify a folder within the bucket to hold your MySQL backups. Do not include a trailing /. If the folder does not already exist, it will be created automatically.

    Note: You must use this folder exclusively for this cluster’s backup artifacts. Mixing the backup artifacts from different clusters within a single folder can cause confusion and possible inadvertent loss of backup artifacts.

  5. For AWS Access Key ID and AWS Secret Access Key, enter your Ceph or AWS credentials. For AWS, Pivotal recommends creating an IAM credential that only has access to this bucket.
  6. Click Save.

SCP

To back up your database using SCP, perform the following steps:

  1. Select SCP to a Remote Host. Configure backups scp
  2. Enter the Username, Hostname, and Destination Directory for the backups.

    Note: Pivotal recommends using a VM not within the PCF deployment for the destination of SCP backups. SCP enables the operator to use any desired storage solution on the destination VM.

  3. For Private Key, paste in the private key that will be used to encrypt the SCP transfer.
  4. Enter the SCP Port. SCP runs on port 22 by default.
  5. Click Save.

Disable Automated Backups

To disable automated backups, perform the following steps:

  1. Navigate to the MySQL for Pivotal Cloud Foundry tile on the Ops Manager Installation Dashboard.
  2. Click Backups. Disable backups
  3. Under Backups, click Disable Backups.
  4. Under Backup Destination, click No Backups.
  5. Click Save.
  6. In the left navigation, click Resource Config.
  7. Change the number of instances for Backup Prepare Node from 1 to 0.
  8. Click Save.
  9. Return to the Ops Manager Installation Dashboard and click Apply Changes.

To configure automated backups for MySQL for PCF, perform the following steps:

  1. Navigate to the MySQL for Pivotal Cloud Foundry tile on the Ops Manager Installation Dashboard.
  2. Click Backups.

Understand Backups

The sections below describe the process that MySQL for PCF component jobs follow when performing automated backups, and the format for the metadata file that records information about each backup.

Backup Process

Operators use Ops Manager to configure the schedule for automated backups and the location and credentials needed to store backup artifacts.

The diagram below shows the process through which MySQL for PCF jobs initiate and run automated backups.

sequenceDiagram participant Blob store participant Service Backup job Note over Service Backup job: Triggered by timer, following schedule configured in Ops Manager Service Backup job->>Streaming Backup client:Request backup Streaming Backup client->>Streaming Backup tool:Request backup Streaming Backup tool->>MySQL server:Request backup Note over MySQL server: Flush tables with read lock MySQL server->>Streaming Backup tool:Data Streaming Backup tool->>Streaming Backup client:Data Streaming Backup client->>Service Backup job:Data Note over Service Backup job:Compress and encrypt Service Backup job->>Blob store:Backup artifact Note over Blob store:Store backup artifact, using creds configured in Ops Manager Blob store-->>Service Backup job:Confirm artifact stored Note over Service Backup job:Clean up local storage

Two MySQL for PCF component VMs host the jobs listed above as follows:

Job Job name in thecode Host VM
Service Backup service-backup Backup Prepare VM
Streaming Backup client streaming-backup-client
Streaming Backup tool streaming-backup-tool MySQL VM
MySQL server mysql

Backup Metadata

Along with each backup artifact, MySQL for PCF uploads a mysql-backup-XXXXXXXXXX.txt metadata file.

The contents of the metadata file resemble the following:

compact = N
encrypted = N
tool_version = 2.4.5
server_version = 10.1.20-MariaDB
end_time = 2017-05-05 23:26:19
binlog_pos = filename 'mysql-bin.000016', position '7000000', GTID of the last change '0-1-30000'
incremental = N
format = tar
compressed = N
uuid = 30000000-3000-1000-9000-40000000000f
name =
lock_time = 0
innodb_from_lsn = 0
innodb_to_lsn = 6286393
partial = N
tool_command = --user=admin --password=... --stream=tar tmp/
ibbackup_version = 2.4.5
tool_name = innobackupex
start_time = 2017-05-05 23:26:17

Within this file, the most important items are the start_time and the server_version entries. Transactions that have not been completed at the start of the backup effort are not present in the restored artifact.

Note: Both compressed and encrypted show as N in this file, yet the artifact uploaded by MySQL for PCF is both compressed and encrypted. This is a known bug.

Restore a Backup Artifact

MySQL for PCF keeps at least two complete copies of the data. In most cases, if a cluster is still able to connect to persistent storage, you can restore a cluster to health using the bootstrap process. Before resorting to a database restore, contact Pivotal Support to ensure your existing cluster is beyond help.

The disaster recovery backups feature of MySQL for PCF is primarily intended as a way to recover data to the same PCF deployment from which the data was backed up. This process replaces 100% of the data and state of a running MySQL for PCF cluster. This is especially relevant with regard to service instances and bindings.

Note: Because of how services instances are defined, you cannot restore a MySQL for PCF database to a different PCF deployment.

In the event of a total cluster loss, the process to restore a backup artifact to a MySQL for PCF cluster is entirely manual. Perform the following steps to use the offsite backups to restore your cluster to its previous state:

  1. Discover the encryption keys in the Credentials tab of the MySQL for PCF tile.
  2. If necessary, install the same version of the MySQL for PCF product in the Ops Manager Installation Dashboard.
  3. Perform the following steps to reduce the size of the MySQL for PCF cluster to a single node:
    1. From the Ops Manager Installation Dashboard, click the MySQL for PCF tile.
    2. Click Resource Config.
    3. Set the number of instances for MySQL Server to 1.
    4. Click Save.
    5. Return to the Ops Manager Installation Dashboard and click Apply Changes.
  4. After the deployment finishes, perform the following steps to prepare the first node for restoration:
    1. SSH into the Ops Manager Director. For more information, see the SSH into Ops Manager section in the Advanced Troubleshooting with the BOSH CLI topic.
    2. Retrieve the IP address for the MySQL server by navigating to the MySQL for PCF tile and clicking the Status tab.
    3. Retrieve the VM credentials for the MySQL server by navigating to the MySQL for PCF tile and clicking the Credentials tab.
    4. From the Ops Manager Director VM, use the BOSH CLI to SSH into the first MySQL job. For more information, see the BOSH SSH section in the Advanced Troubleshooting with the BOSH CLI topic.
    5. On the MySQL server VM, become super user:
      $ sudo su
    6. Pause the local database server:
      $ monit stop all
    7. Confirm that all jobs are listed as not monitored:
      $ watch monit summary
    8. Delete the existing MySQL data that is stored on disk:
      $ rm -rf /var/vcap/store/mysql/*
  5. Perform the following steps to restore the backup:
    1. Move the compressed backup file to the node using scp.
    2. Decrypt and expand the file using gpg, sending the output to tar:
      $ gpg --decrypt mysql-backup.tar.gpg | tar -C /var/vcap/store/mysql -xvf -
    3. Change the owner of the data directory, because MySQL expects the data directory to be owned by a particular user:
      $ chown -R vcap:vcap /var/vcap/store/mysql
    4. Start all services with monit:
      $ monit start all
    5. Watch the summary until all jobs are listed as running:
      $ watch monit summary
    6. Exit out of the MySQL node.
  6. Perform the following steps to increase the size of the cluster back to three:
    1. From the Ops Manager Installation Dashboard, click the MySQL for PCF tile.
    2. Click Resource Config.
    3. Set the number of instances for MySQL Server to 3.
    4. Click Save.
    5. Return to the Ops Manager Installation Dashboard and click Apply Changes.

Perform Manual Backup

If you do not want to use the automated backups included in MySQL for PCF, you can perform backups manually.

Retrieve IP Address and Credentials

Perform the following steps to retrieve the IP address and credentials required for a manual backup:

  1. From the Ops Manager Installation Dashboard, click the MySQL for PCF tile.
  2. Click the Status tab.
  3. Locate the IP address for the MySQL node under MySQL Server. MySQL Server IP
  4. In the Credentials tab, from the MySQL Server job and Mysql Admin Password name, obtain the admin password.

Manual Backup

Back up your data manually with mysqldump. This backup acquires a global read lock on all tables, but does not hold it for the entire duration of the dump.

To back up all databases in the MySQL deployment:

$ mysqldump -u admin -p -h $MYSQL_NODE_IP --all-databases --single-transaction > user_databases.sql

To back up a single database, specify the database name:

$ mysqldump -u admin -p -h $MYSQL_NODE_IP $DB_NAME --single-transaction > user_databases.sql

Manual Restore

The procedure for restoring from a backup is the same whether one or multiple databases were backed up. Executing the SQL dump will drop, recreate, and refill the specified databases and tables.

WARNING: Restoring a database deletes all data that existed in the database before the restore. Restoring a database using a full backup artifact, produced by mysqldump --all-databases for example, replaces all data and user permissions.

Prepare to restore:

  • If running in HA configuration, reduce the size of the MySQL for PCF cluster to a single node, following the restore instructions above.
  • Locate the MySQL Admin credentials in the Credentials tab, as above.
  • Use the MySQL password and IP address to enable the creation of tables using any storage engine.
    $ mysql -u admin -p -h $MYSQL_NODE_IP -e "SET GLOBAL enforce_storage_engine=NULL"
    
  • Use the MySQL password and IP address to restore the MySQL databases by running the following command.
    $ mysql -u admin -p -h $MYSQL_NODE_IP < user_databases.sql
    
  • Use the MySQL password and IP address to restore original storage engine restriction.
    $ mysql -u admin -p -h $MYSQL_NODE_IP -e "SET GLOBAL enforce_storage_engine='InnoDB'"
    
  • To restore HA mode, re-configure MySQL for PCF to run using three nodes in the same way as the restoring instructions above.
  • If not running HA mode, it’s important to restart the database server. This step is not necessary if scaling back to three MySQL nodes.
    $ monit stop mariadb_ctrl
    $ monit start mariadb_ctrl
    

For more examples of manual backup and restore procedures, see the MariaDB documentation.

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