#!/bin/sh
#============================================================================
# File:         DV_MySQL_backup.sh
# Description:  backup individual table backup from each database.
# Usage:        sh DV_MySQL_backup.sh >DV_MySQL_backup.log 2>DV_MySQL_backup.err
#============================================================================

### MySQL Setup ###
##BKP_DATE=$(date ‘+%m%d%Y’)
BKP_DATE=$(date ‘+%u%H’)  ## current format of backup day/time
HOST=`hostname`
MUSER=”xxxxx”
MPASS=”xxxxx”
BACKUP_DIR=”/mnt/backup/” #${HOST}-${BKP_DATE}
DNAME=${HOST}-${BKP_DATE}
MYSQLDUMP=”/usr/local/mysql/bin/mysqldump”
MYSQL=”/usr/local/mysql/bin/mysql”

cd $BACKUP_DIR
#mkdir ${HOST}-${BKP_DATE}
echo “starting backup job at `date ‘+%m%d%Y %H:%M %Z’` ”

### Get all databases name ###
###$MYSQL -u$MUSER -p$MPASS –skip-column-names -e “show databases” |grep -v “information_schema” |while read db
DATABASES=`$MYSQL -u$MUSER -p$MPASS –skip-column-names -e “show databases”`
for db in $DATABASES
do
### Create dir for each databases(DUE TO FILESYSTEM BUGS), backup tables in individual files ###
#echo ${BACKUP_DIR}
#echo ${BACKUP_DIR}/${db}
mkdir -p -m 777 ${BACKUP_DIR}/$DNAME
cd ${BACKUP_DIR}/$DNAME
mkdir -m 777 ${db}

#### directory creation is done.Proceed with backup
echo “  ”
echo “starting backup of database — $db”
echo “————————————-”
###$MYSQL -u $MUSER -p$MPASS $db -e “show tables” |grep -v “Tables_in_”  |while read table
TABLES=`$MYSQL -u $MUSER -p$MPASS $db -e “show tables” |grep -v “Tables_in_”`
for table in $TABLES
do
FILE=$BACKUP_DIR/$DNAME/$db/$table.sql.gz
echo $table;
## for schema,data/routines  make sure we backup mysql database also.
$MYSQLDUMP -q –skip-triggers –single-transaction -u $MUSER -p$MPASS $db $table | gzip > $FILE  ##dump only table schema and table data
done
## to backup routines/triggers for each db.
ROUTINE=$BACKUP_DIR/$DNAME/${db}/${db}_routines_triggers.sql.gz
$MYSQLDUMP -t -d -q –allow-keywords -R -u $MUSER -p$MPASS $db $table | gzip > $ROUTINE        ##triggers are enabled by default
echo “completed the backup of database — $db”
done

echo “##################################”
echo “backup job completed at `date ‘+%m%d%Y %H:%M %Z’` ”
echo “All database backup complted”
echo ” ”
echo “Deleting old backups”
rm -rf ${BACKUP_DIR}/${DNAME}.tar.bz2
echo “Compressing the backupfiles”
##create   bzipped   tar  archive  of  the  directory
cd ${BACKUP_DIR}
tar -cjf ${DNAME}.tar.bz2 ${DNAME}
##tar -cvf – <directory name> | gzip -c > <directory name>.tar.gz
echo “backup archiving is done.plz verify the files”
ls -lhtr ${BACKUP_DIR}/${DNAME}.tar.bz2
rm -rf ${BACKUP_DIR}/${DNAME}
## done with backup