Database Backup script for MariaDB/MySQL

.my.cnf

# MySQL/mariaDB secure authorization file.
#
# To not have to put the password as an argument to the mysql commands, i.e. we don't want to
# use the -p option. Instead use a .my.cnf file in the home dir of the user you are running this 
# script as, i.e. /root/.my.cnf if running as root. This way you can securely run mysqldump as 
# root without the password being exposed in the process list. First create a .my.cnf in the home 
# dir of root and make sure only root can read or change it by running the following three commands:
#
sudo touch /root/.my.cnf
sudo chown 0:0 /root/.my.cnf
sudo chmod 600 /root/.my.cnf
#
# It should contain something like mine:
# cat /root/.my.cnf
[mysql]
user=root
password=something

[mysqldump]
user=root
password=something

[client]
user=root
password=something

database_Backups.sh

#!/bin/bash
#
#
# This script will backup all databases except for those excluded by egrep -v 'database1|database2'
#
# This script assumes that you have ssh-keys setup between the database server and the offsite
# storage server and a secure authorization file.
#
# To not have to put the password as an argument to the mysql commands, i.e. we don't want to
# use the -p option. Instead use a .my.cnf file in the home dir of the user you are running this 
# script as, i.e. /root/.my.cnf if running as root. This way you can securely run mysqldump as 
# root without the password being exposed in the process list. First create a .my.cnf in the home 
# dir of root and make sure only root can read or change it by running the following 
#
# three commands:
# 'sudo touch /root/.my.cnf'
# 'sudo chown 0:0 /root/.my.cnf'
# 'sudo chmod 600 /root/.my.cnf'
#
# then make sure it contains something like this:
# cat /root/.my.cnf
# [mysql]
# user=root
# password=something
#
# [mysqldump]
# user=root
# password=something
#
#[client]
# user=root
# password=something

# Database Server Name
dbsrv=$(hostname)

# Offsite storage location
offsite_srv=servername or IP# server FQDN or IP address to store a redundent of the backups

# Directory to which backup files will be written. Should end with slash ("/").
backups_dir="/data/bkup/db/"  # temp or working director
backups_sql="/data/bkup/sql/" # directory where backups are stored
backups_archive="/data/bkup/archive/" # directory where archive copies are stored & offsite copies come from
offsite_dir="/data/bkup/" # directory on remote server where archive copies are uploaded to

# Number of days to retain copies on the local server
backup_retention=7

# Date/time included in the file names of the database backup files.
todaysDate=$(date +'%Y%m%d')

umask 007

renice 10 $$ >/dev/null

echo "##########        Backing up MariaDB/MySQL databases"
echo "  "
# The databases performance_schema and information_schema are not being backuped.  You can exclude
# other databases by adding them in like 'performance_schema|information_schema|another_database'
cd $backups_db
for db in `mysql --batch --skip-column-names --execute="SHOW DATABASES" | egrep -v 'performance_schema|information_schema'`; do
                /usr/bin/nice /usr/bin/mysqldump --events --ignore-table=mysql.event --single-transaction --quick --extended-insert "$db" > $backups_dir$db.sql | echo   "##########        Backup successfully compl
eted for  " $db 
        done
echo "  "
echo "##########        All databases have been successfully copied" 
echo "  "
echo "##########        Compressing files individually"
# Compresses all files that are not already compressed
for dbSQL in *.sql; do
                bzip2 -v $backups_dir$dbSQL
        done
echo "  "
echo "##########        Renaming files w/"$todaysDate"_ as prefix"
cd $backups_dir
for dsSQL in *.bz2; do
        mv $dsSQL "$todaysDate""_"$dsSQL
done
echo "  "
echo "##########        All files have been compressed and renamed"
# Making date-stamped directory for storage and moving files 

echo "  "
echo "##########        Creating" $backups_sql$todaysDate "folder"
mkdir $backups_sql$todaysDate

echo "  "
echo "##########        Moving files from" $backups_dir "to" $backups_dir$todaysDate
mv $backups_dir* $backups_sql$todaysDate

# Making single tar archive for offsite storage in archive directory
echo "  "
echo "##########        Archiving today's backup to" $backups_sql$todaysDate
tar -cvf $backups_archive"$todaysDate""_database.tar" $backups_sql$todaysDate 

# Uses rsync via ssh to upload a copy of the database backups
echo "  "
echo "##########        Uploading to offsite storage on" $offsite_srv
ssh $offsite_srv "mkdir -p /data/bkup/$dbsrv"
rsync -avzPe 'ssh -p 22' $backups_archive"$todaysDate""_database.tar"  $offsite_srv:$offsite_dir$dbsrv
echo "##########        "$backups_archive"$todaysDate""_database.tar" "has been seccussfully uploaded"

# This will remove all local files that are older than 10 days
echo "  "
echo "##########        Removing local backups that more than " $backup_retention "days old"
/usr/bin/find $backups_sql* -maxdepth 0  -type d -mtime +$backup_retention -exec rm -rf {} \;