MySQL Kubernetes backup to Minio

December 22, 2022December 22, 2022| CiberNiteCiberNite|
Categories:

Do you have MySQL running on Kubernetes and would like to dump a database (or all of them). I, for one do, call me old school or paranoid, but there are times when backing up disk or longhorn backups just don’t do it for me. There are times you may need to restore a database to another server for testing or preparing for a migration.

There are many ways to achieve what i was looking for, I chose to create my own docker image which includes:

  • mc (minio-client)
  • curl
  • rsync
  • mysql-client

This will be deployed to Kubernetes as a cron job and dump the databases to a Minio bucket. Now this is far from complete, but it works, so follow along. Check back as I add more functionality

Dockerfile

FROM alpine
 
# Database User
ENV DB_USER=
# Database Password
ENV DB_PASS=
# Database name or blank for all databases
ENV DB_NAME=
# Database Server Name/Service for k3s
ENV DB_HOST=
# true for all databases
ENV ALL_DATABASES=
# Database name to ignore or blank
ENV IGNORE_DATABASE=
# Minio or local S3 URL
ENV S3_URL=
# Minio/S3 Access Key/User
ENV ACCESS_KEY=
# Minio/S3 Secret Key/Password
ENV SECRET_KEY=
# Minion/S3 Bucket
## Minio does not use region therefore it is not a Parameter to expoect
 
ENV BUCKET=
 
 
COPY backup.sh /opt/backup.sh
 
RUN apk update 
RUN apk upgrade 
RUN apk add curl
RUN apk add rsync
RUN apk add mysql-client
RUN mkdir /run/mysqld
RUN curl  https://dl.minio.io/client/mc/release/linux-amd64/mc --create-dirs -o /opt/mc
 
RUN chmod +x /opt/mc
RUN chmod +x /opt/backup.sh
CMD ["/opt/backup.sh"]

So here we are:

  • Setting up the environment variables
  • nCopying our backup.sh file, which will we see shortly
  • Installing the needed apps (curl, rsync, mysql-client, mc)
  • Setting permissions
  • Executing backup.sh

backup.sh

#!/bin/sh
 
if [[ ${DB_USER} == "" ]]; then
    echo "Missing DB_USER env variable"
    exit 1
fi
if [[ ${DB_PASS} == "" ]]; then
    echo "Missing DB_PASS env variable"
    exit 1
fi
if [[ ${DB_HOST} == "" ]]; then
    echo "Missing DB_HOST env variable"
    exit 1
fi
if [[ ${S3_URL} == "" ]]; then
    echo "Missing S3_URL env variable"
    exit 1
fi
if [[ ${ACCESS_KEY} == "" ]]; then
    echo "Missing ACCESS_KEY env variable"
    exit 1
fi
if [[ ${SECRET_KEY} == "" ]]; then
    echo "Missing SECRET_KEY env variable"
    exit 1
fi
if [[ ${BUCKET} == "" ]]; then
    echo "Missing BUCKET env variable"
    exit 1
fi
 
/opt/mc alias set backup-mysql $S3_URL $ACCESS_KEY $SECRET_KEY 
 
databases=`mysql --user="${DB_USER}" --password="${DB_PASS}" --host="${DB_HOST}" -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] && [[ "$db" != "$IGNORE_DATABASE" ]]; then
        echo "Removing backup file: $db"
        /opt/mc rm backup-mysql/"${BUCKET}/${db}".sql
        echo "Dumping database: $db"
        mysqldump --user="${DB_USER}" --password="${DB_PASS}" --host="${DB_HOST}" --databases $db | /opt/mc pipe backup-mysql/"${BUCKET}/${db}".sql
 
    fi
done
 
/opt/mc alias rm backup-mysql  
echo "Backup Complete..."

Based on script created by camilb’s (github.com/camilb)

So this is where part of the magic happens (remember, this is a start, adding on), true this may not be feasible for really large dataset, however, in those case, something a little more robust would be in order.

Build the image

Both the dockerfile and backup.sh file need to be in the same folder. Now we build the image and push it to a repo.

docker build -t your_dockerhub_username/image_name:tag .
docker push your_dockerhub_username/image_name:optional_tag

cron.yaml

---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-backup
spec:
  schedule: "0 0 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: mysql-backup
            image: <whatever you decide your image name and repo are goes here>
            env:
              - name: DB_USER
                value: <mysql user or root>
              - name: DB_PASS
                value: <passworD>
              - name: DB_HOST
                value: <mysql host or kubernetes service>
              - name: ALL_DATABASES
                value: "true"
              - name: IGNORE_DATABASE
                value: <database to ignore: ex: mysql>
              - name: S3_URL
                value: https://<your minio or s3 server>:<port>
              - name: ACCESS_KEY
                value: <minio or s3 access key>
              - name: SECRET_KEY
                value: <minio or s3 secret>
              - name: BUCKET
                value: <bucket>
          restartPolicy: OnFailure

Deploy the Job

kubectl apply -f cron.yaml

That’s pretty much it so far, add in your values and deploy to kubernetes, oh and set the cron as you like, it is set to midnight in the example.

CiberNite

Leave a Reply