Runs mysql under docker and enables automatic backups

docker-compose.yaml

version: '3.5'

volumes:
  mysql_data:
      external: true
      driver: local

services:
  mysql:
      container_name: mysql
      image: mysql:5.6
      volumes:
        - ./mysql_data:/var/lib/mysql
        - ./mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
        - ./scripts/mysql-backup.sh:/scripts/mysql-backup.sh
        - /backups/mysql:/backups/mysql
      environment:
        - MYSQL_ROOT_PASSWORD=CHANGEROOTPW
        - MYSQL_DATABASE=database
        - MYSQL_USER=mysqluser
        - MYSQL_PASSWORD=CHANGEUSERPW
      ports:
        - 3306:3306

mysql-backup.sh

#!/bin/bash
#----------------------------------------
# OPTIONS
#----------------------------------------
USER='USERNAME'       # MySQL User
PASSWORD='PASSWORD' # MySQL Password
DAYS_TO_KEEP=5    # 0 to keep forever
GZIP=1            # 1 = Compress
BACKUP_PATH='/backups/mysql'
#----------------------------------------

# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
  mkdir -p $BACKUP_PATH
fi

# Get list of database names
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "|" | grep -v Database`

for db in $databases; do

  if [ $db == 'information_schema' ] || [ $db == 'performance_schema' ] || [ $db == 'mysql' ] || [ $db == 'sys' ]; then
    echo "Skipping database: $db"
    continue
  fi

  date=$(date -I)
  if [ "$GZIP" -eq 0 ] ; then
    echo "Backing up database: $db without compression"
    mysqldump -u $USER -p$PASSWORD --databases $db --single-transaction --quick --lock-tables=false > $BACKUP_PATH/$date-$db.sql
  else
    echo "Backing up database: $db with compression"
    mysqldump -u $USER -p$PASSWORD --databases $db --single-transaction --quick --lock-tables=false | gzip -c > $BACKUP_PATH/$date-$db.gz
  fi
done

# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi