backup databases

backup-db.sh

#!/bin/bash

#
# Create a MariaDB user for the backups:
#
# CREATE USER 'backupuser'@localhost IDENTIFIED BY 'password';
# GRANT SELECT ON *.* TO 'backupuser'@'localhost';
# FLUSH PRIVILEGES;
#
# Tested for a MONARC installation:
# https://github.com/monarc-project
#

MYSQL_USER=backupuser
MYSQL_PWD=password
ME=`whoami`

BACKUP_DIR=/home/$ME/dbbackup # directory for the backup
mkdir -p $BACKUP_DIR

DB_TO_SKIP="mysql;information_schema;performance_schema;" # databases to skip

# set -x
set -e

git=0
while getopts "hg" option
do
    case $option in
        h)
        echo -e "Backup MariaDB databases. Available options:"
        echo -e "\t-g\tversion each backup with Git"
        echo -e "\t-h\tdisplay this message"
        exit 1
        ;;
        g)
        git=1
    esac
done



for DB in $(mysql -u $MYSQL_USER -p$MYSQL_PWD -e 'show databases' -s --skip-column-names); do
  if [[ "$DB_TO_SKIP" != *"$DB"* ]]; then
      mysqldump -u $MYSQL_USER -p$MYSQL_PWD $DB > $BACKUP_DIR/$DB.sql;
      # mysqldump -u $MYSQL_USER -p$MYSQL_PWD  $DB > $BACKUP_DIR/$DB-$(date +%Y%m%d).sql
  fi
done

if [[ $git -eq 1 ]]; then
  git init $BACKUP_DIR
  cd $BACKUP_DIR
  git add .
  git commit -am "Update from $(date +%Y-%m-%d)."
fi