Main menu

MySQl enhanced backup script

I have tried many ways to backup and restore the MySQL database and it’s quite easy. I wanted a way to backup all database in individual files instead mysqldump –all-databaes option in which create a single file for all databases.  I was working on a script and find that many scripts has already created for it then why reinvent the wheel. But for my specific requirement i created one..

  • Only need to configure database setting rest all configuration done my Script.
  • Database retention is already configured in script which is 3 days default and you can change it.
  • You can ignore databases which you don’t want to backup.
  • mysqldump query is optimized for large and online databases.
  • Backup is compressed by gzip to save disk space.

Note: It will create a directory in /home/BackupAdmin to store database backup.

#!/bin/bash
##################################################
# Author: Zolan
# License: GPL 1.2 (C) sector19.net

# Function to check backup directory exist or not
function checkDir_Exist()
{
  if [ ! -d $1 ] 
  then
    echo "Creating directory... "
    mkdir -p $1
  else
    echo "starting the backup ..."
  fi
}
Backup_Path="/home/BackupAdmin/MySQL"
checkDir_Exist $Backup_Path
Date=`date +_%d%b%y_%H-%I`
BackupDIR=$Backup_Path/$Backup_Date
checkDir_Exist $BackupDIR
#
#Backup Mysql Databases
#
MyUSER="root" # USERNAME
MyPASS="Desk#87g6" # PASSWORD
MyHOST="localhost" # Hostname

#Define the executables path
#
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# DO NOT BACKUP these databases
IgnoreDB="information_schema performance_schema test mysql"
     
# Getting list of databases
#
echo "Getting List of Databases"
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
     
for db in $DBS
do
  backupDB=yes
  if [ "$IgnoreDB" != "" ];
  then
  for i in $IgnoreDB
    do
    if [ "$db" == "$i" ]; then 
            backupDB="NO";        
            echo "Backup Database ... $i ...! Ignored";
        fi
    done
  fi
     
  if [ "$backupDB" == "yes" ] ; then
  bakName="$BackupDIR/$db.gz"
  echo -n "Backup database ... $db ..."
  $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS --opt --single-transaction  --add-drop-database --max_allowed_packet=1G $db | $GZIP -9 > $bakName
  echo " ! Done"
  fi
done

# Removing backups older then 3 days 
find $Backup_Path  -type d  -mtime +3 -exec rm -rf {} \;


FacebookTwitterGoogle+RSS