MySQL Backup Script

1. Login to Server
2. Goto /opt/
3. mkdir backup
4. touch password and in this file paste mysql root password.


#!/bin/sh

#############################################################
# File:         dbbackup.sh
# Description:  A shell script to backup all mysql databases
# Cron Example: 0 2 * * * sh /path/to/dbbackup.sh
#############################################################

####DEFINE THESE#############################################
USER="root"
PASSWORD=`cat /opt/backup/password`
SCPTOREMOTE=0;
REMOTESERVER="192.168.0.0";
REMOTEDIR="/backup";
DUMPBASEDIR="/opt/backup";
####END CHANGE HERE###########################################

MYSQLDUMP=`which mysqldump`
MYSQL=`which mysql`
LOOK=`which find`
DATE=`/bin/date +%d-%b-%Y`;
DELDIR=`/bin/date -d "-7 day" +%d-%b-%Y`;
DUMPDIR="$DUMPBASEDIR/$DATE";
DISKFREE=`df -h | grep -v Filesystem|grep '/dev/xvda1' | awk '{print $5}'| sed 's/%//g'`

if [ $DISKFREE -le "75" ]
then

    echo "Free Space on Drive...Starting Backups"

    if [ ! -d $DUMPDIR ];
    then
            echo "Creating Directory: ".$DUMPDIR;
            mkdir $DUMPDIR;
    fi

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    echo "Database $db Backup Started";
    $MYSQLDUMP --single-transaction --events --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$DUMPDIR/$db.bak"
    echo "Database $db Backup Completed";
done

#################################################################
tar -cvzf $DUMPBASEDIR/$DATE.tar.gz $DUMPBASEDIR/$DATE
rm -rf $DUMPBASEDIR/$DATE
mkdir -p $DUMPBASEDIR/$DATE
mv $DUMPBASEDIR/$DATE.tar.gz $DUMPBASEDIR/$DATE/
#################################################################

    if [ $SCPTOREMOTE -eq "1" ]
    then
            echo "Backing up data to remote server";
            scp -r $DUMPDIR $REMOTESERVER:$REMOTEDIR
    fi

        echo "Done";

    if [ ! -d $DELDIR ];
    then
        echo "No directory with name: $DELDIR"
        echo "exiting....."
        exit 1;
    else
   
        echo "Deleting Backup Of Date : $DELDIR"
                $LOOK $DUMPBASEDIR -type d -name $DELDIR -exec rm -rf {} \; 2>/dev/null
                find /opt/backup/ -iname "$(date +%A)*.tar.gz" -mtime +10 -exec rm -rf {} \;
              echo "Backup Deleted Successfully...!"
        exit 0;
    fi

fi

exit;



:wq

=========================



[root@ip-172-31-3-161 backup]# crontab -l
0 0 * * * /bin/bash /opt/backup/mysql-bak-rotation-weekly.sh >> /opt/backup/mysql-bak-rotation-weekly.log