This topic describes how to back up and restore VMware SQL with MySQL for Kubernetes.
VMware MySQL Operator allows you to generate on-demand backups, configure schedules for automated backups, and restore backups to new MySQL instances.
For uploading and retrieving backup artifacts, VMware MySQL Operator currently supports S3-compatible storage and Azure Blob Storage.
For backing up and restoring, VMware MySQL Operator uses four Custom Resource Definitions (CRDs):
MySQLBackup: References a MySQL backup artifact that exists in an external blobstore such as S3 or Minio. A new MySQLBackup resource is created every time an on-demand or scheduled backup is generated.
MySQLBackupLocation: References an external blobstore and credentials necessary to access the blobstore.
MySQLBackupSchedule: Represents a CronJob schedule on which to perform backups.
MySQLRestore: References an instance of a restore that was performed. A new MySQLRestore resource is created every time a restore is performed.
For detailed information about the CRDs, see Controllers and Custom Resource Definitions (CRDs) in Architecture.
Note: The procedures in this topic require the VMware SQL with MySQL for Kubernetes deployment templates. If you have not yet downloaded the deployment templates, see Download the Deployment Templates in Creating and Deleting MySQL Instances.
VMware MySQL Operator syncs MySQLBackup resources in a Kubernetes cluster with the contents of the external blobstore. The external blobstore is treated as the source of truth. This means that, if a MySQLBackup
resource is deleted on the Kubernetes cluster, but the associated backup artifact still exists in the external blobstore, VMware MySQL Operator re-creates the MySQLBackup
resource to match the contents of the external blobstore.
Performing backups for VMware MySQL Operator requires creating a MySQLBackupLocation resource that references an external blobstore. Both on-demand backups and scheduled backups use the MySQLBackupLocation to upload backup artifacts to the external blobstore.
Before starting the procedures for backing up a VMware MySQL Operator instance, ensure that you know the configuration details of your external blobstore and how often you want to perform scheduled backups.
The MySQLBackupLocation Resource is used to configure the namespace with the location of the blobstore, and the credentials to access it. If your storage endpoint uses custom TLS certificates, provide your CA bundle in the MySQLBackupLocation Resource.
For details on the backup location CR and its properties, refer to Properties for the MySQLBackupLocation Resource, and Properties for the Secret.
To create a MySQLBackupLocation resource:
Find the backuplocation.yaml
deployment template that you downloaded in the TGZ file from Broadcom Support. For how to download deployment templates, see Download the Deployment Templates in Creating and Deleting MySQL Instances.
Create a copy of the backuplocation.yaml
file and give it a unique name.
For example:
cp ~/Downloads/mysql-for-kubernetes-1.10.0/samples/backuplocation.yaml testbackuplocation.yaml
Edit the file with the configuration details of your external blobstore.
Create the MySQLBackupLocation resource in the same namespace as the MySQL instances that you want to back up by running:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
DEVELOPMENT-NAMESPACE
is the namespace for the MySQL instance.FILENAME
is the name of the configuration file you created in Step 2 above.For example:
kubectl apply -f testbackuplocation.yaml -n my-namespace
mysqlbackuplocation.with.sql.tanzu.vmware.com/backuplocation-sample created
secret/backuplocation-sample-creds configured
Verify that the MySQLBackupLocation has been created by running:
kubectl get mysqlbackuplocation backuplocation-sample \
-o jsonpath={.spec} -n DEVELOPMENT-NAMESPACE
For example:
kubectl get mysqlbackuplocation backuplocation-sample -o jsonpath={.spec} -n my-namespace
{
"storage": {
"s3": {
"bucket": "bucket-sample",
"forcePathStyle": false,
"region": "us-west-1",
"secret": {
"name": "backuplocation-sample-creds"
}
}
}
}
To set a schedule for automatic backups, create a MySQLBackupSchedule resource:
Find the backupschedule.yaml
deployment template that you downloaded in the TGZ file from Broadcom Support. For how to download deployment templates, see Download the Deployment Templates in Creating and Deleting MySQL Instances.
Create a copy of the backupschedule.yaml
file and give it a unique name.
For example:
cp ~/Downloads/mysql-for-kubernetes-1.10.0/samples/backupschedule.yaml testbackupschedule.yaml
Edit the file with the name of the MySQLBackupLocation resource that you created in Create a MySQLBackupLocation Resource and the name of the MySQL instance you want scheduled backups of. For an explanation of the properties that you can set in this file, see Properties for the MySQLBackupSchedule Resource.
Create the MySQLBackupSchedule resource in the same namespace as the MySQLBackupLocation and MySQL instance that you referenced in the MySQLBackupSchedule YAML file.
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
Where FILENAME
is the name of the configuration file you created in Step 2 above.
For example:
kubectl apply -f testbackupschedule.yaml -n my-namespace
mysqlbackupschedule.with.sql.tanzu.vmware.com/backupschedule-sample created
Verify that the MySQLBackupSchedule has been created by running:
kubectl get mysqlbackupschedule mysqlbackupschedule-sample -o jsonpath={.spec} -n DEVELOPMENT-NAMESPACE
For example:
kubectl get mysqlbackupschedule mysqlbackupschedule-sample -o jsonpath={.spec} -n my-namespace
{
"backupTemplate": {
"spec": {
"instance": {
"name": "demo-db"
},
"location": {
"name": "demo-backuplocation"
}
}
},
"schedule": "@daily"
}
If you correctly configured both a MySQLBackupLocation
resource and MySQLBackupSchedule
resource for an existing MySQL instance, you see backups being generated and uploaded to the external blobstore.
Recovery Point Objective (RPO) refers to how much data loss your application can tolerate. The smaller the RPO, the less data loss involved during an outage.
To minimize RPO, restore transactions after a full backup. To accomplish this, configure MySQLBackupSchedule to save binary logs of the MySQL instance as full backups in the same blob store where the backups of the MySQL instance are stored.
To enable binary log collection, set the spec.binaryLogCollection
fields (spec.binaryLogCollection.enabled
, spec.binaryLogCollection.flushInterval
, and spec.binaryLogCollection.resources
) in the MySQLBackupSchedule. For more information, see MySQLBackupSchedule Property Reference.
For example, you can configure MySQLBackupSchedule
to enable binary log collection every five minutes as follows:
binaryLogCollection:
enabled: true
flushInterval: 5m
To improve Recovery Time Objective (RTO), configure the flush interval to be shorter so that binary logs are flushed and uploaded to the blobstore more frequently. To review all the options, see the MySQLBackupSchedule Property Reference.
Note: If binary logs are purged or rotated before they can be collected, point-in-time recovery does not restore all the desired data successfully .
The binary logs are collected and uploaded to the blobstore in a separate Kubernetes deployment named <SCHEDULE>-binlog-collector
where SCHEDULE
is the name of the MySQLBackupSchedule
.
To configure resources for the container that is collecting and uploading the binary logs, set the resource requirements with spec.binaryLogCollection.resources
.
MySQLBackup resources that are automatically generated as a result of a MySQLBackupSchedule are named SCHEDULE-NAME-TIMESTAMP
.
By default, VMware MySQL Operator stores backup artifacts under the subfolder structure mysql-<instance-name>-<instance-uuid> / backups / yyyy / mm / dd /
. You can configure a custom path for backups so that backup artifacts are stored under the subfolder structure CUSTOM-PATH / mysql-<instance-name>-<instance-uuid> / backups / yyyy / mm / dd
.
Backup artifacts stored in the external blobstore are named DATETIME-RANDOM_STRING-backup.xb
.
For example, a user has an instance called sample-instance
with a UUID of bf5398d1-c0fb-4459-a024-559e9855a2cd
. If a MySQLBackupSchedule name is mysqlbackupschedule-sample
, the custom backup path is my-backups/
, and a backup was taken on Thursday, December 10, 2020 at 8:51:03 PM GMT (timestamp 1607633463
), then:
mysqlbackupschedule-sample-1607633463
20201210T205103-kzw54l-backup.xb
my-backups/mysql-sample-instance-bf5398d1-c0fb-4459-a024-559e9855a2cd/backups/2020/12/10/
.If you enable binary log collection, then binary logs are stored in a subfolder structure CUSTOM-PATH / mysql-<instance-name>-<instance-uuid> / binlogs
.
The binary logs are named as binlog_<timestamp>
, where timestamp
is the Unix time of the first transaction in the binary log. The Global Transaction Identifier (GTID) of the transactions associated with this binary log are stored in a blob named gtid_binlog_<timestamp>
.
For example, if you use the same sample-instance
, if the MySQL database has flushed logs, and if there is a binary log whose first transaction occurs with Unix timestamp 1607633463
, then: * The name of the binary log is binlog_1607633463
* The path to the binary log is my-backups/mysql-sample-instance-bf5398d1-c0fb-4459-a024-559e9855a2cd/binlogs/binlog_1607633463
. * The path to the GTID blob is my-backups/mysql-sample-instance-bf5398d1-c0fb-4459-a024-559e9855a2cd/binlogs/gtid_binlog_1607633463
.
In addition to scheduled backups, you can take individual backups whenever you want.
Prerequisite: A MySQLBackupLocation resource that represents the external blobstore to which you upload the generated backup artifact. To configure the MySQLBackupLocation resource, see Create a MySQLBackupLocation Resource above.
To take a backup:
Find the backup.yaml
deployment template that you downloaded in the TGZ file from Broadcom Support. For how to download deployment templates, see Download the Deployment Templates in Creating and Deleting MySQL Instances.
Create a copy of the backup.yaml
file and give it a unique name.
For example:
cp ~/Downloads/mysql-for-kubernetes-1.10.0/samples/backup.yaml testbackup.yaml
Edit the file. For an explanation of the properties that you can set for the MySQLBackup resource, see Properties for the MySQLBackup Resource.
Trigger the backup by creating the MySQLBackup resource in the same namespace as the instance by running:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
Where FILENAME
is the name of the configuration file you created in Step 2 above.
For example:
kubectl apply -f testbackup.yaml -n my-namespace
mysqlbackup.with.sql.tanzu.vmware.com/backup-sample created
Verify that a backup has been generated and track its progress by running:
kubectl get mysqlbackup backup-sample -n DEVELOPMENT-NAMESPACE
For example:
kubectl get mysqlbackup backup-sample -n my-namespace
NAME STATUS SOURCE INSTANCE TIME STARTED TIME COMPLETED
backup-sample Succeeded mysql-sample 2020-12-01T21:49:26Z 2020-12-01T21:49:30Z
For an explanation of what each column means, see List Existing MySQLBackup Resources below.
You might want to list existing MySQLBackup resources for various reasons, for example:
To see a list of existing MySQLBackup resources:
List existing MySQLBackup resources by running:
kubectl get mysqlbackup
For example:
kubectl get mysqlbackup
NAME STATUS SOURCE INSTANCE TIME STARTED TIME COMPLETED
backup-sample Failed mysql-sample
To understand the output, see the table below:
Column Name | Meaning |
---|---|
STATUS |
Represents the current status of the backup. Allowed values are:
|
SOURCE INSTANCE |
The MySQL instance the backup was taken from. |
TIME STARTED |
The time that the backup process started. |
TIME COMPLETED |
The time that the backup process finished. If the backup fails, this value is empty. |
VMware MySQL Operator allows users to set a retention policy for backups on the blobstore. Retention can be set in the MySQLBackupLocation resource. For example:
---
apiVersion: with.sql.tanzu.vmware.com/v1
kind: MySQLBackupLocation
metadata:
name: backuplocation-sample
spec:
# retentionPolicy:
# days: 30
# type: Days
storage:
...
In this example, the retention policy requires at least one backup that is 30 days or older to be retained. In other words, the retention policy ensures a backup is retained to restore up to 30 days of data. When an existing backup becomes 30 days old, it will not be immediately deleted. Rather, a new successful backup triggers the retention policy.
Consider there are backups taken on a weekly schedule. Eventually, there will be backups that are 35 days and 42 days old. At the time of a new successful backup, the 42 day old backup will be deleted and the 35 day old backup and all later backups are kept in order to allow a restore with up to 30 days worth of data.
Deleting a MySQLBackup resource in Kubernetes will not remove the backup from the blobstore. For each backup artifact in the blobstore referred to by a MySQLBackupLocation object, a corresponding MySQLBackup resource will be created if missing. This allows users in Kubernetes to restore any backup artifact in the blobstore using its MySQLBackup reference.
This section discusses three kinds of restores:
MySQLRestores always restores to a new MySQL instance to avoid overwriting any data on an existing MySQL instance. When the restore is triggered, it automatically creates the new MySQL instance.
VMware MySQL Operator does not allow you to restore a backup to an existing MySQL instance. Although you can perform this manually by copying the MySQL data from the backup artifact onto an existing MySQL instance, VMware strongly discourages you from doing this because you might overwrite existing data on the MySQL instance.
Before you restore from a backup, you must have:
To restore from a backup:
Find the restore.yaml
deployment template that you downloaded in the TGZ file from Broadcom Support. For how to download deployment templates, see Download the Deployment Templates in Creating and Deleting MySQL Instances.
Create a copy of the restore.yaml
file and give it a unique name.
For example:
cp ~/Downloads/mysql-for-kubernetes-1.10.0/samples/restore.yaml testrestore.yaml
Edit the file. For information about the properties that you can set for the MySQLRestore resource, see Property Reference for Backup and Restore.
To restore from an HA instance to a non-HA instance, edit the testrestore.yaml
and amend the spec.instanceTemplate.spec.highAvailability
field to false. For example:
---
apiVersion: with.sql.tanzu.vmware.com/v1
kind: MySQLRestore
metadata:
name: restore-sample
spec:
backup:
name: backup-sample
instanceTemplate:
metadata:
name: mysql-sample
spec:
storageSize: 1Gi
imagePullSecretName: tanzu-image-registry
highAvailability:
enabled: false
Trigger the restore by creating the MySQLRestore resource in the same namespace as the MySQLBackup and MySQLBackupLocation by running:
kubectl apply -f FILENAME -n DEVELOPMENT-NAMESPACE
Where FILENAME
is the name of the configuration file you created in Step 2 above.
For example:
kubectl apply -f testrestore.yaml -n my-namespace
mysqlrestore.with.sql.tanzu.vmware.com/restore-sample created
Verify that a restore has been triggered and track the progress of your restore by running:
kubectl get mysqlrestore restore-sample -n DEVELOPMENT-NAMESPACE
For example:
kubectl get mysqlrestore restore-sample -n my-namespace
NAME STATUS SOURCE BACKUP TARGET INSTANCE TIME STARTED TIME COMPLETED
restore-sample Succeeded backup-sample mysql-sample 2020-12-01T21:52:30Z 2020-12-01T21:53:09Z
To understand the output, see the table below:
Column Name | Meaning |
---|---|
STATUS |
Represents the current status of the restore process. Allowed values are:
|
SOURCE BACKUP |
The name of the backup being restored. |
TARGET INSTANCE |
The name of the new MySQL instance to be restored with the backup contents. |
TIME STARTED |
The time that the restore process started. |
TIME COMPLETED |
The time that the restore process finished. If the restore fails, this value is empty. |
If you want to restore a backup to a different namespace or a different Kubernetes cluster, create a MySQLBackupLocation
in the target namespace or Kubernetes cluster. Then, VMware MySQL Operator automatically creates MySQLBackup
resources for the backup artifacts in the external blobstore.
To restore to a different namespace or Kubernetes cluster, you create a BackupLocation in the target namespace:
Target the destination Kubernetes cluster or namespace.
Create a MySQLBackupLocation resource that contains the backup artifact to restore. For how to do this, see Create a MySQLBackupLocation Resource.
Confirm that the MySQLBackup artifact to restore is included in the list by running:
kubectl get mysqlbackup
For example:
kubectl get mysqlbackup
NAME STATUS SOURCE INSTANCE TIME STARTED TIME COMPLETED
sample-backup Succeeded mysql-sample 2020-12-01T21:49:26Z 2020-12-01T21:49:30Z
Trigger a restore by following steps in Restore from a Backup.
To restore a backup along with binary logs, you must first enable binary log collection. Review the documentation to enable binary log connection.
To speed up application recovery, you can reduce Recovery Time Objective (RTO). Thereby, you can restore the MySQL instance to a point in time first by using the full backup right before the time of recovery and recover the binary logs as well.
You can use the field spec.pitr.type
in a MySQLRestore object and restore backups to a point in time using binary logs in two ways: * Timestamp
: The recovery process restores the binary logs until the required timestamp is reached. * Latest
: The recovery process restores all the binary logs available in the blobstore.
There are two ways to provide the backup artifact: * By providing the name of the MySQLBackup object. * By providing the name and UID of the MySQL object and by allowing the operator to find the latest MySQLBackup object associated with the MySQL instance. If a recovery timestamp is provided, the operator looks for the latest MySQLBackup before the timestamp.
To review all the options, see the MySQLRestore Property Reference.
Some examples:
backup-sample
is of two days before and you wish to create a new MySQL instance with all the latest data from a source instance, then the you can create a MySQLRestore as follows:apiVersion: with.sql.tanzu.vmware.com/v1
kind: MySQLRestore
metadata:
name: restore-sample
spec:
backup:
name: backup-sample
instanceTemplate:
metadata:
name: new-mysql-sample
spec:
imagePullSecretName: tanzu-image-registry
pitr:
type: Latest
backup-sample
is of two days before and there is a specific transaction that is undesired, then you must identify the timestamp of the transaction and provide it in the MySQLRestore as follows:apiVersion: with.sql.tanzu.vmware.com/v1
kind: MySQLRestore
metadata:
name: restore-sample
spec:
backup:
name: backup-sample
instanceTemplate:
metadata:
name: new-mysql-sample
spec:
imagePullSecretName: tanzu-image-registry
pitr:
type: Timestamp
timestamp: "2023-05-25T19:22:15Z"
To avoid the work of finding the latest MySQLBackup before a specific timestamp, you can specify the identity of the source MySQL instance with spec.searchForBackup
to allow the operator to find the appropriate MySQLBackup as follows:
apiVersion: with.sql.tanzu.vmware.com/v1
kind: MySQLRestore
metadata:
name: restore-sample
spec:
searchForBackup:
instanceName: mysql-sample
instanceUid: 4e1677b5-9b9a-4b2a-bd90-9799441a145f
instanceTemplate:
metadata:
name: new-mysql-sample
spec:
imagePullSecretName: tanzu-image-registry
pitr:
type: Timestamp
timestamp: "2023-05-25T19:22:15Z"
Basic troubleshooting begins with reviewing the status for the resource and reading the messages associated with the resource events.
To troubleshoot problems with backup and restore:
Detect issues by monitoring the STATUS
column of any MySQL custom resource. If the status is Failed
or is stuck in Pending
, Scheduled
, or Running
, then one of the following might be the problem:
In this example, the kubectl get
command outputs a Failed
status:
kubectl get mysqlbackup backup-sample
NAME STATUS SOURCE INSTANCE TIME STARTED TIME COMPLETED
backup-sample Failed mysql-sample
Diagnose the issue by inspecting the Kubernetes events for the resource, for example:
kubectl get events --field-selector involvedObject.name=backup-sample
LAST SEEN TYPE REASON OBJECT MESSAGE
2m43s Warning Failed mysqlbackup/backup-sample Secret "backuplocation-sample-creds" not found
Read the message in the MESSAGE
column to understand why the failure occurred.
In the example above, the backup-sample
expected a Kubernetes secret called backuplocation-sample-creds
to exist. Fix this problem by creating the backuplocation-sample-creds
secret. The template for this secret is located in the backuplocation.yaml
template.