readme.md

Download

curl -JO https://gist.githubusercontent.com/sebastian13/2bd018d47f5e5c4bca1f58055b9f47c9/raw/docker-zabbix-mysql-dump.sh
curl -JO https://gist.githubusercontent.com/sebastian13/2bd018d47f5e5c4bca1f58055b9f47c9/raw/docker-zabbix-mysql-restore.sh
chmod +x docker-zabbix-mysql-*.sh

Example: .env

MYSQL_DATABASE=example
MYSQL_ROOT_PASSWORD=123456

Example: docker-compose.yml

services:
  mysql:
    image: mariadb
    ...
    env_file: .env
    
  other:
    ...
    environment:
      - WORDPRESS_DB_NAME=${MYSQL_DATABASE}
      - WORDPRESS_DB_PASSWORD=${MYSQL_ROOT_PASSWORD}      

Acknowledgements

• This omits tables listed in https://blog.zabbix.com/backups-to-the-rescue/23442/
• Another nice list of tables to exclude from backup: https://github.com/maxhq/zabbix-backup/blob/4a093d348a3ca07402c6482990a3a8e29a9b6a86/zabbix-dump#L607

docker-zabbix-mysql-dump.sh

#!/bin/bash

# Change to the script's directory & create directory
cd $(dirname "$(readlink -f "$0")")
mkdir -p ./dbdumps

# Start mysql service
docker --log-level=error compose up -d mysql

# Wait
i=20
while (( i >= 1 )); do
        sleep 10
        echo -ne
        echo -ne "Wait for DB to initialize. Creating Dump in $(( i-- )) seconds ...  "'\r'
done

# Load database name + root password
source .env

# Delete old Backups
find ./dbdumps/* -atime +7 -exec rm {} \;

# Dump Table Definitions
docker compose exec -T mysql mysqldump -u root --password=$MYSQL_ROOT_PASSWORD --no-data $MYSQL_DATABASE > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE-defs.sql

## Dump Content
docker compose exec -T mysql /usr/bin/mysqldump -u root \
      --password=$MYSQL_ROOT_PASSWORD \
      --single-transaction \
      --no-create-info \
      --ignore-table=zabbix.events \
      --ignore-table=zabbix.history \
      --ignore-table=zabbix.history_log \
      --ignore-table=zabbix.history_str \
      --ignore-table=zabbix.history_text \
      --ignore-table=zabbix.history_uint \
      --ignore-table=zabbix.trends \
      --ignore-table=zabbix.trends_uint \
      $MYSQL_DATABASE \
      | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE.sql.gz

docker-zabbix-mysql-restore.sh

#!/bin/bash

# Check package availability
command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }

# Start mysql service
docker --log-level=error compose up -d mysql

# Load database name + root password
source .env

# Find latest definition
latestd1=$(ls -1t ./dbdumps/*-defs.sql | head -1)
latestd2=$(ls -1t ./dbdumps/*-defs.sql | head -n2 | tail -n1)
latestd3=$(ls -1t ./dbdumps/*-defs.sql | head -n3 | tail -n1)

# Select dump
echo "Which content dump should be used for restoring?"
select resultd in $latestd1 $latestd2 $latestd3
do
    [ $resultd ] && break
done

# Find latest content
latest1=$(ls -1t ./dbdumps/*.sql.gz | head -1)
latest2=$(ls -1t ./dbdumps/*.sql.gz | head -n2 | tail -n1)
latest3=$(ls -1t ./dbdumps/*.sql.gz | head -n3 | tail -n1)

# Select dump
echo "Which content dump should be used for restoring?"
select result in $latest1 $latest2 $latest3
do
    [ $result ] && break
done

# Get the size of the uncompressed sql file
size=$(gzip -l $result | awk 'FNR==2{print $2}')

# Wait
while ! (docker compose exec mysql /usr/bin/mysqladmin -u root --password=${MYSQL_ROOT_PASSWORD} ping --silent)
do
	sleep 15
	echo "Wait for DB to initialize"
done

# Restore Defs
cat $resultd | docker compose exec -T mysql \
	/usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}

# Restore
echo "Starting restore now."
gunzip --keep --stdout $result | pv --size $size | docker compose exec -T mysql \
	/usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}