#!/bin/bash
#
# Author: Jiří Blažek (blazek@isn.cz)
# App: MySQL/MariaDB backup script FOR ALL DATABASES
#

# absolute path home
APH=$(dirname $(readlink -f $0))

# app conf
MYSQL_USER="root"
MYSQL_PASS="secret-password"
MYSQL_BIN="/usr/bin/mysql"
MYSQL_DUMP="/usr/bin/mysqldump"
BZIP_BIN="/usr/bin/bzip2 -9"
BACKUP_DIR="/backup/mysql"

# log
function log() {
 echo $(/bin/date +"%Y-%m-%d %k:%M:%S") $@
}
LOG_PATH="$APH/backup-mysql.log"

# log info
log "[INFO] START backup MySQL databases" >> $LOG_PATH

# get all db names of mysql
GET_DB_NAMES=$($MYSQL_BIN --user=$MYSQL_USER --password=$MYSQL_PASS -s -N -e "SHOW DATABASES;" | grep -Ev 'information_schema|performance_schema|test|mysql')

# backup all databases
for DB_NAME in $GET_DB_NAMES; do

  $MYSQL_DUMP --user=$MYSQL_USER --password=$MYSQL_PASS --default-character-set=utf8 $DB_NAME | $BZIP_BIN > $BACKUP_DIR/$DB_NAME-$(date '+%Y-%m-%d-%H-%M-%S').bz2
  if [ $? -eq 0 ]; then
    log "[INFO] Database backup OK = "$DB_NAME >> $LOG_PATH
  else
    log "[ERROR] Database backup failed = "$DB_NAME >> $LOG_PATH
    exit 1
  fi

done

# log info
log "[INFO] END backup MySQL databases" >> $LOG_PATH