MYSQLDUMP SCRIPT

.................

======> DATABASE BACKUP SCRIPT <====



===> 1st step--- create a directory where the the backup will store:-

[root@ab ~] mkdir /backups

[root@ab backups] mkdir /backups/db_backup

====> 2nd step  create a directory where script will store:-


[root@ab] mkdir /backups/script

[root@ab /backup/script] vi db_backup.sh


and paste the following script here:-

#!/bin/bash
export path1=/backups/db_backup
date1=`date +%y%m%d_%H%M%S`
/usr/bin/find /backups/db_backup/* -type d -mtime +32 -exec rm -r {} \; 2> /dev/null
cd $path1/
mkdir $date1
USER="root"
PASSWORD="redhat"
OUTPUTDIR="$path1/$date1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
HOST="ab.kmi.com"
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 `"


======> 3th step  save and quit(:wq)

======> 4th step Give The Permission.


[root@ab /] chmod -Rf 777 /db_backup


======> 5th step Now Create a crontab

[root@ab /] crontab -e

34 11 * * * /backups/script/db_backup.sh >


save and quit(:wq)

==============================OUTPUT==========================

To view the task output go the backup location

[root@ab backups]# cd db_backup/
[root@ab db_backup]# ll
total 12
drwxr-xr-x. 2 root root 4096 Mar 20 12:05 150320_120543
drwxr-xr-x. 2 root root 4096 Mar 20 12:07 150320_120720
drwxr-xr-x. 2 root root 4096 Mar 20 12:08 150320_120749

[root@ab db_backup]#


.....................................................................