Posted onMar 3, 2021

Backup MySQL Databases in Kubernetes

#docker #kubernetes #cronjob #bash

In this post, we will show you how to create a MySQL server backup using Kubernetes CronJobs.

In our case, we do not have a managed MySQL server. But we want to backup it to our NAS, so that we have a backup in case of emergency.
For this we first build a container that can execute our tasks, because we will certainly need several tasks to backup our cluster.

CronJob Agent Container

First, we'll show you our Dockerfile so you know what we need.

FROM alpine:3.10# UpdateRUNapk--update add--no-cache bash nodejs-current yarn curl busybox-extras vim rsync git mysql-client openssh-clientRUNcurl-LO https://storage.googleapis.com/kubernetes-release/release/v1.18.0/bin/linux/amd64/kubectl&&chmod +x ./kubectl&&mv ./kubectl /usr/local/bin/kubectl# ScriptsRUNmkdir /srv/jobsCOPY jobs/* /srv/jobs/# Backup FolderRUNmkdir /var/backupRUNmkdir /var/backup/mysql

Backup Script

And now our backup script which the container executes.

Our script is quite simple, we get all tables with the mysql client, export them as sql file, pack them in a zip file and send them in a 8 hours interval to our NAS.

#!/bin/bash############# SET VARIABLES ############## Env VariablesBACKUPSERVER="8.8.8.8"# Backup Server IpBACKUPDIR=/var/backup/mysqlBACKUPREMOTEDIR="/mnt/backup/kubernetes/"HOST="mariadb.default"NOW="$(date +"%Y-%m-%d")"STARTTIME=$(date +"%s")USER=mysqlUserPASS=mysqlPassword############# BUILD ENVIROMENT ############## Check if temp Backup Directory is emptymkdir$BACKUPDIRif["$(ls-A$BACKUPDIR)"];thenecho"Take action$BACKUPDIR is not Empty"rm-f$BACKUPDIR/*.gzrm-f$BACKUPDIR/*.mysqlelseecho"$BACKUPDIR is Empty"fi############# BACKUP SQL DATABASES #############forDBin$(mysql-u$USER-p$PASS-h$HOST-e'show databases'-s--skip-column-names);domysqldump-u$USER-p$PASS-h$HOST--lock-tables=false$DB>"$BACKUPDIR/$DB.sql";done############# ZIP BACKUP #############cd$BACKUPDIRtar-zcvf backup-${NOW}.tar.gz*.sql############# MOVE BACKUP TO REMOTE #############rsync-avz$BACKUPDIR/backup-${NOW}.tar.gz root@$BACKUPSERVER:$BACKUPREMOTEDIR# done

Kubernetes CronJob Deployment

Finally we show you the kubernetes deployment for our agent.

In the deployment, our agent is defined as a CronJob that runs every 8 hours.
In addition, we have added an SSH key as a Conifg map so that this can write to the NAS and a certain security is given.

apiVersion:batch/v1beta1kind:CronJobmetadata:name:backup-mariadbnamespace:defaultspec:schedule:"08***"successfulJobsHistoryLimit:1failedJobsHistoryLimit:1jobTemplate:spec:template:spec:containers:-name:cronjob-agentimage:xxx/cronjob-agentcommand:["bash","/srv/jobs/backup-mariadb.sh"]volumeMounts:-mountPath:/root/.ssh/id_rsa.pubname:cronjob-default-configsubPath:id_rsa.pub-mountPath:/root/.ssh/id_rsaname:cronjob-default-configsubPath:id_rsareadOnly:true-mountPath:/root/.ssh/configname:cronjob-default-configsubPath:configvolumes:-name:cronjob-default-configconfigMap:name:cronjob-default-configdefaultMode:256restartPolicy:Never