Backing up and restoring with mysqldump

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 Tanzu SQL for VMs database to an off-platform database.
• Backing up specific individual Tanzu SQL for VMs databases.
• Editing table definitions or insert statements before restoring the Tanzu SQL for VMs database.
• Migrating an off-platform database with encryption at rest or the Percona PAM Authentication plug-in enabled to a Tanzu SQL for VMs database. See Restore from an off-platform logical backup.

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

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

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

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

Back up and restore a Tanzu SQL for VMs logical backup

To back up and restore a Tanzu SQL for VMs logical backup:

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

Create a Tanzu SQL for VMs logical backup

Tanzu SQL for VMs 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 Tanzu SQL for VMs 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 Tanzu SQL for VMs 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 Tanzu SQL for VMs 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 Tanzu SQL for VMs 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