SCRIPT TO TAKE MYSQL DATABASE BACKUP FOR LAST 7 DAYS

 a. create a directory for database backup and script:

     mkdir /home1/db_backup

b. Create Directory and file:
     mkdir /home1/script
     cd /home1/script
     touch dbbackup.sh

c. Login to MYSQL console and run below commands
   To Create read only user:

GRANT LOCK TABLES, SELECT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'readonly';
flush privileges;

d. vi dbbackup.sh
Copy Below Script

#!/bin/sh
#For taking backup
DIR=/home/db_backup/
DATESTAMP=$(date +%d-%m-%y-%H-%M)
DB_USER=backup
DB_PASS='readonly'
HOST=localhost
# remove backups older than $DAYS_KEEP
DAYS_KEEP=7
find ${DIR}* -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null
# create backups securely
umask 006
# list MySQL databases and dump each
DB_LIST=`mysql -h $HOST -u $DB_USER -p"$DB_PASS" -e'show databases;'`
DB_LIST=${DB_LIST##Database}
for DB in $DB_LIST;
do
FILENAME=${DIR}${DB}-${DATESTAMP}.sql.gz
mysqldump -h $HOST -u $DB_USER -p"$DB_PASS" $DB --single-transaction | gzip > $FILENAME
done

e. Cron for DB backup:
Run below command to run script 8:00 AM everyday
crontab -e
0 8 * * * bash -l /home/script/dbbackup.sh

f. Enjoy !!!