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!
Share This