MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_USER='root'
MYSQL_PASSWORD='xxxxxxx'
BACKUP_RETAIN_DAYS=90 ## Number of days to keep local backup copy
TODAY=`date +%d-%b-%Y-%H:%M`
LOGS=/nfsc/mysql/logs/mysql_backup_${TODAY}.out
echo >> ${LOGS}
echo ${TODAY} >> ${LOGS}
schema=`mysql -h ${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -N -e "SHOW DATABASES;" | grep -v information_schema | grep -v performance_schema | grep -v sys | grep -v mysql | awk {'print $0'}`
DB_BACKUP_PATH='/path/backup/'`date +\%Y-\%m-\%d`'-daily'
if ! mkdir -p ${DB_BACKUP_PATH}; then
echo "Cannot create backup directory in ${DB_BACKUP_PATH}." >> ${LOGS}
exit 1;
fi;
for d in ${schema[@]}
do
DATABASE_NAME=${d}
mkdir -p ${DB_BACKUP_PATH}/${DATABASE_NAME}
echo "Backup started for database - ${DATABASE_NAME}" >> ${LOGS}
mysqldump -h ${MYSQL_HOST} \
-P ${MYSQL_PORT} \
-u ${MYSQL_USER} \
-p${MYSQL_PASSWORD} \
${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${d}/${DATABASE_NAME}-${TODAY}.sql.gz
if [ $? -eq 0 ]; then
echo "Database backup successfully completed" >> ${LOGS}
else
echo "Error found during backup" >> ${LOGS}
exit 1
fi
done
##### Remove backups older than {BACKUP_RETAIN_DAYS} days #####
DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"`
if [ ! -z ${DB_BACKUP_PATH} ]; then
cd ${DB_BACKUP_PATH}
if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
rm -rf ${DBDELDATE}
fi
fi
#extract and copy to bucket
tar cvf /path/backup/backup-$TODAY-daily.tar.gz $DB_BACKUP_PATH
/usr/local/bin/aws s3 cp /path/backup/backup-$TODAY-daily.tar.gz s3://dbbackup-uat
rm -rf /path/backup/backup-$TODAY-daily.tar.gz
### End of script ####
