You can manually create a logical backup with mysqldump and restore a MySQL database with the backup.

A logical backup reproduces the database table structure and data, without copying the InnoDB data files. The backup contains SQL statements to recreate the data. Because of this, restoring a logical backup takes longer than 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 VMware Tanzu for MySQL database to an off-platform database.
  • Backing up specific individual VMware Tanzu for MySQL databases.
  • Editing table definitions or insert statements before restoring the VMware Tanzu for MySQL database.
  • Migrating an off-platform database with encryption at rest or the Percona PAM Authentication plug-in enabled to a VMware Tanzu for MySQL database. See Restore from an off-platform logical backup.

In most cases, if you want to copy a VMware Tanzu for MySQL database you can use the migrate command. For information about using the migrate command, see About data migration in VMware Tanzu for MySQL.

You can create a physical backup by following the procedure in Backing up and restoring VMware SQL with MySQL for Tanzu Application Service.

You can configure physical backups of your MySQL database in VMware Tanzu for MySQL tile. For information about backups configured by operators, see Configuring automated backups.

When you take a logical backup, VMware Tanzu for MySQL does not send an email notification.

Back up and restore a VMware Tanzu for MySQL logical backup

To back up and restore a VMware Tanzu for MySQL logical backup:

  1. Create a logical backup of your database. See Create a VMware Tanzu for MySQL logical backup.
  2. Restore the logical backup to a VMware Tanzu for MySQL service instance. For more information about restoring a logical backup, see Restore from a VMware Tanzu for MySQL logical backup.

Create a VMware Tanzu for MySQL logical backup

VMware Tanzu for MySQL deactivates remote admin access to MySQL databases. 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 VMware Tanzu for MySQL 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 service-gateway access or an SSH tunnel. For more information, see Creating a service instance with Service-Gateway access or Using SSH to connect from outside a deployment.

  4. View a list of your databases by running:

    mysql --user=USERNAME --password=PASSWORD \
--host=MYSQL-IP \
--port=MYSQL-PORT \
--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.
    • MYSQL-PORT is the MySQL Port. This value is 3306 if you are connecting to the database directly.

    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 \
--no-tablespaces \
--single-transaction \
--user=USERNAME --password=PASSWORD \
--host=MYSQL-IP \
--port=MYSQL-PORT \
--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.
    • MYSQL-PORT is the MySQL Port.
    • 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 \
--no-tablespaces \
--single-transaction \
--user=abcdefghijklm --password=123456789 \
--host=10.10.10.5 \
--port=3306 \
--databases canary\_db > canary\_db.sql

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

Restore from a VMware Tanzu for MySQL 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 VMware Tanzu for MySQL logical backup. 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 service-gateway access or an SSH tunnel. For more information, see Creating a service instance with Service-Gateway access or Using SSH to connect from outside a 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 plug-in enabled, you cannot use the migrate command.

To restore an off-platform logical backup to a VMware Tanzu for MySQL 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
check-circle-line exclamation-circle-line close-line
Scroll to top icon