Docker compose file to start MariaDB container on docker swarm.

vi docker-compose.yml

version: '3.7'
services:

  mariadb:
    image: mariadb
    secrets:
      - MYSQL_ROOT_PASSWORD
      - MYSQL_PASSWORD
    environment:
      - MYSQL_ROOT_PASSWORD_FILE=/run/secrets/MYSQL_ROOT_PASSWORD
      - MYSQL_PASSWORD_FILE=/run/secrets/MYSQL_PASSWORD
      - MYSQL_DATABASE=db
      - MYSQL_USER=user
    command: --transaction-isolation=READ-COMMITTED --binlog-format=ROW
    volumes:
      - mariadb:/var/lib/mysql
    deploy:
      replicas: 1
      restart_policy:
        condition: any
      placement:
        constraints:
          - node.labels.mariadb==true

volumes:
  mariadb:

secrets:
  MYSQL_ROOT_PASSWORD:
    external: true
  MYSQL_PASSWORD:
    external: true

https://docs.docker.com/compose/compose-file/

Create secrets

Use strong passwords.

printf "ROOTPASSWORD" | sudo docker secret create MYSQL_ROOT_PASSWORD -
printf "USERPASSWORD" | sudo docker secret create MYSQL_PASSWORD -

https://docs.docker.com/engine/reference/commandline/secret_create/

Select node to host MariaDB container

docker node update --label-add mariadb=true DOCKERNODENAME

Deploy stack

sudo docker stack deploy --compose-file docker-compose.yml STACKNAME

Backup with Mariabackup

Create PGP key and run backup script on DOCKERNODENAME where MariaDB is running.

Generate PGP key for root user, with strong password

sudo su –

gpg --gen-key
# (1) RSA and RSA (default)
# 4096
# 2y

Export keys to safe location

pub 4096R/XXXXXXXX

gpg -a --export XXXXXXXX > backupGPGpublic.key
gpg -a --export-secret-keys XXXXXXXX > backupGPGprivate.key

remember to remove .key files from server after you moved them to safe location.

Sign new PGP key with you master PGP key (Optional)

# where you store your master key
gpg --import backupGPGpublic.key
gpg --sign-key --ask-cert-level XXXXXXXX
# 3
# y
gpg -a --export XXXXXXXX > signedBackupGPGpublic.key
# move back to DOCKERNODENAME server
gpg --import signedBackupGPGpublic.key

Backup script

I use SFTP to move files and i have added this servers root users SSH publickey to my backup server.

sudo vi mariadbBackup.sh

#!/bin/bash
docker exec $(docker ps -q -f name=mariadb) /usr/bin/mariabackup --backup --stream=xbstream --user root --password=$(docker exec $(docker ps -q -f name=mariadb) cat /run/secrets/MYSQL_ROOT_PASSWORD) | gzip | gpg --batch --encrypt --output PATHTO/mariabackup-$(date +"%m-%d-%Y_%H-%M-%S").xbstream.gz.gpg -r XXXXXXXX
printf "put PATHTO/mariabackup-*\nquit" | sftp -b- [email protected]:/location/
rm -f PATHTO/mariabackup-*

Daily cron

sudo vi /etc/crontab

56 2 * * * root PATHTO/mariadbBackup.sh >> /var/log/backups/mariadb.log 2>&1

sudo mkdir -p /var/log/backups/