JIYIK CN >

Oracle's MySQL Operator for Kubernetes is a convenient way to automatically configure a MySQL database within a cluster. One of the key features of the Operator is the integrated automatic backup support for increased resiliency. The backups periodically copy the database to external storage.

This article walks you through setting up backups to an Amazon S3-compatible object storage service. You will also learn how to store backups in Oracle Cloud Infrastructure (OCI) storage or in a local persistent volume within the cluster.

Preparing the database cluster

Install MySQL Operator in your Kubernetes cluster and create a simple database instance for testing purposes. Copy the following YAML and save it to mysql.yaml :

apiVersion: v1
kind: Secret
metadata:
  name: mysql-root-user
stringData:
  rootHost: "%"
  rootUser: "root"
  rootPassword: "P@$$w0rd"
 
---

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1

Apply the configuration using Kubectl:

$ kubectl apply -f mysql.yaml

Wait a few minutes while the MySQL operator configures the Pods. get podsCheck the progress using the Kubectl command. We should see four Pods running: one MySQL router instance and three MySQL server replicas.

$ kubectl get pods
NAME                                    READY   STATUS    RESTARTS   AGE
mysql-cluster-0                         2/2     Running   0          2m
mysql-cluster-1                         2/2     Running   0          2m
mysql-cluster-2                         2/2     Running   0          2m
mysql-cluster-router-6b68f9b5cb-wbqm5   1/1     Running   0          2m

Defining a backup plan

The MySQL operator requires two components to successfully create a backup:

• A backup schedule that defines when backups run.
• A backup configuration file that configures the storage location and export options for MySQL.

Schedules and profiles are created independently of each other. This allows us to run multiple backups on different schedules using the same profile.

Each plan and profile is associated with a specific database cluster. They are created as InnoDBClusternested resources within a MySQL object. Each database we create using the MySQL operator requires its own backup configuration.

The backup schedule spec.backupSchedulesis defined by the database's schedule field. Each project requires a schedule field that specifies when to run the backup using a cron expression. Here is an example that starts a backup every hour:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1
   backupSchedules:
    - name: hourly
      enabled: true
      schedule: "0 * * * *"
      backupProfileName: hourly-backup

backupProfileNameThe field references the backup configuration file to use. We will create it in the next step.

Creating a backup configuration file

Profiles spec.backupProfilesare defined in the field. Each profile should have a name and a dumpInstanceproperty that configures the backup operation.

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1
  backupSchedules:
    - name: hourly
      enabled: true
      schedule: "0 * * * *"
      backupProfileName: hourly-backup
  backupProfiles:
    - name: hourly-backup
      dumpInstance:
        storage:
          # ...

The backup storage is dumpInstance.storageconfigured on a per-profile basis in the field. The properties we need to provide depend on the type of storage we are using.

S3 Storage

MySQL Operator can upload our backups directly to an S3-compatible object storage provider. To use this method, we must create a Kubernetessecret that contains an AWS CLI configuration file with our credentials.

Add the following to s3-secret.yaml :

apiVersion: v1
kind: Secret
metadata:
  name: s3-secret
stringData:
  credentials: |
    [default]
    aws_access_key_id = YOUR_S3_ACCESS_KEY
    aws_secret_access_key = YOUR_S3_SECRET_KEY

Replace the S3 access key with our own, and then create the secret using Kubectl:

$ kubectl apply -f s3-secret.yaml
secret/s3-secret created

Next add the following fields to the storage.s3 section of the backup configuration file:

• bucketName – The name of the S3 bucket to upload the backup to.
• prefix – Set this to apply a prefix to the files we upload, for example /my-app/mysql . This prefix allows us to create a folder tree within the bucket.
• endpoint - When we are using a third-party S3 compatible storage, set this to the URL of our service provider. If we are using Amazon S3, this field can be omitted.
• config – The name of the secret that contains our credentials file.
• profile – The name of the profile to use in the credentials file. This is set as the default in the example above.

Here is a complete example:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1
  backupSchedules:
    - name: hourly
      enabled: true
      schedule: "0 * * * *"
      backupProfileName: hourly-backup
  backupProfiles:
    - name: hourly-backup
      dumpInstance:
        storage:
          s3:
            bucketName: backups
            prefix: /mysql
            config: s3-secret
            profile: default

Applying this manifest will activate hourly database backups to our S3 account.

OCI Storage

The operator supports Oracle Cloud Infrastructure (OCI) Object Storage as an alternative to S3. It is configured in a similar way. First create a key for your OCI credentials

apiVersion: v1
kind: Secret
metadata:
  name: oci-secret
stringData:
  fingerprint: YOUR_OCI_FINGERPRINT
  passphrase: YOUR_OCI_PASSPHRASE
  privatekey: YOUR_OCI_RSA_PRIVATE_KEY
  region: us-ashburn-1
  tenancy: YOUR_OCI_TENANCY
  user: YOUR_OCI_USER

Next, use storage.ociObjectStoragethe stanza to configure the backup configuration file:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1
  backupSchedules:
    - name: hourly
      enabled: true
      schedule: "0 * * * *"
      backupProfileName: hourly-backup
  backupProfiles:
    - name: hourly-backup
      dumpInstance:
        storage:
          ociObjectStorage:
            bucketName: backups
            prefix: /mysql
            credentials: oci-secret

Modify the bucketNameand prefixfields to set the upload location in your OCI account. The Credentials field must reference the key that contains your OCI credentials.

Kubernetes Volume Storage

Local persistent volumes are a third storage option. This is less reliable, as your backup data will still reside on Kubernetesthe cluster. However, it is useful for one-time backups and testing purposes.

First create a persistent volume and accompanying claims:

apiVersion: v1
kind: PersistentVolume
metadata:
  name: backup-pv
spec:
  storageClassName: standard
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /tmp
 
---

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: backup-pvc
spec:
  storageClassName: standard
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi

This example manifest is not suitable for production use. You should choose the appropriate storage class and volume mount mode for your Kubernetesdistribution.

storage.persistentVolumeClaimNext configure your backup configuration file to use your own persistent volume by adding the field:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1
  backupSchedules:
    - name: hourly
      enabled: true
      schedule: "0 * * * *"
      backupProfileName: hourly-backup
  backupProfiles:
    - name: hourly-backup
      dumpInstance:
        storage:
          persistentVolumeClaim:
            claimName: backup-pvc

The persistent volume claim created earlier claimNameis referenced by the field. MySQL Operator will now store the backup data in this volume.

Setting backup options

The backup is created using the MySQL Shell's dumpInstanceutilities. This by default exports a full dump of the server. The format writes both structure and chunked data files for each table. zstdCompressed output is used.

dumpOptionsWe can pass options via the MySQL operator backup configuration file field to dumpInstance:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster
spec:
  # ...
  backupProfiles:
    - name: hourly-backup
      dumpInstance:
        dumpOptions:
          chunking: false
          compression: gzip
        storage:
          # ...

This example disables chunked output, creates one data file per table, and switches to gzipcompression instead zstd. A full reference of the available options can be found in the MySQL documentation.

Restoring a backup

The MySQL Operator can dumpInstanceinitialize a new database cluster using files previously created from . This allows us to restore a backup directly into a Kubernetes cluster. It is useful in recovery situations or when migrating an existing database to Kubernetes.

Database initialization is controlled by the field InnoDBClusteron the object spec.initDB. In this section, use the object to reference the backup location used previously. The format matches the dump.storageequivalent field in the backup configuration file object .dumpInstance.storage

apiVersion: v1
kind: Secret
metadata:
  name: s3-secret
stringData:
  credentials: |
    [default]
    aws_access_key_id = YOUR_S3_ACCESS_KEY
    aws_secret_access_key = YOUR_S3_SECRET_KEY

---

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mysql-cluster-recovered
spec:
  secretName: mysql-root-user
  instances: 3
  tlsUseSelfSigned: true
  router:
    instances: 1
  initDB:
    dump:
      storage:
        s3:
          bucketName: backups
          prefix: /mysql/mysql20221031220000
          config: s3-secret
          profile: default

Applying this YAML file will create a new database cluster dumpInstanceinitialized with the output from the specified S3 bucket. The prefix field must contain the full path to the dump file in the bucket. The backups created by the Operator are automatically stored in timestamped folders; we need to indicate which one to restore by setting the prefix. If you are restoring from a persistent volume, use the path field instead of the prefix.

Summarize

Oracle's MySQL Operator automates MySQL database management in a Kubernetes cluster. In this article, you learned how to configure the Operator's backup system to store full database dumps in a persistent volume or object storage bucket.

Scaling MySQL horizontally with Kubernetes increases resiliency, but external backups are still critical in case your cluster is damaged or your data is accidentally deleted. If needed, MySQL Operator can restore a new database instance from your backup, simplifying the disaster recovery process.