MariaDB backup with Kubernetes CronJob

README.md

Kubernetes CronJob to backup, compress (with gzip --rsyncable), and finally use restic to backup your DBs to an S3 endpoint.

more details in my blog post

Advantages:

• using mariadb:latest Docker image, to ensure I don't use an outdated mariadb-dump binary
• backing up each database in a separate file, to make for easier restore
• compressing the backups with gzip and the --rsyncable option (details here), which makes gzip "regularly reset his compression algorithm to what it was at the beginning of the file", so that changes to a portion of the file do not alter the whole compressed output, which permits to make incremental backups.
• using restic to store the backups on an S3 endpoint (Cloudflare R2, with a generous free tier!), which makes for simple management and rotation, as well as for simple restores.

The CronJob can be used as is, and the secrets obviously need some adaptations !

cronjob.yaml

---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: mariadb-restic-backup
spec:
  schedule: "*/15 * * * *"
  jobTemplate:
    spec:
      backoffLimit: 0
      template:
        spec:
          initContainers:
            - name: mariadb-dump
              image: mariadb:latest
              env:
                - name: USER
                  value: backup
                - name: PASSWORD
                  valueFrom:
                    secretKeyRef:
                      name: mariadb-backup-credentials
                      key: database_password
                - name: DB_HOST
                  value: mariadb.appl-mariadb.svc.cluster.local
                - name: DB_PORT
                  value: "3306"
              workingDir: "/work"
              command: ["/bin/bash", "-c"]
              args:
                - |
                  ARGS=(--host="$DB_HOST" --port="$DB_PORT" --user="$USER" --password="$PASSWORD")

                  databases=$(mariadb "${ARGS[@]}" \
                      --execute 'show databases' --skip-column-names --batch | \
                      grep -vE 'mysql|information_schema|performance_schema|sys')

                  for db in  $databases; do
                      echo "$db"
                      mariadb-dump "${ARGS[@]}" "$db" | \
                      gzip --rsyncable > ./"$db".sql.gz
                  done
              volumeMounts:
                - mountPath: /work
                  name: work
          containers:
            - name: restic-backup
              image: restic/restic
              envFrom:
                - secretRef:
                    name: mariadb-backup-credentials
                    optional: false
              command: ["/bin/sh", "-c"]
              args:
                - |
                  restic backup /work && \
                  restic unlock && \
                  restic forget --group-by paths --keep-hourly 24 --keep-daily 7 --keep-monthly 24 && \
                  restic prune
              resources:
                limits:
                  cpu: 1000m
                  memory: 512Mi
              volumeMounts:
                - mountPath: /work
                  name: work
          restartPolicy: OnFailure
          volumes:
            - name: work
              emptyDir:
                sizeLimit: 512Mi

secrets.yaml

---
apiVersion: v1
kind: Secret
metadata:
    name: mariadb-backup-credentials
type: Opaque
stringData:
    database_password: change.me # maridb backup user's password
    
    RESTIC_PASSWORD_COMMAND: echo your-repo-password
    RESTIC_REPOSITORY: s3:some-repo-id.eu.r2.cloudflarestorage.com/mariadb-backup
    AWS_ACCESS_KEY_ID: access_key
    AWS_SECRET_ACCESS_KEY: secret_key