This is a suggestion of how to backup a DB (MySQL/MariaBD), rotate the backups and mirror them from another server

DB backup, rotate & mirror.md

Backup managment

This is a suggestion of how to backup a DB (MySQL/MariaBD), rotate the backups and mirror them from another server

Backup

First we need to backup the database on a given frequency

• Create a dedicated directory

mkdir backup

• Create a docker composition and add the backup service

nano docker-compose.yml

version: '3'

services:
  backup:
    image: databack/mysql-backup:ee17125b37e354e34d322a0ccbf61e144d542a0f
    volumes:
      - ./backup:/db
    user: "0"
    environment:
      DB_DUMP_TARGET: /db
      DB_SERVER: db
      DB_DUMP_FREQ: "1440"
      DB_DUMP_BEGIN: "0000"
      DB_NAMES: ${MYSQL_DATABASE}
      DB_USER: ${MYSQL_USER}
      DB_PASS: ${MYSQL_PASSWORD}

• Set the DB_SERVER variable to point to the correct database host (use an Docker external network to be able to communicate with another composition)
• Set DB_DUMP_FREQ to the delay between dumps in minutes (here 1440min: 24h)
• Set DB_DUMP_BEGIN for the start time (here 00:00), comment for immediate start
• Create a .env

nano docker-compose.yml

MYSQL_DATABASE=my_db
MYSQL_USER=my_user
MYSQL_PASSWORD=my_password

• Start the service

docker-compose up -d

• Check the backup directory content

ls -l backup/

All the backup files should be found here

Force a backup by specifying RUN_ONCE: 1 env var
For more info about the configuration, check the databacker/mysql-backup github

Rotate

Now we need to rotate the backups (remove unneeded/too old backups)
For that we are going to use another docker image

• Add the rotate service into the docker-compose.yml

# ...
  rotate:
    image: glide/cron-rotate-backups:0.1
    volumes:
      - ./backup:/data
    environment:
      ROTATE_CRON: "0 * * * *"
      ROTATE_OPTIONS: "--daily=7 --weekly=4 --monthly=12 --yearly=2"

• Set ROTATE_CRON cron string accordingly, this tells the frequency for the rotation script to run
• Set ROTATE_OPTIONS to tell which time ranges are needed to keep the files
• Update the services

docker-compose up -d

• Check if the services are running

docker-compose ps

• Check the logs for the new service

docker-compose logs -f rotate

For more info about the configuration, check the glide/cron-rotate-backups github

Mirror the backups

To ensure the persistence of our backups we need to have them copied to another host.
That allows us to avoid loosing all the backups in case of production total crash.
For that, we are going to use another Docker image.

• SSH into the destination server (the host where the backups need to be copied)
• Create a dedicated directory

mkdir backup

• Create a docker composition and add the rsync service

nano docker-compose.yml

version: '3'

services:
  rsync:
    image: glide/cron-rsync:0.1
    volumes:
      - ~/.ssh:/host-ssh:ro
      - ./backup:/backup
    environment:
      RSYNC_CRONTAB: "0 * * * *"
      RSYNC_OPTIONS: "-tvr --delete [email protected]:backup/ /backup"

• Set RSYNC_CRONTAB cron string accordingly, this tells the frequency for the rotation script to run
• Set RSYNC_OPTIONS right part of the rsync command (rsync ${RSYNC_OPTIONS} will be run)
• Replace [email protected] by the production server where the backups are
• Make sure the host can access [email protected] without password (with an ssh key)
• Run the service

docker-compose up -d

• Check the logs

docker-compose logs -f rsync

For more info about the configuration, check the glide/cron-rsync github