Scripting to backup mySQL databases

rakkad
rakkad used Ask the Experts™
on
Assistance in writing a script that will:-

a) backup mySQL databases at 00:01, together a log to state that backups are completed successfully or failed and and e-mail notification is sent out
b) mySQL databases are kept for 7days before they are archived
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Prabhin MPDevOps Engineer
Distinguished Expert 2018
Commented:
use the below script

nowdate=$(date +"%Y-%m-%d-%H")
dayago=`(date --date='7 days ago' '+%Y-%m-%d-%H')`
months=$(date +"%Y-%m")
days=$(date +"%d")
daysago=`(date --date='1 days ago' '+%d')`
username=youruser
host=
password=yourpassword
mkdir -p /backup/mysql-backup/$months/$days/
cd /backup/mysql-backup/$months/$days/
dbs=`mysql -h zurichdevrds.ctno4kxs9e0b.ap-southeast-1.rds.amazonaws.com -uroot -p$password -e "show databases"`
for db in $dbs
do
echo "==========$db ========"
mysqldump -h $host -C $db -uroot -p$password > ${db}-$nowdate.sql
done
echo $?
#Email Notification
if [ "$?" = "0" ]; then
        echo "Backup Process was Successful. A new backup file $nowdate.sql.gz has been created" | mailx -s "backup has been successful" your-email@domain.com
         echo "Backup Process Failed. Please contact System Administrator" | mailx -s "backup has been failed, " your-email@domain.com
        exit 1
fi

gzip *.sql
rm -rf /backup/mysql-backup/$months/$dayago
Database Consultant
Commented:
If might want to consider using the Holland Framework. Here are the holland setup steps. You can provide details there and have an after-backup (success/failure)  script to send the email.

Update:
If you're willing to stick to the mysqldump / logical backup then you might want to use this backup script.

Author

Commented:
Both solutions have been helpful and I appreciated your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial