Backup for MySQL with mysqldump (all database)

.my.cnf

[mysql]
user=backups
password=secret

[mysqldump]
user=backups
password=secret

README.md

Backup for simple MySQL / www stack

su root mkdir /backups nano .my.cnf

Add script to cronjob: crontab -e

0 2 * * * root /root/backup/mysql.sh
0 2 * * * root /root/backup/www.sh

mysql.sh

#!/bin/bash

if [ "$EUID" -ne 0 ]
  then echo "Please run as root"
  exit
fi

DATEFORMAT='%Y-%m-%dT%H-%M-%S'
DATEFORMAT_FOLDER='%Y-%m-%d'

DATETIME=$(date +$DATEFORMAT)
DATETIME_FOLDER=$(date +$DATEFORMAT_FOLDER)

CONFIG_FILE="/backup/.my.cnf"
BACKUP_BASE_DIR="/backup/db"
BACKUP_DIR="$BACKUP_BASE_DIR/$DATETIME_FOLDER"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
BACKUP_RETAIN_DAYS=30

mkdir -p "$BACKUP_DIR"

databases=`$MYSQL --defaults-extra-file=$CONFIG_FILE -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys|mysql)"`

for database in ${databases[@]}; do
        mysqldump --defaults-extra-file=$CONFIG_FILE $database | gzip -9 > "$BACKUP_DIR/$database--$DATETIME.sql.gz"
done

cd $BACKUP_DIR

chown -R root:root *--$DATETIME.sql.gz
chmod -R 0400 *--$DATETIME.sql.gz

## Remove backups

DBDELDATE=`date +$DATEFORMAT_FOLDER --date="${BACKUP_RETAIN_DAYS} days ago"`

if [ ! -z ${BACKUP_BASE_DIR} ]; then
      cd ${BACKUP_BASE_DIR}
      if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
            rm -rf ${DBDELDATE}
      fi
fi

www.sh

#!/bin/bash

if [ "$EUID" -ne 0 ]
  then echo "Please run as root"
  exit
fi

DATEFORMAT='%Y-%m-%dT%H-%M-%S'
DATEFORMAT_FOLDER='%Y-%m-%d'

DATETIME=$(date +$DATEFORMAT)
DATETIME_FOLDER=$(date +$DATEFORMAT_FOLDER)

BACKUP_SYNC_FOLDER="/var/www"
BACKUP_FILES=($(/bin/ls -1 $BACKUP_SYNC_FOLDER))
BACKUP_BASE_DIR="/backup/www"
BACKUP_RETAIN_DAYS=30

BACKUP_DIR="$BACKUP_BASE_DIR/$DATETIME_FOLDER"

mkdir -p "$BACKUP_DIR"

cd $BACKUP_DIR

for i in "${BACKUP_FILES[@]}"; do
   ARCHIVE_FILE="$i--$DATETIME.tar.gz"

   tar -czvf $ARCHIVE_FILE --absolute-names $BACKUP_SYNC_FOLDER/$i
done

chown -R root:root *--$DATETIME.tar.gz
chmod -R 0400 *--$DATETIME.tar.gz

## Remove backups

DBDELDATE=`date +$DATEFORMAT_FOLDER --date="${BACKUP_RETAIN_DAYS} days ago"`

if [ ! -z ${BACKUP_BASE_DIR} ]; then
      cd ${BACKUP_BASE_DIR}
      if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
            rm -rf ${DBDELDATE}
      fi
fi