#!/bin/sh

#backup directory
cd /var/backup

#date in format yyyy-mm-dd
date=`date -I`

echo "Backup: $date" >> backuplog

echo "Dumping MySQL data..." >> backuplog

#dumps database table structures and data into file backup-yyyy-mm-dd.sql
mysqldump -u username -p password dbname > backup-$date.sql 2>>backuplog

#check if this file exists and is not empty, as an with mysqldump in the command above
 will result in creating an empty file
if [ -s backup-$date.sql ]
then

        echo "MySQL dump successful." >> backuplog

        echo "Uploading to FTP Server..." >> backuplog

        #log into server and upload
        ftp -n 2>>backuplog 1>>backuplog << END-OF-SESSION
        open server
        user username password
        bin
        hash
        prompt
        cd ~/backup
        put backup-$date.sql
        mls backup-$date.sql temp
        bye
END-OF-SESSION

        if [ -s temp ]
        then
                uploaded=`cat temp`

                if [ $uploaded = "backup-$date.sql" ]
                then
                        echo "Backup of '$uploaded' successful." >> backuplog
                        echo "Backup successful."
                else
                        echo "File upload failed." >> backuplog
                        echo "Backup unsuccessful.  FTP upload failed."
                fi
                rm temp
        else
                echo "Problem establishing FTP connection" >> backuplog
                echo "Backup unsuccessful.  Problem establishing FTP connection."
        fi
        #remove files beginning with backup- which are older than 7 days
        find /var/backup -name "backup-*" -mtime +7 -exec rm -v '{}' \;
        #nb - old backup files not removed from ftp server
else
        echo "MySQL dump failed." >> backuplog
        echo "MySQL dump failed."
fi

echo "" >> backuplog

exit 0