Shane Rainville
Posted on

Using Kubernetes CronJob to Backup MySQL on GKE

Overview

In this tutorial, you will learn how to protect your MySQL database in GKE by using Kubernetes CronJobs and Google Cloud Storage buckets.

CronJobs are scheduled events to perform tasks, and unlike Unix cronjobs, tasks are executed via a container.

You will build MySQL backup image based on Google Cloud SDK image, and use it to perform a standard mysqldump. The dumped databases will then be uploaded to a secure Cloud Storage Bucket using the gsutil.

Getting Started

Manifests and scripts used in this tutorial are available from Github. Download them to follow along or to use as templates.

Kubernetes Templates

IAM Service Account

The container started by the cronjob will need access to a storage bucket. Service Accounts are idea in scenarios like this, because the account is for an automated process and it only serves a single purpose — copy the dump file to a storage bucket.

Create the Service Account

The backup container will need write access to the storage bucket where the sql dumps will be stored. This can be accomplished by creating a service account and then assigning it the appropriate permissions to the storage bucket.

Create a Kubernetes Secret

Our backup Docker container should not be built with the service accounts credentials stored in it. Instead, it will be stored as a Kubernetes Secret, which will be accessible to the MySQL backup container.

Storage Bucket

Creating a storage bucket.

MySQL Backup Docker Image

With next step is to create a new Docker image that will perform MySQL backups. Google provides images with CloudSDK preinstalled

Backup script

A shell script will be needed to perform the actual backup. The script will be the entrypoint for the image we create, and it’s job will be do perform the MySQL dump and the upload the dumps to cloud storage. 

#!/bin/bash
# Based on script created by camilb's (github.com/camilb)  
# Source: https://github.com/camilb/kube-mysqldump-cron/blob/master/Docker/dump.sh

DB_USER=${DB_USER:-${MYSQL_ENV_DB_USER}}
DB_PASS=${DB_PASS:-${MYSQL_ENV_DB_PASS}}
DB_NAME=${DB_NAME:-${MYSQL_ENV_DB_NAME}}
DB_HOST=${DB_HOST:-${MYSQL_ENV_DB_HOST}}
ALL_DATABASES=${ALL_DATABASES}
IGNORE_DATABASE=${IGNORE_DATABASE}
GS_BUCKET=${BACKUP_STORAGE_BUCKET}

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 [[ ${GS_STORAGE_BUCKET} == "" ]]; then
    echo "Missing GS_BUCKET env variable"
    exit 1
fi

if [[ ${ALL_DATABASES} == "" ]]; then
    if [[ ${DB_NAME} == "" ]]; then
        echo "Missing DB_NAME env variable"
        exit 1
    fi
    mysqldump --user="${DB_USER}" --password="${DB_PASS}" --host="${DB_HOST}" "$@" "${DB_NAME}" > /mysqldump/"${DB_NAME}".sql
    gsutil cp /mysqldump/"${DB_NAME}".sql ${GS_BUCKET}
else
    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 "Dumping database: $db"
        mysqldump --user="${DB_USER}" --password="${DB_PASS}" --host="${DB_HOST}" --databases $db > /mysqldump/$db.sql
        gsutil cp /mysqldump/$db.sql ${GS_BUCKET}
    fi
done
fi

Dockerfile

The Dockerfiles will be very simple. It will accept a few environment variables, which will be used to connect to the target database server.

The backup script created earlier will also be copied to it, and it will be set as the entry point for the container. 

FROM google/cloud-sdk:alpine

ENV GOOGLE_PROJECT \
ENV GOOGLE_CLIENT_EMAIL \ 
ENV DB_USER \
    DB_PASS \   
    DB_NAME  \
    DB_HOST  \
    ALL_DATABASES  \
    IGNORE_DATABASES  \
    GS_BUCKET demo-backup-serverlab

COPY service-account.json /root/service_key.json
COPY backup.sh /root/backup.sh

RUN gcloud config set project $GOOGLE_PROJECT && \
    gcloud auth activate-service-account --key-file /root/service_key.json && \
    gsutil ls gs://$GS_STORAGE_BUCKET/


VOLUME ["/root/.config"]

Build and Publish Docker Image

Instructions on building the container. 

docker build -t gcr.io/serverlab/mysql-backup:0.0.1 .
docker push gcr.io/serverlab/mysql-backup:0.0.1

Create A Kuberbetes CronJob

It is finally time to create the CronJob. We will schedule the job to run nightly. 

---
apiVersion: v1
kind: CronJob
metadata:
  name: mysql-backup
spec:
  schedule: "1/* * * *"
  jobTemplate:
    spec:
      containers:
        - name: mysql-backup
          image: gcr.io/serverlab/mysqlbackup:0.0.1
          env:
            - name: GOOGLE_PROJECT
              value: myblog
            - name: GOOGLE_EMAIL
              value: [email protected]
            - name: DB_HOST
              value: mysql-service
            - name: DB_USER
              value: root
            - name: DB_PASS
              valueFrom:
                secretKeyRef:
                  name: mysql-secrets
                  key: root-password
            - name: DB_NAME
              value: wordpress
            - name: GS_BUCKET
              value: demo-backup-bucket

Related Posts: