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

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

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

# Start mysql service
docker-compose --log-level ERROR up -d mysql

# 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

# Load database name + root password
source .env

# 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

#   Check package availability
command -v docker-compose >/dev/null 2>&1 || { echo "[Error] Please install docker-compose"; 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-compose --log-level ERROR up -d mysql

# Load database name + root password
source .env

# 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%' "