tobiasbp/db-backup repository overview

⁠Overview

db_backup.py is a Python script for backing up MySQL/MariaDB databases to an S3 bucket or a local directory. Databases are read using mysqldump , compressed with gzip and backed up using rclone⁠.

In an attempt to complete as many backups as possible, the script will not exit on an error, but will report a non 0 exit code after all backups have been attempted.

Local logging is to standard out only, unless a log file is specified with the flag --log-file, in which case messages are also logged to the file. The configuration file is /etc/backups.yaml unless a file is specified with the flag --config.

Logging to Google Chat is supported. A separate loglevel⁠ can be configured. That loglevel must be higher than the general loglevel to have any restrictive effect. Setting the loglevel to WARNING(30), would post only warnings and above to Google Chat if the general loglevel is set to INFO(20).

Database backups are stored at the following sub path: rootdir/backup_name/year/month/database_name+timestamp.sql.gz on the destination.

The reporting of the size of backed up files, can be configured using bitmath_prefix (default: True) & bitmath_system (Default: SI). These values are parsed directly to the bitmap⁠ library. Read their documentation.

You can configure as many backups as you want. Each backup needs a source and a destination. A source can specify more than one database to be backed up.

rootdir: my_database_backups
rclone_config: /etc/rclone.conf
timeout: 600
loglevel: INFO
bitmath_system: SI
bitmath_bestprefix: yes
google_chat:
  url: https://chat.googleapis.com/v1/spaces/XXX/messages?key=XXX
  loglevel: WARNING
backups:
  backup_01:
    source:
      type: mysql
      host: db1.example.com
      port: 3306
      user: read_only_user
      password: 123456
      databases: [mysql, sys]
    destination:
      type: s3
      access_key_id: my_access_key_id
      bucket: my_bucket
      endpoint: my_endpoint
      secret_access_key: my_secret_access_key
  backup_02:
    source:
      type: mysql
      host: db2.example.com
      port: 3306
      user: read_only_user
      password: 654321
      databases: [sys]
      single-transaction: No
    destination:
      type: local
      path: /var/backups

• In source, single-transaction defaults to Yes. This will dump InnoDB databases without the need to lock them.
• In source, port defaults to the standard MySQL port 3306.

Environment variables S3_BUCKET, S3_ENDPOINT, S3_ACCESS_KEY_ID and S3_SECRET_ACCESS_KEY will be used if matching parameters are not set in an S3 destination. This allows for simpler configuration if many sources are to be backed up to single S3 destination. That is, config file has highest precedence.

⁠Running db-backup

How to run db-backup.

⁠In Kubernetes with Helm

You can download the Helm chart in this repository⁠ or you can install it by adding the Helm package repository as described below.

• Add repository to Helm: helm repo add charts.balle-petersen.org https://charts.balle-petersen.org
• Create local configuration file based on helm/my_values.yaml in this repository
• Install in Kubernetes as my-db-backup: helm install my-db-backup charts.balle-petersen.org/db-backup -f my_values.yaml

When updating configuration in my_values.local.yaml in the future, or the chart is updated, the following command can be used to upgrade the running release: helm upgrade my-db-backup charts.balle-petersen.org/db-backup -f my_values.yaml

⁠In Docker

⁠Build umage locally

• Check out repository
• Build image: docker build -t ddb .
• Create a local config file (External to the container) at /local/backups/backups.yaml
• Run container once, and delete it: docker run -v /local/backups:/etc/backups --rm ddb

⁠Pull image from Docker Hub

• Pull image: docker pull tobiasbp/db-backup
• Create a local config file (External to the container) at /local/backups/backups.yaml
• Run container once, and delete it: docker run -v /local/backups:/etc/backups --rm tobiasbp/db-backup

⁠Locally

• Check out repository
• Go to dir src
• Copy backups.yaml to backups.local.yaml
• Add your configuration to backups.local.yaml
• Run with : ./do-db-backup.py --config ./backups.local.yaml

⁠Alternatives

Here are some alternatives I am aware of

⁠mysqldump for Kubernetes

A Helm chart⁠ for setting up mysqldump to run as a cron job in Kubernetes.

• Only allows for a single mysql host.
• Relies on existing Kubernetes secret.
• Does not support S3
• Saves backup to local dir (Also to SSH, Open Stack and Google Cloud Storage?)

⁠kubernetes-cloud-mysql-backup

A shell script⁠ with Dockerfile, for backing up MySQL databases with mysqldump and storing them on S3 storage og Google Cloud Storage.

• Only allows for a single mysql host.
• No Helm chart.
• No Kubernetes config file in repository.
• Relies on existing Kubernetes secret.
• Supports logging to a Slack channel.
• Saves backup to S3 and Google Cloud Storage.

⁠mysql-backup-chart

A Helm chart⁠ for running database backups as a cron job in Kubernetes.

• Only allows for a single mysql host.
• Relies on existing Kubernetes secret.
• Saves backup to S3 and Google Cloud Storage.
• Two backup sources: MySQL and Coogle Cloud SQL

⁠To do

• Add backend local
• Exit code should be non 0 if any errors occured during the backup run
• Log to Google Chat
• Add helm chart to public repo
• Add info on time spent on backup to log
• Add info on size of file backed up to log
• Somehow expose a metric with time since last successful backup of a database
• Spin out GoggleChatHandler in to seperate PyPI package for reuse (Someone else had a package. Now using it)
• Add config parameter for expected size of backup. Throw error/warning if backup is too small
• Add other rclone backends by looking at rclone config⁠
• Setting for max number of backups? Max age?
• Add option for encrypting backed up files
• Support encrypted MySQL connections
• Support dumping of all databases without naming them. (Could be default, if no databases mentioned)
• Validate configuration values
• When using the S3 backend, allow for non changing backup path to take advantage of versioning in S3
• Make separate kubernetes secret to hold the environment variables. Different teams could then have access to the source & destination credentials.