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- user@backupserver:/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/