Backup mariaDB using mariabackup and upload to S3 or BackBlaze using docker compose and restic. How to restore it

MariaDB container

I started the mariadb container like this:

version: "3"
name: "mariadb"
services:
  mariadb:
    image: mariadb:10.10.2
    container_name: mariadb_prod_1
    ports:
      - "3306:3306"
    volumes:
      - ./fs/mariadb/volume:/var/lib/mysql:rw
      - ./fs/mariadb/mysqld:/var/run/mysqld:rw
      - ./fs/mariadb/log:/var/log/mysql:rw
      - ./fs/mariadb/conf.d:/etc/mysql/conf.d:ro
    environment:
      MARIADB_MYSQL_LOCALHOST_USER: 1
      MARIADB_MYSQL_LOCALHOST_GRANTS: "RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR"
      MARIADB_ROOT_PASSWORD: password
    restart: always

Those folders are empty at startup, I used them to make the server data persistent and also because some were required by mariabackup.

Afterwards I created a script which is ran by a cronjob, the script creates a full backup using mariabackup and uploads the backup to S3 or back blaze using restic(restic detects which files are already up and it won't upload them again).

Env Credentials

Before running the script, you need to create an env file next to it called .env with the following envs:

# Add here the envs for restic
export RESTIC_REPOSITORY="s3:https://s3.eu-central-003.backblazeb2.com/bucket/folder"
export AWS_ACCESS_KEY_ID="?"
export AWS_SECRET_ACCESS_KEY="?"
export RESTIC_PASSWORD="?"

If the restic repository is not initialized, you need to run this(anywhere):

# This initializes the repository in S3

unset HISTFILE
export RESTIC_REPOSITORY="s3:https://s3.eu-central-003.backblazeb2.com/bucket/folder"
export AWS_ACCESS_KEY_ID="key"
export AWS_SECRET_ACCESS_KEY="secret"
export RESTIC_PASSWORD="pass"

restic init

Backup Script

The script looks like this:

#!/bin/bash
SCRIPT_PATH='/opt/mariadb/backup/'

LOG_FOLDER="${SCRIPT_PATH}/fs/log"
BACKUP_FOLDER="${SCRIPT_PATH}/fs/backup"
# From the running mariadb container
MARIADB_VOLUME="/opt/mariadb/fs/mariadb/volume"
MARIADB_MYSQLD="/opt/mariadb/fs/mariadb/mysqld"
MARIADB_VERSION=10.10.2

declare -A levels=([DEBUG]=0 [INFO]=1 [WARN]=2 [ERROR]=3)
script_logging_level="INFO"

CURRENT_MONTH_LOG_FOLDER="${LOG_FOLDER}/$(date +'%m-%Y')"
mkdir -p ${CURRENT_MONTH_LOG_FOLDER}
LOG_FILE="${CURRENT_MONTH_LOG_FOLDER}/$(date +'%d-%m-%Y').log"

logThis() {
    local log_priority=$1
    local log_message=$2

    # check if level exists
    [[ ${levels[$log_priority]} ]] || return 1

    # check if level is enough
    (( ${levels[$log_priority]} < ${levels[$script_logging_level]} )) && return 2

    # log here
    # TODO: add colors: https://stackoverflow.com/questions/5947742/how-to-change-the-output-color-of-echo-in-linux
    echo "=====================================================================" >> ${LOG_FILE}
    echo "[$(date)] ${log_priority} : ${log_message}" >> ${LOG_FILE}
}
logThis INFO "Performin Backup for MariaDB" &&

logThis INFO "Cleaning ${BACKUP_FOLDER} folder" &&
rm -rf ${BACKUP_FOLDER}/* &&
rm -rf ${BACKUP_FOLDER}/.gitkeep &&

BEGIN_BACKUP=${SECONDS} &&
# Full backup
logThis INFO "Performing full backup" &&
docker run --rm --user mysql -v ${MARIADB_MYSQLD}:/var/run/mysqld \
  -v ${BACKUP_FOLDER}:/backup \
  -v ${MARIADB_VOLUME}:/var/lib/mysql \
  mariadb:${MARIADB_VERSION} mariabackup --backup \
  --target-dir=/backup --user mysql &>> ${LOG_FILE} &&
BACKUP_DURATION=$((${SECONDS}-${BEGIN_BACKUP})) &&

logThis INFO "Finished performing a full backup, it took ${BACKUP_DURATION} seconds." &&

cd ${BACKUP_FOLDER} &&

# Load restic credentials envs
source ${SCRIPT_PATH}/env &&

logThis INFO "Running restic to upload the bacup to S3" &&
BEGIN_RESTIC=${SECONDS} &&
restic --verbose backup . &>> ${LOG_FILE} &&
RESTIC_DURATION=$((${SECONDS}-${BEGIN_RESTIC})) &&

logThis INFO "Finished uploading backup to S3, it took ${RESTIC_DURATION} seconds" &&
logThis INFO "Finished running script, ran for ${SECONDS} s, Backup took ${BACKUP_DURATION} s, Upload took ${RESTIC_DURATION} s" ||
logThis ERROR "Something went wrong.."

Cronjob

We need to add this to the cronjob to run as root:

# From root
sudo crontab -e

# Run every hour between 07 AM - 21 PM and then run another backup at 1 AM(The cron is in UTC, that's why it's 5-19 and 23)
0 5-19,23 * * * bash /opt/mariadb/backup/backup.sh &>> /opt/mariadb/backup/log/crontab.log

We need root, because we need to have read rights on the mysql files that were backed up and upload them in restic.

Restore Backup

RESTORED_BACKUP_PATH="/opt/mariadb/backup/fs/backup"
MARIADB_VOLUME="/opt/mariadb/fs/mariadb/volume"
MARIADB_COMPOSE="/opt/mariadb/prod_1/compose"

# Stop mariadb service
cd ${MARIADB_COMPOSE} && docker compose down

# Get backup from restic
unset HISTFILE
export RESTIC_REPOSITORY="s3:https://s3.eu-central-003.backblazeb2.com/bucket/folder"
export AWS_ACCESS_KEY_ID="key"
export AWS_SECRET_ACCESS_KEY="secret"
export RESTIC_PASSWORD="pass"

restic snapshots

restic restore ff7baae1 --target ${RESTORED_BACKUP_PATH}

# Prepare backup
docker run --user mysql --rm -v ${RESTORED_BACKUP_PATH}:/backup mariadb:10.10.2 mariabackup --prepare --target-dir=/backup

# Cleand MariaDB volume
cd ${MARIADB_VOLUME} && rm -rf *

# Restore the backup
docker run --user mysql --rm -v ${MARIADB_VOLUME}:/var/lib/mysql \
  -v ${RESTORED_BACKUP_PATH}:/backup mariadb:10.10.2 mariabackup --copy-back --target-dir=/backup

# Start mariadb service or run pipeline
cd ${MARIADB_COMPOSE} && docker compose up -d