Backing Up and Restoring with mysqldump

Page last updated:

This topic describes how to manually create a logical backup with mysqldump and restore a MySQL database with the backup.

Overview

A logical backup reproduces the database table structure and data, without copying the InnoDB data files. The backup contains SQL statements to re-create the data. Because of this, restoring a logical backup takes longer that restoring a physical backup. While the backup is running, operations against the database are stalled.

You might want to create a logical backup in the following use cases:

  • Migrating a MySQL for Pivotal Cloud Foundry database to an off-platform database.
  • Backing up specific individual MySQL for Pivotal Cloud Foundry databases.
  • Editing table definitions or insert statements before restoring the MySQL for Pivotal Cloud Foundry database.
  • Migrating an off-platform database with encryption at rest or the Percona PAM Authentication plugin enabled to a MySQL for Pivotal Cloud Foundry database. See Restore from an Off-Platform Logical Backup.

In most cases, if you want to copy a MySQL for Pivotal Cloud Foundry database you should use the migrate command. For information about using the migrate command, see About Data Migration in MySQL for Pivotal Cloud Foundry.

The operator configures automated physical backups of your MySQL database. Developers do not have access to physical backups. For information about automated backups configured by operators, see Backing Up MySQL for Pivotal Cloud Foundry.

Note: When you take a logical backup, MySQL for Pivotal Cloud Foundry does not send an email notification. You only receive notifications for automated backups.

Back Up and Restore a MySQL for Pivotal Cloud Foundry Logical Backup

To back up and restore a MySQL for Pivotal Cloud Foundry logical backup:

  1. Create a logical backup of your database. See Create a MySQL for Pivotal Cloud Foundry Logical Backup below.
  2. Restore the logical backup to a MySQL for Pivotal Cloud Foundry service instance. See Restore from a MySQL for Pivotal Cloud Foundry Logical Backup below.

Create a MySQL for Pivotal Cloud Foundry Logical Backup

MySQL for Pivotal Cloud Foundry disables remote admin access to MySQL databases. Because of this, you must create a read-only service key to access the database for the service instance you want to back up.

To back up your MySQL for Pivotal Cloud Foundry data manually:

  1. Create and retrieve read-only access credentials by following the procedure in Create Read-only Access Credentials.

  2. From the output of the previous step, record the following values:

    • hostname: The MySQL BOSH DNS hostname
    • password: The password for the user that can be used for backups of the service instance database
    • username: The username for the user that can be used for backups of the service instance database
  3. Connect to your service instance, by either using an SSH tunnel or by connecting directly to its IP address. For more information, see Establish a Connection to a Service Instance from Outside Your Pivotal Cloud Foundry Deployment.

  4. View a list of your databases by running:

    mysql --user=USERNAME --password=PASSWORD \
    --host=MYSQL-IP \
    --silent --execute='show databases'

    Where:

    • USERNAME is the username retrieved from the output of cf service-key.
    • PASSWORD is the password retrieved from the output of cf service-key.
    • MYSQL-IP is the MySQL IP address. This value is 0 if you are connecting using an SSH tunnel.

    For example:

    $ mysql --user=abcdefghijklm --password=123456789 \
    --host=10.10.10.5 \
    --silent --execute='show databases'
    

  5. Do not back up the following databases:

    • cf_metadata
    • information_schema
    • mysql
    • performance_schema
    • sys
  6. For each remaining database, back up by running:

    mysqldump --set-gtid-purged=off --single-transaction \
    --user=USERNAME --password=PASSWORD \
    --host=MYSQL-IP \
    --databases DB-NAME > BACKUP-FILE
    

    Where:

    • USERNAME is the username retrieved from the output of cf service-key.
    • PASSWORD is the password retrieved from the output of cf service-key.
    • MYSQL-IP is the MySQL IP address.
    • DB-NAME is the name of the database.
    • BACKUP-FILE is a name you create for the backup file. Use a different filename for each backup.

    The --set-gtid-purged=off flag enables you to restore the backup without admin privileges.

    For example:

    $ mysqldump --set-gtid-purged=off --single-transaction \
    --user=abcdefghijklm --password=123456789 \
    --host=10.10.10.5 \
    --databases canary_db > canary_db.sql
    

    For more information about the mysqldump utility, see mysqldump in the MySQL Documentation.

Restore from a MySQL for Pivotal Cloud Foundry Logical Backup

To restore a logical backup:

  1. (Optional) If you want to create a new service instance, do the procedure in Create a Service Instance.

  2. Retrieve the credentials for the service instance you are restoring the backup to by following steps 1 and 2 in Create a MySQL for Pivotal Platform Logical Backup above. You can either restore the backup to an existing service instance or the one you created in step 1.

  3. Connect to your service instance, by either using an SSH tunnel or by connecting directly to its IP address. For more information, see Establish a Connection to a Service Instance from Outside Your Deployment.

  4. Restore your data from the SQL file on your local machine by running:

    mysql --user=USERNAME --password=PASSWORD --host=MYSQL-IP  < BACKUP-FILE
    

    Where:

    • USERNAME is the username retrieved from the output of cf service-key.
    • PASSWORD is the password retrieved from the output of cf service-key.
    • MYSQL-IP is the MySQL IP address.
    • BACKUP-FILE is the name of your backup artifact.

Restore from an Off-Platform Logical Backup

This section assumes that you have already created a logical backup for your off-platform database using mysqldump.

If you want to restore a logical backup from an off-platform database that has encryption at rest or the Percona PAM Authentication plugin enabled, you cannot use the migrate command.

To restore an off-platform logical backup to a MySQL for Pivotal Cloud Foundry database:

  1. If your database has encryption at rest enabled, delete all instances of ENCRYPTION='Y' from your backup artifact.

  2. Retrieve your service instance GUID by running:

    cf service SERVICE-INSTANCE-NAME --guid
    

    Record the output.

  3. Copy the backup artifact to the service instance by running:

    bosh -d service-instance_GUID scp ./BACKUP-FILE mysql:/tmp/
    

    Where GUID is the GUID you recorded in the previous step.

  4. SSH into the service instance by running:

    bosh -d service-instance_GUID ssh
    
  5. Restore your backup artifact into mysql by running:

    mysql --defaults-file=/var/vcap/jobs/mysql/config/mylogin.cnf  \
    -D SERVICE-INSTANCE-NAME < /tmp/BACKUP-FILE