Activities

July 2012
M T W T F S S
« Jun   Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

FTP enabled MySQL backup script

This is a small script which would help you to setup automated offshore database backup. This script will do the following things,

1. Send email alert once the ftp connection fails. I uses the third party program “SendEmail” to do this.
2. Take the full database backup.
3. Retain recent coy on local disk.
4. Upload the backup to remote ftp location.

You may get the script download from here

#!/bin/bash
# Shell script to backup Fmi database
# To backup  databases file to /var/sqlbackup dir and later upload to ftp
# ------------- Varible declaration --------

############# FTP access ################  
FTP_SERVER="server name"        # FTP server name
FTP_USER="user"                 # FTP user name
FTP_PASSWORD="pwd"              # ftp password
FTP_REMOTE_LOC="loc"            # ftp remote location eg "/mydomain.com/database"
                    #
###### SMTP Account #####################
ALERT_MAIL_USER="asd@asd.com"           # Admins email address
ALERT_MAIL_SUB="Email subject"          # Email subject
ALERT_MAIL_BODY="email body"            # Email body
SMTP_HOST="smtp server name"            # Smtp server name
SMTP_USER="smtp user"           # Smtp user name
SMTP_PASS="password"            # Email password   
                    #
######### Mysql Account##################
                    #
MyUSER="root"               # mySQL USERNAME I uses root account. So this file must have 600 permission set
MyPASS="password"               # mysql PASSWORD
MyHOST="localhost"              # Mysql Hostname
                    #
###########Local path####################
DB_BKP_TEMP_LOC="temp folder"       # temporory folder path where dump is placed   
BACKUP_FILE="server name"       # This is for setting backup file name will be eg : Full-backup.mydomain.05-07-2012.bz2
BACKUP_LOG_FILE="log file path"         # declaire a full path of log file name eg: /var/log/dbbackup.log
MYSQL_LOCAL_BKP="local backup"      # Local copy of the database should be on differnet disk/volume    
RETENTION_PERIOD="7"                    # Number of days to retain the backup.

# ------------- Checking FTP is working --------
checkftp=`/bin/sh /home/installation/scripts/ftp_check.sh $FTP_USER $FTP_PASSWORD 21  $FTP_SERVER |awk ' {print $1}'`
if [ $checkftp == "OK:" ]
then
echo " FTP is connected !!"
else
#-------------   Sending email alert if ftp fails to connect-------------

/usr/bin/sendEmail -f alert@mydomain.com  -t $ALERT_MAIL_USER   -u $ALERT_MAIL_SUB -l /var/log/sendEmail  -o

message-content-type=auto  -m $ALERT_MAIL_BODY   -s $SMTP_HOST:25 -xu $SMTP_USER -xp $SMTP_PASS
exit
fi

# ----------Flushing cached pages from the momory-----------------------------------------
sync;echo "3" > /proc/sys/vm/drop_caches;

# ----------Deleting aged backup file-----------------------------------------------------
/usr/bin/find  $MYSQL_LOCAL_BKP  -mtime +$RETENTION_PERIOD -exec rm {} \;


# ----------Prepaiting Database backup-----------------------------------------------------

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
BZIP2="$(which bzip2)"

# Backup Dest directory
DEST=$DB_BKP_TEMP_LOC

# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE=""
# Database to backup
db="full-DB"
FILE="$DEST/$db.$BACKUP_FILE.$NOW.bz2"
        $MYSQLDUMP --all-databases -u $MyUSER -h $MyHOST -p$MyPASS  | $BZIP2 > $FILE
echo " #######   Backup Completed ###### "
cp $FILE $MYSQL_LOCAL_BKP
# ----------Uploading  database backup to remote ftp-----------------------------------------------------

cd $DB_BKP_TEMP_LOC
echo " Dbbackup is uploading to Remote ftp server "  `date` >> $BACKUP_LOG_FILE

ftp -d -v -n $FTP_SERVER <<EOF
user $FTP_USER $FTP_PASSWORD
bin
lcd  $DB_BKP_TEMP_LOC
cd $FTP_REMOTE_LOC/
mput *
quit
EOF
rm -rf $DB_BKP_TEMP_LOC/*
echo " Dbbackup has been completed  "  `date` >> $BACKUP_LOG_FILE

1 comment to FTP enabled MySQL backup script

  • alvin

    hi sir? is this script direct to sql server query…i am first time in sql server backing up database and send to FTP is so difficult to me..can you help me..thanks

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>