Create directories

_$: for database in <database 1> <database 2> <database N>
do
    mkdir -p /srv/backup/databases/$database
done
_$: chown root:backupgroup       /srv/backup/databases
_$: chown backupuser:backupgroup /srv/backup/databases/guid

Backups

_$: mkdir -p /root/cron
/root/cron/database-backup.sh:
------------------------------
backup_database() {

	# Arguments: user, pass, database, host, folder
	#  -user: Database user
	#  -password: Database password
	#  -database: The database to back up
	#  -host: Where the database is
	#  -folder: Where to save the database


	# Daily copy
	copy=$3"-"`date +%y%m%d`

	# Dump the database
	DUMP_FOLDER="/srv/backup/databases"
	/usr/bin/mysqldump -u $1 -p$2 $3 -h $4 > ${DUMP_FOLDER}/mysql-$copy
	if [ $? -eq 0 ]
	then
		# Compress the database
		/usr/bin/7za a ${DUMP_FOLDER}/mysql-$copy.7z ${DUMP_FOLDER}/mysql-$copy -p...
		rm ${DUMP_FOLDER}/mysql-$copy
		mv ${DUMP_FOLDER}/mysql-$copy.7z /srv/sqlbackup/$5
	fi
}


# Local databases
#################

printf "========================\n"
printf "Backing up database_1 ...\n"
backup_database "admin" "..." "database_1" "localhost" "database_1"


# Remote databases
##################

printf "=====================\n"
printf "Backing up database_2 ...\n"
backup_database "admin" "..." "database_2" "database.example.com" "database_2"

Delete old backups

/root/cron/database-clean.sh:
-----------------------------
BACKUPPATH="/srv/backup/databases"

delete_older_than() {
    # Arguments: path and age (in days)
    if [ $# -ne 2 ]
    then
        printf "Not enough arguments\n"
        exit 1
    else
        printf "Deleting all files older than $2 days in $1\n"
        find $1 -type f -mtime +$2 -exec echo {} \;
        find $1 -type f -mtime +$2 -exec rm {} \;
    fi
}

# Delete old files
delete_older_than ${BACKUPPATH}/database_1  30
delete_older_than ${BACKUPPATH}/database_2  60

Add to crontab

We could add our backup procedures to /etc/cron.daily o /etc/cron.hourly but since they contain passwords it is better to leave them in a safe directory and add the corresponding crontab entries:

MAILTO="user@example.com"
00  09  *   *   *     /root/cron/database-backup.sh
15  09  *   *   *     /root/cron/database-clean.sh