Configuring Automated Backups

Note: In v2.9 and later, MySQL for VMware Tanzu is named VMware Tanzu SQL with MySQL for VMs.

Page last updated:

This topic describes how to configure automated, physical backups for VMware Tanzu SQL with MySQL for VMs.

Additionally, when they want, developers can create physical backups using the Cloud Foundry Command Line Interface (cf CLI) or logical backups using mysqldump. For more information about physical backups, see Backing Up and Restoring VMware Tanzu SQL with MySQL for VMs. For more information about logical backups, see Backing Up and Restoring with mysqldump.

You can restore a physical backup by following the procedures in Restore a Service Instance.

About Configuring Automated Backups

You can configure Tanzu SQL for VMs to automatically back up databases to external storage. Tanzu SQL for VMs backs up the entire data directory for each service instance.

Tanzu SQL for VMs backs up your database on a schedule. You configure this schedule with a cron expression.

Note: Configuring a cron expression overrides the default schedule for your service instance.

Developers can override the default for their service instance. For more information, see Backup Schedule.

To configure backups, follow the procedure for your external storage solution:

Back Up Using SCP

Secure copy protocol (SCP) enables operators to use any storage solution on the destination VM. This is the fastest method for backing up your database.

When you configure backups with SCP, Tanzu SQL for VMs runs an SCP command that uses SFTP to securely copy backups to a VM or physical machine operating outside of your deployment. The operator provisions the backup machine separately from their installation.

To back up your database using SCP:

Create a Public and Private Key‑Pair

Tanzu SQL for VMs accesses a remote host as a user with a private key for authentication. VMware recommends that this user and key-pair is only used for Tanzu SQL for VMs.

  1. Determine the remote host that you use to store backups for Tanzu SQL for VMs. Ensure that the MySQL service instances can access the remote host.

    Note: VMware recommends using a VM outside your deployment for the destination of SCP backups. As a result, you might need to enable public IPs for the MySQL VMs.

  2. (Recommended) Create a new user for Tanzu SQL for VMs on the destination VM.

  3. (Recommended) Create a new public and private key-pair for authenticating as the above user on the destination VM.

Configure Backups in Ops Manager

Use Ops Manager to configure Tanzu SQL for VMs to back up using SCP.

  1. In Ops Manager, open the Tanzu SQL for VMs tile Backups pane.

  2. Select SCP.

    Backup configuration pane shows SCP radio button selected and the fields
 in the pane are described in the table below.

  3. Configure the fields as follows:

    Field Instructions
    Username Enter the user that you created in Create a Public and Private Key‑Pair above.
    Private Key Enter the private key that you created in Create a Public and Private Key‑Pair above.
    Store the public key that is used for SSH and SCP access on the destination VM.
    Hostname Enter the IP address or DNS entry that is used to access the destination VM.
    Destination Directory Enter the directory that Tanzu SQL for VMs uploads backups to.
    SCP Port Enter the SCP port number for SSH. This port usually is 22.
    Cron Schedule Enter a cron expression using standard syntax. The cron expression sets the schedule for taking backups for each service instance. This overrides the default schedule for your service instance. Test your cron expression using a website such as Crontab Guru.

    Note: Developers can override the default for their service instance. For more information, see Backup Schedule.

    Fingerprint (Optional) Enter the fingerprint for the destination VM public key. The fingerprint detects any changes to the destination VM.

Back Up to Amazon S3 or Ceph

When you configure backups for Amazon S3 or Ceph, Tanzu SQL for VMs runs an Amazon S3 client that saves the backups to one of the following:

  • an Amazon S3 bucket
  • a Ceph storage cluster
  • another S3-compatible endpoint certified by VMware

For information about Amazon S3 buckets, see the Amazon documentation. For information about Ceph storage clusters, see the Ceph documentation.

To back up your database to Amazon S3 or Ceph:

Create a Custom Policy and Access Key

Tanzu SQL for VMs accesses your S3 bucket through a user account. VMware recommends that this account is only used by Tanzu SQL for VMs. You must apply a minimal policy that enables the user account upload backups to your S3 bucket.

Give the policy the permission to list and upload to buckets.

The procedure in this section assumes that you are using an Amazon S3 bucket. If you are using a Ceph or another S3-compatible bucket to create the policy and access key, follow the documentation for your storage solution. For more information about Ceph S3 bucket policies, see the Ceph documentation.

To create a policy and access key in AWS:

  1. Create a policy for your Tanzu SQL for VMs user account.

    In AWS, create a new custom policy by following this procedure in the AWS documentation.

    Paste in the following permissions:

    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Sid": "MySQLBackupPolicy",
          "Effect": "Allow",
          "Action": [
            "s3:ListBucket",
            "s3:ListBucketMultipartUploads",
            "s3:ListMultipartUploadParts",
            "s3:PutObject"
          ],
          "Resource": [
            "arn:aws:s3:::MY_BUCKET_NAME/*",
            "arn:aws:s3:::MY_BUCKET_NAME"
          ]
        }
      ]
    }
    
  2. Record the Access Key ID and Secret Access Key user credentials for a new user account by following this procedure in the AWS documentation. Ensure you select Programmatic access and Attach existing policies to user directly. You must attach the policy you created in the previous step.

Configure Backups in Ops Manager

Use Ops Manager to connect Tanzu SQL for VMs to your S3 account.

Prerequisite: Before beginning this procedure, you must have an S3 bucket in which to store the backups.

  1. In Ops Manager, open the Tanzu SQL for VMs tile Backups pane.

  2. Select Ceph or Amazon S3.

    Backup configuration pane shows Ceph or Amazon S3 selected and the fields
in the pane are described in the table below.

  3. Configure the fields as follows:

    Field Instructions
    Access Key ID and Secret Access Key Enter the S3 Access Key ID and Secret Access Key that you created in Create a Custom Policy and Access Key above.
    Endpoint URL Enter the S3-compatible endpoint URL for uploading backups.
    The URL must start with http:// or https://.
    The default is https://s3.amazonaws.com.
    If you are using a public S3 endpoint, see the S3 Endpoint procedure in Step 3: Director Config Page.
    Region Enter the region where your bucket is located.
    Bucket Name Enter the name of your bucket.
    Do not include an s3:// prefix, a trailing /, or underscores. VMware recommends using the naming convention DEPLOYMENT-backups. For example, sandbox-backups.
    Force path style access to bucket Do not select this if you use Amazon S3.
    Select this if you use another S3-compatible endpoint such as Minio that requires path-style URLs.

    This checkbox is not available in Tanzu SQL for VMs v2.9.0.
    Cron Schedule Enter a cron expression using standard syntax. The cron expression sets the schedule for taking backups for each service instance. This overrides the default schedule for your service instance. Test your cron expression using a website such as Crontab Guru.

    Note: Developers can override the default for their service instance. For more information, see Backup Schedule.

Back Up to GCS

When you configure backups for a Google Cloud Storage (GCS) bucket, Tanzu SQL for VMs runs a GCS SDK that saves backups to a GCS bucket.

For information about GCS buckets, see the GCS documentation.

To back up your database to Google Cloud Storage (GCS):

Create a Service Account and Private Key

Tanzu SQL for VMs accesses your GCS bucket through a service account. VMware recommends that this account is only used by Tanzu SQL for VMs. You must apply a minimal policy that enables the service account to upload backups to your GCS bucket.

The service account needs the following permissions:

  • List and upload to buckets
  • (Optional) Create buckets if they do not already exist

To create a service account and private key in GCS:

  1. Create a new service account by following this procedure in the GCS documentation.
    When you create the service account:
    1. Enter a unique name for the service account name.
    2. Add the Storage Admin role.
    3. Create and download a private key JSON file.

Configure Backups in Ops Manager

Use Ops Manager to connect Tanzu SQL for VMs to your GCS account.

  1. In Ops Manager, open the Tanzu SQL for VMs tile Backups pane.

  2. Select GCS.

    Backup configuration pane shows GCS radio button selected and the fields
 in the pane are described in the table below.

  3. Configure the fields as follows:

    Field Instructions
    Project ID Enter the Project ID for the Google Cloud project that you are using.
    Bucket name Enter the bucket name that Tanzu SQL for VMs uploads backups to.
    Service Account JSON Enter the contents of the service account JSON file that you downloaded when creating a service account in Create a Service Account and Private Key above.
    Cron Schedule Enter a cron expression using standard syntax. The cron expression sets the schedule for taking backups for each service instance. This overrides the default schedule for your service instance. Test your cron expression using a website such as Crontab Guru.

    Note: Developers can override the default for their service instance. For more information, see Backup Schedule.

Back Up to Azure Storage

When you configure backups for Azure Storage, Tanzu SQL for VMs runs an Azure SDK that saves backups to an Azure storage account.

For information about Azure Storage, see the Azure documentation.

To back up your database to Azure Storage:

Create a Storage Account and Access Key

Tanzu SQL for VMs accesses your Azure Storage account through a storage access key. VMware recommends that this account is only used by Tanzu SQL for VMs. You must apply a minimal policy that enables the storage account upload backups to your Azure Storage.

The storage account needs the following permissions:

  • List and upload to buckets
  • (Optional) Create buckets if they do not already exist

To create a storage account and access key:

  1. Create a new storage account by following this procedure in the Azure documentation.

  2. View your access key by following this procedure in the Azure documentation

Configure Backups in Ops Manager

To back up your database to your Azure Storage account:

  1. In Ops Manager, open the Tanzu SQL for VMs tile Backups pane.

  2. Select Azure.

    Backup configuration pane shows Azure radio button selected and the fields
 in the pane are described in the table below.

  3. Configure the fields as follows:

    Field Instructions
    Account Enter the Azure Storage account name that you created in Create a Storage Account and Access Key above.
    Azure Storage Access Key Enter one of the storage access keys that you viewed in Create a Storage Account and Access Key above.
    Container Name Enter the container name that Tanzu SQL for VMs uploads backups to.
    Blob Store Base URL To use an on-premise blob storage, enter the hostname of the blob storage. By default, backups are sent to the public Azure blob storage.
    Cron Schedule Enter a cron expression using standard syntax. The cron expression sets the schedule for taking backups for each service instance. This overrides the default schedule for your service instance. Test your cron expression using a website such as Crontab Guru.

    Note: Developers can override the default for their service instance. For more information, see Backup Schedule.