7.1 Handling MySQL Backups

There are three main topics related to MySQL backups:

• Backup profile: describes the general backup structure that includes storage, schedule, and MySQL Shell dump related options. Defining profiles is optional, and profiles are separated by name.

• Backup request: requesting a backup initiates a new object that creates a new pod to perform the backup.

• Backup schedule: defined as a cron expression for regular backups, or with no schedule when performing one-off backups.

See also Chapter 8, MySQL Operator Custom Resource Properties for a list of all MySQLBackup resource options.

Backup Profiles with backupProfiles

Backup profiles are defined and reused for regular backups and one-off backups using the backupProfiles specification object. A profile is defined and called from within the InnoDB Cluster specification object, or values can be defined from within the individual backup requests without a profile.

How a Backup is Created

MySQL Operator for Kubernetes supports the dumpInstance() command using MySQL Shell by defining the associated dumpInstance specification object that contains the dumpOptions and storage specification objects:

• The optional dumpOptions value is a dictionary of key-value pairs passed directly to MySQL Shell's DumpInstance() function. See Instance Dump Utility, Schema Dump Utility, and Table Dump Utility for a list of relevant options.

  MySQL Operator for Kubernetes adds definitions by default, such as defining threads based on what the system claims as its CPU count; but these values can be overridden.

• The storage configuration specification offers two options as of MySQL Operator for Kubernetes 8.0.29: persistentVolumeClaim or ociObjectStorage (OCI refers to Oracle Cloud Infrastructure).

  Note

  Limitations: Restore capability is not available for persistentVolumeClaim as of MySQL Operator for Kubernetes 8.0.29, and ociObjectStorage use is specific to the Oracle Cloud Infrastructure (OCI).

• The backupSchedules schedule utilizes the Kubernetes CronJob controller for regular backups.

A PersistentVolumeClaim Scheduled Backup Example

This example uses PersistentVolumeClaim (PVC), sets a daily backup schedule, and defines a backup profile named "myfancyprofile" in the backupProfiles object.

Note

This example defines a single backupProfile and schedule but could define multiple profiles and schedules depending need. For example, a volatile table may have hourly backups in addition to the full nightly backup.

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: mycluster
spec:
  instances: 3
  router:
    instances: 1
  secretName: mypwds
  tlsUseSelfSigned: true
  backupProfiles:       
    - name: myfancyprofile  # Embedded backup profile
      dumpInstance:         # MySQL Shell Dump
        dumpOptions:
          excludeTables:
          - world.country   # Example to exclude one table 
        storage:
          persistentVolumeClaim:
            claimName: myexample-pvc # store to this pre-existing PVC
  backupSchedules:
    - name: mygreatschedule
      schedule: "0 0 * * *" # Daily, at midnight
      backupProfileName:  myfancyprofile # reference the desired backupProfiles's name 
      enabled: true # backup schedules can be temporarily disabled

This example requires a PersistentVolumeClaim definition named "myexample-pvc"; see the official Kubernetes Persistent Volumes documentation for PersistentVolumeClaim specifics. A simple example:

apiVersion: v1
kind: PersistentVolume
metadata:
  name: myexample-pv
  labels:
    type: local
spec:
  storageClassName: manual
  capacity:
    storage: 2Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /tmp
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: myexample-pvc
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 2Gi

The example "mycluster" InnoDB Cluster definition uses a secret named "mypwds" for its root user, for example:

$> kubectl create secret generic mypwds \
        --from-literal=rootUser=root \
        --from-literal=rootHost=% \
        --from-literal=rootPassword="sakila"

After creating the example InnoDB Cluster, you may want to execute a one-off backup using an existing profile, such as:

apiVersion: mysql.oracle.com/v2
kind: MySQLBackup
metadata:
    name: a-cool-one-off-backup
spec:
  clusterName: mycluster
  backupProfileName: myfancyprofile

Executing this creates a pod with a name similar to a-cool-one-off-backup-20220330-215635-t6thv that executes the backup, and it remains in a Completed state after the backup operation.

Using OciObjectStorage

Using the same example but for Oracle Cloud Infrastructure (OCI) instead of a PVC, modify dumpInstance.storage from PrivateVolumeClaim to an ociObjectStorage object similar to:

    dumpInstance:
      storage:
        ociObjectStorage:
          prefix: someprefix         # a prefix (directory) used for ObjectStorage
          bucketName: bucket         # the ObjectStorage bucket
          credentials: backup-apikey # a secret with credentials ...

The backup-apikey secret used in this OCI example looks similar to:

    apiVersion: v1
    kind: Secret
    type: Opaque
    metadata:
      name: backup-apikey
    stringData:
      fingerprint: 06:e9:e1:c6:e5:df:81:f3:......
      passphrase: ....
      privatekey: |
        -----BEGIN RSA PRIVATE KEY-----
        MIIEogIBAAKCAQEAwmQ1JGOGUBNwyJuq4msGpBfK24toKrWaqAkbZ1Z/XLOFLvEE
       ....
      region: us-ashburn-1..
      tenancy: ocid1.tenancy...
      user: ocid1.user.....

An example method to create the Secret; values are found in the configuration file downloaded from OCI, which is used with the OCI command-line tool.

$> kubectl create secret generic <secret_name> \
        --from-literal=user=<userid> \
        --from-literal=fingerprint=<fingerprint> \
        --from-literal=tenancy=<tenancy> \
        --from-literal=region=<region> \
        --from-literal=passphrase=<passphrase> \
        --from-file=privatekey=<path_to_api_key.pem>

Using profiles (backupProfileName) is optional, so instead it may look like the following with the same settings. This example restores to a new InnoDB Cluster from ociObjectStorage:

    apiVersion: mysql.oracle.com/v2
    kind: InnoDBCluster
    metadata:
      name: newcluster
    spec:
      instances: 3
      router:
        instances: 1
      secretName: newpwds
      tlsUseSelfSigned: true
      baseServerId: 2000
      initDB:
        dump:
          name: some-name
          storage:
            ociObjectStorage:
              prefix: someprefix
              bucketName: bucket
              credentials: restore-apikey

The secret (restore-apikey) could be the same as the backup example (backup-apikey) but may be a different user with different permissions, such as no write permissions to the OS.

Cloning

Data can be initialized using a backup or by cloning an existing and running MySQL instance using iniDB and its donorURL option:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: copycluster
spec:
  instances: 1
  secretName: pwds
  tlsUseSelfSigned: true
  initDB:
    clone:
      donorUrl: [e-mail]:3306
      secretKeyRef:
        name: donorpwds

The donorpwds secret contains a single field named rootPassword, so for example you could reuse the main secretName used when creating the original cluster (named mypwds in the example). This utilizes MySQL's cloning plugin, so standard limitations apply (such as requiring the same MySQL versions). Cloning can theoretically also be used for creating backups.