readme.md

Download

curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-dump.sh
curl -JO https://gist.githubusercontent.com/sebastian13/cc58304f7b177ac1d16d7671dc67efb9/raw/docker-mysql-restore.sh
chmod +x docker-mysql-(dump|restore).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}      

docker-mysql-dump.sh

#!/bin/bash

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

# Load database name + root password
source .env

# Check if variables were provided
if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit 1; fi
if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi

# Check package availability
command -v docker compose >/dev/null 2>&1 || { echo "[Error] Please install the Compose plugin"; exit 1; }
command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }

# Check if mysql is running
docker compose ps | grep -q 'mysql.*Up' || { echo "[Error] mysql service not running. Please start manually."; exit 1; }

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

docker compose exec -T mysql /usr/bin/mysqldump -u root \
      --password=$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE \
      | gzip --rsyncable > ./dbdumps/`date +\%Y\%m\%d-\%H\%M`-$MYSQL_DATABASE.sql.gz

docker-mysql-restore.sh

#!/bin/bash

# Load database name + root password
source .env

# Check if variables were provided
if [ -z ${MYSQL_DATABASE+x} ]; then echo \$MYSQL_DATABASE was not provided; exit 1; fi
if [ -z ${MYSQL_ROOT_PASSWORD+x} ]; then echo \$MYSQL_ROOT_PASSWORD was not provided; exit 1; fi

# Check package availability
command -v docker compose >/dev/null 2>&1 || { echo "[Error] Please install the Compose plugin"; exit 1; }
command -v gzip >/dev/null 2>&1 || { echo "[Error] Please install gzip"; exit 1; }
command -v pv >/dev/null 2>&1 || { echo "[Error] Please install pv"; exit 1; }
command -v awk >/dev/null 2>&1 || { echo "[Error] Please install awk"; exit 1; }

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

# Find latest dumps
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 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 3
	echo "Wait for DB to initialize"
done

# 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}

restore-help.sh

# ERROR 1118 (42000) Row size too large
docker compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "SET GLOBAL innodb_strict_mode = 0"
docker compose exec mysql /usr/bin/mysql -u root --password=${MYSQL_ROOT_PASSWORD} -e "show variables like '%innodb_strict_mode%' "

I just had a problem with source .env , but replacing this line with . ./.env worked. Thanks for these scripts, amazing work!