LATEST VERSION: 1.8 - CHANGELOG
MySQL for PCF v1.8

Back Up MySQL for Pivotal Cloud Foundry

Automated Backups

In versions of MySQL for Pivotal Cloud Foundry 1.7.0.0 and higher, automated backups can be enabled in the “Configure Backups” section. Backups has 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 affect 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 which 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.
  • Backups are currently stored on Amazon S3 only. Other storage targets will be added over time.

Configure Backups

Backups are enabled by default. If you do not wish for this deployment to be backed up, you must disable backups in three steps:

  1. Select “Disable Backups” in the Configure Backup section.
  2. Switch to “Resource Config,” section, and change the number of instances for Backup Prepare Node from 1 to 0.
  3. Return to the Installation Dashboard, and click Apply Changes.

Configuring Backups Step-by-Step

For each step, refer to the screenshot above.

  1. Select “Enable Backups”
  2. Enter the bucket name where backup artifacts will be uploaded. Using the S3 credentials provided, MySQL for PCF will automatically create the bucket if it does not exist.
  3. Enter a folder name for this cluster’s backups to be stored. Again, the folder will be automatically created if it does not exist. It is important to 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.
  4. Provide an AWS Access Key and Secret Access Key in the following fields. We recommend that you create an IAM credential that only has access to this bucket.
  5. Specify how often you’d like backups to occur in the final field, “Cron Schedule.” The syntax definition is similar to traditional cron, plus easy features such as @daily to back up once per day.

Note: For large databases, the default storage for the Backup Prepare Node may not be sufficient. In order to compress and encrypt backup artifacts, the VM must be configured with twice the amount of ephemeral disk space as the persistent disk space of the MySQL Server nodes.

Understanding Backup Metadata

Along with each release, MySQL for PCF will upload a mysql-backup-XXXXXXXXXX.txt file. The contents of that file will look something like this:

uuid = dfe9fcdd-7d0f-11e5-93b3-0695a7b9771f
name =
tool_name = innobackupex
tool_command = --user=root --password=... --stream=tar tmp/
tool_version = 1.5.1-xtrabackup
ibbackup_version = xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
server_version = 10.0.21-MariaDB-wsrep
start_time = 2015-10-28 01:04:40
end_time = 2015-10-28 01:04:43
lock_time = 1
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 1730899
partial = N
incremental = N
format = tar
compact = N
compressed = N
encrypted = N

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 will not be 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. This is a known defect, and will be fixed in a future release.

Restoring a Backup Artifact

MySQL for PCF is normally HA, in that it 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, please check with support to be sure that your existing cluster is beyond help.

The DR backups feature of MySQL for PCF is primarily intended as a way to recover data to the same Pivotal Cloud Foundry Foundation 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. Due to the way in which service instances are defined it is not currently possible to restore a MySQL for PCF database to a different Foundation.

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

  • Discover the encryption keys in the Credentials tab of the MySQL for PCF tile.
  1. If necessary, install the same version of the MySQL for PCF product in Ops Manager.
  2. Reduce the size of the MySQL for PCF cluster to a single node
    1. Select the MySQL for PCF tile > Settings > Resource Config > MySQL Server > Instances, and set to 1
    2. Click “Save”
    3. Click “Back to Installation Dashboard,” then “Apply Changes”
    4. Wait for deployment to succeed
  3. Prepare the first node for restoration
    1. SSH into the Ops Manager Director.
      For information, see the SSH into Ops Manager section in the Advanced Troubleshooting with the BOSH CLI topic.
    2. From there, use the BOSH CLI to SSH into first MySQL job.
      For more information, see the BOSH SSH section in the Advanced Troubleshooting with the BOSH CLI topic.
      • IP address can be found in MySQL for PCF > Status > MySQL Server
      • VM credentials can be found in MySQL for PCF > Credentials > MySQL Server > Vm Credentials
    3. Become super user
      sudo su
    4. Pause the local database server
      monit stop all
    5. Confirm that all jobs are listed as ‘not monitored’
      watch monit summary
    6. Delete the existing mysql data which is stored on disk
      rm -rf /var/vcap/store/mysql/*
  4. Restore the backup
    1. Move the compressed backup (named e.g. mysql-backup.tar.bzip2) to the node (e.g. via scp)
    2. Decrypt the file using the gpg command
      gpg --decrypt mysql-backup-1444362715.tar.gpg
    3. Expand the backup artifact into the data director of MySQL
      tar xvjf mysql-backup.tar.bzip2 --directory=/var/vcap/store/mysql
    4. MySQL process expects data directory to be owned by a particular user
      chown -R vcap:vcap /var/vcap/store/mysql
    5. monit start all
    6. watch monit summary until all jobs are listed as 'running’
    7. Exit out of the MySQL node
  5. Increase the size of the cluster back to three
    1. MySQL for PCF tile > Settings > Resource Config > MySQL Server > Instances to 3
    2. Click “Save”
    3. “Back to Installation Dashboard” > Apply Changes
    4. Wait for deployment to succeed

Manual Process

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

  • Locate the IP address for the MySQL node in the Status tab.

MySQL Server IP

  • Locate the root password for the MySQL server in the Credentials tab.

MySQL Server Root Password

Manual Backup

Manual backup can be performed 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 backup all databases in the MySQL deployment, use --all-databases:

    $ mysqldump -u root -p -h $MYSQL_NODE_IP --all-databases > user_databases.sql
    
  • To backup a single database, specify the database name:

    $ mysqldump -u root -p -h $MHQL_NODE_IP $DB_NAME > user_databases.sql
    

Manual Restore

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 will delete all data that existed in the database prior to the restore. Restoring a database using a full backup artifact, produced by mysqldump --all-databases for example, will replace all data and user permissions.

  • Restore from the data dump:

    $ mysql -u root -p -h $MYSQL_NODE_IP < user_databases.sql
    
  • Re-apply users privileges:

    $ mysql -u root -p -h $MYSQL_NODE_IP -e "FLUSH PRIVILEGES"
    

    This command tells the cluster to re-load user permissions using the data that has just been restored.

Examples

More examples can be found in the MariaDB documentation.

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