Shell Script For Take Backup of Mysql Databases

Step 1: Create Directory For Backups Scripts & Backups

# mkdir -p /backups/db_backup /backups/scripts

Step 2: Create Backups Scripts

# vi /backups/scripts/db_backups.sh

#!/bin/bash
export path1=/backups/db_backup ### Backup Path
date1=`date +%y%m%d_%H%M%S`
/usr/bin/find /backups/db_backup/* -type d -mtime +14 -exec rm -r {} \; 2> /dev/null ### Retention For Backups of 15 Days
cd $path1/
mkdir $date1
USER="root" ## Mysql User Name
PASSWORD="redhat" ## Mysql User's Password
OUTPUTDIR="$path1/$date1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
HOST="localhost"
databases=`$MYSQL --user=$USER --password=$PASSWORD --host=$HOST \
-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
echo "` for db in $databases; do
echo $db

if [ "$db" = "performance_schema" ] ; then
$MYSQLDUMP --force --opt --single-transaction --lock-tables=false --skip-events --user=$USER --password=$PASSWORD --host=$HOST --routines \
--databases $db | gzip > "$OUTPUTDIR/$db.gz"
else

$MYSQLDUMP --force --opt --single-transaction --lock-tables=false --events --user=$USER --password=$PASSWORD --host=$HOST --routines \
--databases $db | gzip > "$OUTPUTDIR/$db.gz"
fi
done `"
/root/monitor.sh 99 | tee /root/backup_report >> /root/monthly_backup_report #to create a backup report in /root/backup_report file
cat /root/backup_report | mail -s "backup_report of Test Server (192.168.72.240) " -r root@Test_Server deb.mind009@gmail.com ### Mail the Backup Report But Need Monitoring Script for this Feature

--- save and quit (:wq) ---

Step 3: Schedule Backup Scripts on Crontab

## Then Schedule the script to Crontab for Run it As per a schedule, Here we are scheduling it Daily Basis at 12:00 AM

# crontab -e

0 0 * * * /backups/scripts/db_backups.sh > /dev/null

--- save and quit (:wq) ---

# crontab -l (To see the Scheduled Crontab List)

Thanks For Visiting on My Blog, For More Tutorials Keep Visiting My Blog.