Linux shell scripting for mysql database backup over rsync connection

                                                                                
 ,d88b.    ,                                                        ,d88b.    , 
'    `Y88P'  `Yb    dP' .d888b.  .dP""Yb  `Yb d88b d88b  .dP'  dP' '    `Y88P'  
 ,d88b.    ,   Yb  dP   8'   `Yb       Yb  88P   8Y   8b 88    88   ,d88b.    , 
'    `Y88P'     YbdP    Yb.   88       dP  88    8P   88 Y8   .88  '    `Y88P'  
                .8P         .dP  `YbwwdP  .8P  bdP  bdP  `Y88P'88               
              dP'  b      .dP'                                 88               
              Y.  ,P    .dP'                                   88               

               `""'                                            Y8.              
Backup database is a important task for system administrator. Database files backup are helpfull when there has data/file corruption, damage, etc. Backing up database is a task that must to do. Mysqldump is a popular tool to backup  databases in mysql. I make a bash script to backup mysql databases using mysqldump and rsync. Rsync is usefull tool to send file across network in fast transfer.


The script bellow will dump a mysql database. Then the dump file will send to a specified server over RSYNC connection. The destination server is listening a RSYNC service. The dump file after received by destination server will passed to a destination directory as specified by RSYNC configuration. 

It's just a shell script to dump and send the mysql dump file, the script for rsync connection will not be provided here :)

Here is my shell script :

#!/bin/bash                                                              
#                                                                        
# Dump database in instant,                                                 
# Then dumped file send to your specified remote server                  
# Author: Suwardi - line.console49@gmail.com                              
# URL: www.0x41.tk                                                          
                                                                         
TODAY=`date +%A-%F`                                                      
DAY=`date +%A`                                                            
DB_NAME=my_sample_db                                                      
TABLE_NAME=admin
DIR_DUMP=/backup/db_dump
DIR_DUMP_LOG=/backup/cce/db_dump/log
FILE_MAIN_LOG=/backup/cce/db_dump/log/dump_main.log
FILE_ERR_LOG=/backup/cce/db_dump/log/dump_err.log
FILE_DUMP_PATH=$DIR_DUMP/dbcce-$TODAY.sql
RSYNC_SCRIPT=/home/cce/backup_site_lawas/our_scripts/rsync-dt.sh
RSYNC_SWEVEL=/home/cce/backup_site_lawas/our_scripts/rsync-swevel.sh
DUMPED=


MYSQL="mysql"
MYSQLDUMP="mysqldump"
DBHOST="localhost"
DBUSER="cce"
DBPASS="!QAZxsw2"

WHICH=which
CD=`${TOUCH} cd`
TAR=`${WHICH} tar`
BZIP2=`${WHICH} bzip2`
REMOVE=`${WHICH} rm`
CHOWN=`${WHICH} chown`
CHMOD=`${CHMOD} chmod`
MKDIR=`${WHICH} mkdir`
TOUCH=`${WHICH} touch`
SH=`${WHICH} sh`

# Main directory checking
#
if [ -d $DIR_DUMP ]; then
        echo " Dump directory exist in $DIR_DUMP"
else
        echo " Create main directory $DIR_DUMP"
        ${MKDIR} -p $DIR_DUMP
fi

if [ -d $DIR_DUMP_LOG ]; then
        echo " Dump directory exist in $DIR_DUMP_LOG"
else
        echo " Create main directory $DIR_DUMP_LOG"
        ${MKDIR} -p $DIR_DUMP_LOG
fi

# File dump log check
# Create it first if none, skip if exist
#

if [ -f $FILE_MAIN_LOG ]; then
        echo " Dump main log exist in $FILE_MAIN_LOG"
else
        echo " Creating log file $FILE_MAIN_LOG"
        ${TOUCH} $FILE_MAIN_LOG
        ${CHOWN} cce.cce $FILE_MAIN_LOG
        ${CHMOD} 740 $FILE_MAIN_LOG
       
        # Insert a title to main log file
        #
        echo "# Main log blablabla" >> $FILE_MAIN_LOG
        echo "" >> $FILE_MAIN_LOG
        echo "" >> $FILE_MAIN_LOG
fi
 
if [ -f $FILE_ERR_LOG ]; then
        echo " Dump error log exist in $FILE_ERR_LOG"
else
        echo " Create error log file $FILE_ERR_LOG"
        ${TOUCH} $FILE_ERR_LOG
        ${CHOWN} cce.cce $FILE_ERR_LOG
        ${CHMOD} 755 $FILE_ERR_LOG

        # Insert a title to error log file
        #
        echo "# Error log of dumping mysql database" >> $FILE_ERR_LOG
        echo "" >> $FILE_ERR_LOG
        echo "" >> $FILE_ERR_LOG
fi



# EOF