Script using mysqldump

cat backup_latest.sh
#!/bin/bash
#########################################################################################################################
#  This script is designed to Take backup of MySql Databases.
#  Author :
#  Description : Backup using mysqldump
#  Pre Req :
#  Usage   :
#  History : Created :
#########################################################################################################################
#########################################################################################################################
# Reserved for History and updates
#
#
#
#
#
#########################################################################################################################
DATE=`date +%d%m%y_%H%M`
mkdir -p $HOME/scripts
chmod -R 755 $HOME/scripts
. $HOME/scripts/newvariables.env
PATH=$MYSQL_HOME/bin:$PATH:.
IP=`/sbin/ifconfig | grep inet | head -1 | awk -F ":" '{print $2}' | awk '{print $1}'`
HOST=`hostname`
STMAIL="abc@domain.com"
mkdir -p ${DUMP_DIR}
mkdir -p ${DUMP_DIR}/${DATE}
mkdir -p ${LOGDIR}
CHKSOCK=`ls -ltr $SOCK|grep -iv such|wc -l`
CR_DUMP_DIR="${DUMP_DIR}/${DATE}"
LOG_FILE="${LOGDIR}/Jio_mysql_backup_${DATE}.log"
touch $LOG_FILE
  if [ "${BKP_SERVER}" = "" ] ; then
    echo "ERROR: 'BKP_SERVER' field is null.  Exiting..." >> $LOG_FILE
    exit 1
  elif [ "${USR}" = "" ] ; then
    echo "ERROR: 'USR' field is null.  Exiting..." >> $LOG_FILE
        exit 1
  elif [ "${PWD}" = "" ] ; then
    echo "ERROR: 'Password' field is null.  Exiting..." >> $LOG_FILE
        exit 1
  elif [ "${USR}" = "" ] ; then
    echo "ERROR: 'USR' field is null.  Exiting..." >> $LOG_FILE
        exit 1
 elif [ ! -d "$DUMP_DIR" ]; then
    echo "ERROR: 'DUMP_DIR: ' does not exist. Exiting..." >> $LOG_FILE
        exit 1
 elif [ ! -d "$LOGDIR" ]; then
    echo "ERROR: 'LOGDIR: ' does not exist. Exiting..." >> $LOG_FILE
        exit 1
 elif [ $CHKSOCK -le 0 ]; then
        echo "Script started at ${DATE}">>${LOG_FILE}
        DBLIST=`$MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -e "show databases" | grep -vE '^Databa|schema$|^test|found$'`
        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -e "show databases"| grep -vE '^Databa|schema$|^test|found$' >> ${LOG_FILE}
        for db in ${DBLIST}
        do
        START_DATE=`date +%d%m%y_%H%M`
        echo "******************************************************************" >> ${LOG_FILE}
        echo "Starting Backup of $db at $START_DATE" >> ${LOG_FILE}
        $MYSQL_HOME/bin/mysqldump -u${USR} -p${PWD} --single-transaction --triggers --routines --events $db > ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        echo "DUMP_FILE" for $db is ${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz >> ${LOG_FILE}
        STATUS=`tail -100 ${CR_DUMP_DIR}/${db}-${START_DATE}.sql|grep "Dump completed"|wc -l`
        END_DATE=`date +%d%m%y_%H%M`
        gzip ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        DUMP="${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz"
                if [ $STATUS -ge 1 ];
                        then
                        echo "Database $db backed up successfully at $END_DATE ">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Success');"
                else
                        echo "Backup of database $db failed!!! Kindly check">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Failed');"
                fi
        done
elif [ $CHKSOCK -ge 1 ]; then
        echo "Script started at ${DATE}">>${LOG_FILE}
        DBLIST=`$MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -S$SOCK -e "show databases" | grep -vE '^Databa|schema$|^test|found$'`
        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -S$SOCK -e "show databases" >> ${LOG_FILE}
        for db in ${DBLIST}
        do
        START_DATE=`date +%d%m%y_%H%M`
        echo "******************************************************************" >> ${LOG_FILE}
        echo "Starting Backup of $db at $START_DATE" >> ${LOG_FILE}
        $MYSQL_HOME/bin/mysqldump -u${USR} -p${PWD} -S$SOCK --single-transaction --triggers --routines --events $db > ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        echo "DUMP_FILE" for $db is ${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz >> ${LOG_FILE}
        STATUS=`tail -100 ${CR_DUMP_DIR}/${db}-${START_DATE}.sql|grep "Dump completed"|wc -l`
        END_DATE=`date +%d%m%y_%H%M`
        gzip ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        DUMP="${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz"
                if [ $STATUS -ge 1 ];
                        then
                        echo "Database $db backed up successfully at $END_DATE ">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Success');"
                else
                        echo "Backup of database $db failed!!! Kindly check">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Failed');"
                fi
        done
  fi
echo "CHECKING SUCESS/FAILURE" >> ${LOG_FILE}
TOTAL=`echo $DBLIST|wc -w`
SUCESS=`cat ${LOG_FILE}|grep -i successfully|wc -l`
FAILURE=`cat ${LOG_FILE}|grep -i failed|wc -l`
echo "TOTAL=$TOTAL,SUCCESS=$SUCESS,FAILED=$FAILURE" >> ${LOG_FILE}
mailx -s "MYSQL BACKUP(${IP}),TOTAL=$TOTAL,SUCCESS=$SUCESS,FAILED=$FAILURE" -r alert@mydomain.com $STMAIL < ${LOG_FILE}
find ${LOGDIR}/*.log -maxdepth 1 -type f -mtime +60 -exec rm -r "{}" \;
find ${DUMP_DIR}/* -maxdepth 1 -type d -mtime +60 -exec rm -r "{}" \;




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


cat newvariables.env
MYSQL_HOME=/mysqldata/mysqlbinary/mysql
LOGDIR=/mysqlbackup/output
DUMP_DIR=/mysqlbackup/dump
SOCK='/mysqldata/data/mysqld.sock'
USR='backupuser'
PWD='somepassword'
BKP_SERVER=''