Create daily fail-resistence (slow) DB dumps.

backup-db.sh

#!/bin/bash

if [ -t 1 ]; then
	CGREEN="\033[0;32m"
	CYELLOW="\033[1;33m"
	CNONE="\033[0m"
fi

MYSQL_USER="root"
MYSQL_PASS=""

STORAGE="/var/backup/mysql"

while getopts "u:p:s:f:h" options; do
	case "$options" in
		u)
			MYSQL_USER=$OPTARG
			;;
		p)
			MYSQL_PASS=$OPTARG
			;;
		s)
			STORAGE=$OPTARG
			;;
		f)
			source $OPTARG
			;;
		h)
			echo 'usage: backup-db.sh -u $mysql_user -p $mysql_pass -s $local_backup_storage'
			echo '       backup-db.sh -f $config_file'
			exit 0
			;;
		?)
			echo 'try backup-db.sh -h'
			exit 1
			;;
	esac
done

# create the storage
mkdir -p "$STORAGE"

# search for databases and do backup
DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e 'SHOW DATABASES' </dev/null 2>/dev/null | grep -vE "Database|information_schema|performance_schema")
for DATABASE in $DATABASES; do
	echo -e " $CGREEN*$CNONE do backup database $CYELLOW$DATABASE$CNONE"
	mysqldump \
		-u$MYSQL_USER \
		-p$MYSQL_PASS \
		--databases $DATABASE \
		--skip-opt \
		--add-locks \
		--allow-keywords \
		--comments \
		--complete-insert \
		--create-options \
		--disable-keys \
		--dump-date \
		--hex-blob \
		--lock-tables \
		--routines \
		--triggers \
		--tz-utc \
		--result-file="$STORAGE/$DATABASE.sql"
done

# remove obsolete database dumps
echo -e " $CGREEN*$CNONE remove obsolete database dumps"
find "$STORAGE" -type f -name "*.sql" -mmin 720 -delete