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 a directory where script resides
mkdir /home1/script
cd /home1/script
touch dbbackup.sh

c. Login to MYSQL console and run below query

To Create readonly user:

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

Flush privileges;

d. Create a file dbbackup.sh

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