#!/bin/bash

# Recommended:
# Do not use root user to create backups.
#
# Create a new user
# CREATE USER 'backup'@'localhost' IDENTIFIED BY '<strong_password>';
#
# Grant the following privileges to backup user
# GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

BACKUP_DIR="/backups/mysql-dumps"
MYSQL_USER="<user>"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="<password>"
MYSQLDUMP=/usr/bin/mysqldump

# check if backup directory exists
if [ -d "$BACKUP_DIR" ]; then
  # remove all previous backups dumps
  rm -f $BACKUP_DIR/*
else
  # create directory to save dump
  mkdir -p "$BACKUP_DIR"
fi

# get all databases names
databases=`$MYSQL -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

# loop through databases and dump each one to a specific file
for db in $databases; do
  $MYSQLDUMP -x -e --events --ignore-table=mysql.event -u $MYSQL_USER -p$MYSQL_PASSWORD --databases $db > $BACKUP_DIR/$db-$(date "+%Y%m%d").sql
done