MySql backups with a docker Sidecar
This time I'm bringing you a small utility project that helps you manage backups of your mysql deployments on docker/kubernetes.
> Archived article from 2018, from the old nutellino.it: commands and versions may be out of date.
> Machine translation from Italian: the original is here.
Hi everyone!
This time I'm bringing you a small utility project that helps you manage backups of your mysql deployments on docker/kubernetes.
I built this little container (you can find the project here: https://github.com/nutellinoit/sidecar-backup-mysql) so that deployments manage their own backups, instead of a sysadmin having to go and create every backup by hand.
Here's the docker-compose.yml for the deploy:
version: '2'
services:
sidecar-backup-mysql:
image: nutellinoit/sidecar-backup-mysql:1.0
volumes:
- ./dumpdb:/go/src/app/dumpdb
restart: always
environment:
- MYSQL_HOST=db
- MYSQL_DATABASE=wordpress
- MYSQL_USER=root
- MYSQL_PASSWORD=123456
- MYSQL_SQL_FILENAME=nomebackup
- SCHEDULE=0 * * * * *
# Database per test
db:
image: mysql:latest
restart: always
environment:
MYSQL_DATABASE: wordpress
MYSQL_ROOT_PASSWORD: 123456Everything is driven by a few environment variables, described below:
- MYSQL_HOST : The hostname of the machine to connect to and back up
- MYSQL_DATABASE : The name of the mysql database to save
- MYSQL_USER : The mysql username to connect with
- MYSQL_PASSWORD : The mysql user's password
- MYSQLSQLFILENAME : The name of the backup file (the suffix
$_now.sqlwill then be appended to this name, where$_nowis a date format built like this:"%s_%m_%d_%Y") - SCHEDULE : the timing of the backup runs. Below is the table explaining each position
| Field name | Mandatory? | Allowed values | Allowed special characters |
|---|---|---|---|
| Seconds | Yes | 0-59 | * / , - |
| Minutes | Yes | 0-59 | * / , - |
| Hours | Yes | 0-23 | * / , - |
| Day of month | Yes | 1-31 | * / , - ? |
| Month | Yes | 1-12 or JAN-DEC | * / , - |
| Day of week | Yes | 0-6 or SUN-SAT | * / , - ? |
Where does the backup get saved?
The destination folder is defined in the volumes directive
volumes:
- ./dumpdb:/go/src/app/dumpdbIn this case the host folder dumpdb is mapped inside the directory where the docker-compose.yml lives. All the mysql backups the container creates according to the configured schedule will be saved in this folder.
Kubernetes yaml
And here's also a small reference for deploying this container on kubernetes:
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
annotations:
kompose.cmd: kompose convert
kompose.version: 1.7.0 ()
creationTimestamp: null
labels:
io.kompose.service: sidecar-backup-mysql
name: sidecar-backup-mysql
spec:
replicas: 1
strategy:
type: Recreate
template:
metadata:
creationTimestamp: null
labels:
io.kompose.service: sidecar-backup-mysql
spec:
containers:
- env:
- name: MYSQL_DATABASE
value: wordpress
- name: MYSQL_HOST
value: db
- name: MYSQL_PASSWORD
value: "123456"
- name: MYSQL_SQL_FILENAME
value: nomebackup
- name: MYSQL_USER
value: root
- name: SCHEDULE
value: 0 * * * * *
image: nutellinoit/sidecar-backup-mysql:1.0
name: sidecar-backup-mysql
resources: {}
volumeMounts:
- mountPath: /go/src/app/dumpdb
name: sidecar-backup-mysql-claim0
restartPolicy: Always
volumes:
- name: sidecar-backup-mysql-claim0
persistentVolumeClaim:
claimName: sidecar-backup-mysql-claim0
status: {}
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
creationTimestamp: null
labels:
io.kompose.service: sidecar-backup-mysql-claim0
name: sidecar-backup-mysql-claim0
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1000Mi
status: {}In this case the backups will be saved inside the persistent volume claim; it's up to you to decide which persistent volume to bind it to, or whether to bind it to a Storage Classes with auto provisioning.
$ cat ~/archivio/backup-mysql-con-sidecar-docker.md
WARNING: this file is from 2018.
I wrote it, but a me from a few years ago: it may no longer reflect what I think or what I can do.
NOTE: machine translation from Italian, the original is here.
Continue? [y/N]