Mysql backup databases separate files

Mysql backup databases separate files

#!/bin/bash

################################################
# Backup and compress all MySQL databases in separate files.
# Furthermore the script will create a folder with the current time stamp,
# send a reporting email and set a time retention policy.
# NOTE: MySQL, gzip and sharutils installed on the system
# and you will need write permissions in the directory where you executing this script
################################################

TEMPFILE=`mktemp -p /tmp/`
trap "rm -f $TEMPFILE; exit 1" INT
HOSTNAME=`hostname`
IP=`ifconfig  | grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}'`;
SUBJECT="{BACKUP DB}: Backup giornaliero dei database (${HOSTNAME})"
MAILING_LIST='youremail@domain.com'
RETENTION='+15'
# MySQL Path
MYSQL_PATH='/opt/mysql-5.6.20/bin'
# MySQL User
USER='user'
# MySQL Password
PASSWORD='password'
# Backup Directory - NO TAILING SLASH!
OUTPUT='/your/backup/dir'
OPTS='--single-transaction --opt -c'
 
TIMESTAMP=`date +%Y%m%d`;
mkdir $OUTPUT/$TIMESTAMP;
cd $OUTPUT/$TIMESTAMP;
echo -n `date` > ${TEMPFILE}
echo " - Starting MySQL Backup" >> ${TEMPFILE}
databases=`${MYSQL_PATH}/mysql --user=${USER} --password=${PASSWORD} -e "SHOW DATABASES;" 2> /dev/nul | tr -d "| " | grep -v Database`
for db in ${databases}; do
    if [[ "${db}" != "information_schema" ]] && [[ "${db}" != _* ]] ; then
        echo -ne "Dumping database ${db}... " >> ${TEMPFILE}
        ${MYSQL_PATH}/mysqldump ${OPTS} --user=$USER --password=$PASSWORD --databases $db > $TIMESTAMP-$db.sql 2> /dev/null
        gzip $TIMESTAMP-$db.sql
        echo 'done!' >> ${TEMPFILE}
    fi
done
echo -n `date` >> ${TEMPFILE}
echo -e " - Finished MySQL Backup" >> ${TEMPFILE}

echo "=============================================================" >> "${TEMPFILE}"
echo "Retention: ${RETENTION}" >> "${TEMPFILE}"
pwd >> "${TEMPFILE}"
ls -lth >> "${TEMPFILE}"
echo "=============================================================" >> "${TEMPFILE}"

cat ${TEMPFILE}

echo -ne "Sending email REPORT to ${MAILING_LIST}... "
cat "${TEMPFILE}" | mailx -s "${SUBJECT}" "${MAILING_LIST}" && echo 'done!'

/usr/bin/find ${OUTPUT} -mtime ${RETENTION} -exec rm -rf {} \; >/dev/null 2>&1

rm -f ${TEMPFILE}

exit 0

The reporting output:

gio 16 ott 2014, 10.48.55, CEST - Starting MySQL Backup
Dumping database database_1... done!
Dumping database database_2... done!
Dumping database database_3... done!
...
Dumping database database_n... done!
gio 16 ott 2014, 10.49.04, CEST - Finished MySQL Backup
=============================================================
Retention: +15
/your/backup/dir/20141016
totale 14M
-rw-r--r-- 1 root root 931K ott 16 10:49 20141016-database_1.sql.gz
-rw-r--r-- 1 root root  526 ott 16 10:49 20141016-database_2.sql.gz
-rw-r--r-- 1 root root 196K ott 16 10:49 20141016-database_3.sql.gz
...
-rw-r--r-- 1 root root 1,5K ott 16 10:48 20141016-database_n.sql.gz
=============================================================
Sending email REPORT to youremail@domain.com... done!
Article By :