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 four 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.
    • MySQL for PCF runs an Azure client that saves backups to a Azure blob store container.
    • MySQL for PCF runs a GCS client that saves backups to a Google Cloud Storage bucket.
  • 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 the tile to open the configuration settings.
  3. Under the Settings tab, click Backups. The Automated Database Backups pane opens.
  4. Under Backups, click Enable Backups. Enable backups
  5. 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.
  6. If you want to back up all nodes, select the Back up all nodes checkbox.
  7. Configure a destination where backup artifacts will be delivered. Skip to the appropriate section below:
  8. Under the Settings tab, click Resource Config.
  9. Set the number of instances for Backup Prepare Node to 1.
  10. Click Save.

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 example, 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.

Azure

To back up your database on Azure, do the following:

  1. Select Azure Blob Storage.
    Configure backups azure
  2. Enter your Azure Storage Account, for example, mystorageaccount.
  3. Enter your Azure Storage Access Key.
  4. Enter your Container name, for example, mycontainer.
  5. For Container Path, specify a folder within the container 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.

  6. (Optional) Configure Base URL to specify the base URL for your on-premise blobstore.

    If you don’t specify a URL here, by default, backups are sent to the public Azure blobstore.
  7. Click Save.

Google Cloud Storage

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

  1. Select Google Cloud Storage.
    Configure backups gcs
  2. Enter your Google Cloud Storage Account, for example, mystorageaccount@google.com.

    Note: Service accounts must have Storage Admin IAM permissions. See documentation for how to generate the JSON key.

  3. Enter your Google Cloud Storage Bucket Name, for example, mybucket.
  4. Enter your Google Cloud Storage Project ID, for example, my-project-id.
  5. 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.

Note: To restore a single service instance, see the Restoring a Single Service Instance topic.

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

The procedure for backing up your data manually varies depending on whether you have enabled remote admin access to MySQL databases.

In MySQL for PCF v1.9 and v1.10, remote admin access is disabled by default. However, an operator can enable remote admin access when configuring the MySQL for PCF tile.

To determine whether remote admin access is enabled, perform the following steps:

  1. From the Ops Manager Installation Dashboard, click the MySQL for PCF tile.
  2. Click MySQL Server Configuration.
  3. Locate the Allow remote admin access checkbox and determine whether or not it is enabled.

    For more information, see MySQL Server Configuration.

  4. Use the following table to determine which procedure to follow for your manual backup:

    If remote admin access is… Then follow…
    disabled Remote Admin Access Disabled (Default)
    enabled Remote Admin Access Enabled

Manual Backup with Remote Admin Access Disabled (Default)

Perform the following steps to back up your MySQL for PCF data manually with remote admin access disabled:

  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

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

Manual Backup with Remote Admin Access Enabled

If remote admin access is enabled, use mysqldump to back up the data as the admin user.

Note: 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, run the following command: mysqldump -u admin -p PASSWORD -h MYSQL-NODE-IP --all-databases --single-transaction > BACKUP-NAME.sql

Where:

For example:

$ mysqldump -u admin -p 123456789 \
  -h 10.10.10.8 --all-databases \
  --single-transaction > user_databases.sql

To back up a single database, run the following command: mysqldump -u admin -p PASSWORD -h MYSQL-NODE-IP DB-NAME --single-transaction > BACKUP-NAME.sql

Where:

For example:

$ mysqldump -u admin -p 123456789 \
-h MYSQL-NODE-IP DB-NAME \
--single-transaction > user_databases.sql

Manual Restore

The procedure for restoring your data from a manual backup varies depending on whether you have enabled remote admin access to MySQL databases.

In MySQL for PCF v1.9 and v1.10, remote admin access is disabled by default. However, an operator can enable remote admin access when configuring the MySQL for PCF tile.

To determine whether remote admin access is enabled, perform the following steps:

  1. From the Ops Manager Installation Dashboard, click the MySQL for PCF tile.
  2. Click MySQL Server Configuration.
  3. Locate the Allow remote admin access checkbox and determine whether or not it is enabled.

For more information, see MySQL Server Configuration.

If remote admin access is disabled, perform the procedures in the Remote Admin Access Disabled (Default) section. If you have enabled remote admin access, perform the procedures in the Remote Admin Access Enabled section.

The procedure for restoring your MySQL data from a manual backup is the same for restoring a backup of one or multiple databases. 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.

Manual Restore with Remote Admin Access Disabled (Default)

Perform the following steps to restore your MySQL data from a manual backup with remote admin access disabled:

  1. If running in HA configuration, reduce the size of the MySQL for PCF cluster to a single node, following the procedures in the Restore a Backup Artifact section above.
  2. Perform steps 1 through 5 of the Manual Backup with Remote Admin Access Disabled (Default) section above.
  3. Use the MySQL client to enable the creation of tables using any storage engine. Run the following command: mysql -u USERNAME -p PASSWORD -h MYSQL-PROXY-IP -e "SET GLOBAL enforce_storage_engine=NULL"

    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.

    For example:

    $ mysql -u abcdefghijklm \
    -p 123456789 \ 
    -h 10.10.10.8 -e "SET GLOBAL enforce_storage_engine=NULL"

  4. Use the MySQL client to restore the MySQL database or databases. Run the following command:

    mysql -u USERNAME -p PASSWORD -h MYSQL-PROXY-IP < 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 the file name of the backup artifact.

    For example:

    $ mysql -u abcdefghijklm \
    -p 123456789 \ 
    -h 10.10.10.8 -e < user_databases.sql

  5. Use the MySQL client to restore the original storage engine restriction. Run the following command:

    mysql -u USERNAME -p PASSWORD -h MYSQL-PROXY-IP -e "SET GLOBAL enforce_storage_engine='InnoDB'"

    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.

    For example:

    $ mysql -u abcdefghijklm \
    -p 123456789 \ 
    -h 10.10.10.8 -e "SET GLOBAL enforce_storage_engine='InnoDB'"

  6. If you are running in HA mode, re-configure MySQL for PCF to run using three nodes by following the procedures in the Restore a Backup Artifact section above.

    If you are not running HA mode, restart the database server. This step is not necessary if scaling back to three MySQL nodes. Run the following commands:

    $ monit stop mariadb_ctrl
    $ monit start mariadb_ctrl
    

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

Manual Restore with Remote Admin Access Enabled

Perform the following steps to restore your MySQL data from a manual backup with remote admin access enabled:

  1. Use the MySQL client to enable the creation of tables using any storage engine. Run the following command: mysql -u admin -p PASSWORD -h MYSQL-NODE-IP -e "SET GLOBAL enforce_storage_engine=NULL"

    Where:

    For example:

    $ mysql -u abcdefghijklm \
    -p 123456789 \ 
    -h 10.10.10.8 -e "SET GLOBAL enforce_storage_engine=NULL"

  2. Use the MySQL password and IP address to enable the creation of tables using any storage engine. Run the following command: mysql -u admin -p PASSWORD -h MYSQL-NODE-IP -e "SET GLOBAL enforce_storage_engine=NULL"

    Where:

  3. Use the MySQL client to restore the MySQL database or databases. Run the following command:

    mysql -u admin -p PASSWORD -h MYSQL-NODE-IP < BACKUP-NAME.sql

    Where:

    For example:

    $ mysql -u admin \
    -p 123456789 \ 
    -h 10.10.10.8 -e < user_databases.sql

  4. Use the MySQL client to restore the original storage engine restriction. Run the following command:

    mysql -u admin -p PASSWORD -h MYSQL-NODE-IP -e "SET GLOBAL enforce_storage_engine='InnoDB'"

    Where:

    For example:

    $ mysql -u admin \
    -p 123456789 \ 
    -h 10.10.10.8 -e "SET GLOBAL enforce_storage_engine='InnoDB'"

  5. If you are running in HA mode, re-configure MySQL for PCF to run using three nodes by following the procedures in the Restore a Backup Artifact section above.

    If you are not running HA mode, restart the database server. This step is not necessary if scaling back to three MySQL nodes. Run the following commands:

    $ 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