MySQL - Sample backup script

Platform - Linux

Sample script to backup MySQL DBs

#!/bin/bash

# Pls change these
db_host='localhost'
db_user='backupuser'
db_user='secretpassword'
#
# BACKUP_DIR - Need to change as per HOST/DB environment
#
BACKUP_DIR="/backups/`hostname -s`/mysql"

if [ ! -d $BACKUP_DIR ] ; then
mkdir -p $BACKUP_DIR
chmod 700 $BACKUP_DIR
fi

# Pls donot touch below code

DATE=`date +"%Y%m%d"`
MYSQLDUMP="$(which mysqldump)"
LOG_FILE=${BACKUP_DIR}/runlog${DATE}.log
MYSQL="$(which mysql)"

DB_LIST="$($MYSQL -u$db_user -h$db_host -p$db_pass -Bse 'show databases')"

exec 2> ${LOG_FILE}

MYSQLDUMP_OPT="${MYSQLDUMP} -u${db_user} -p${db_pass} -h${db_host} --opt"

for DB_NAME in ${DB_LIST} ; do

if [ ! -d ${BACKUP_DIR}/${DB_NAME} ] ; then
mkdir -p ${BACKUP_DIR}/${DB_NAME}
fi

${MYSQLDUMP_OPT} ${DB_NAME} | gzip -9 > ${BACKUP_DIR}/${DB_NAME}/${DATE}.dmp.gz
done

if [ -s ${LOG_FILE} ]; then

mail -s "Alert- Backup failure `hostname -s` " me@hostname.com < ${LOG_FILE} else #Cleanup process..Delete backup files which are one week old find $BACKUP_DIR -type f -name "*.dmp.gz" -mtime +7 -exec rm {} \; mail -s "Notification - Backup done `hostname -s` " me@hostname.com < ${LOG_FILE}
fi