Backup a mysql database and scp it to an offsite box.

offsite-mysql-backup.sh

#!/bin/bash
#
#
# Take dump of the configured database and transfer it to an offsite server
#


start_date=`date +M%m-D%d-Y%Y_%Hh%Mm%Ss`
source_db_user="sourceDatabaseUserNameHere"
source_db_password="sourceDatabaseUserPasswordHere"
source_db_name="sourceDatabaseNameHere"
db_filename=$source_db_name.$start_date.sql
tmp_dir='/tmp'
target_dir_root='/var/chroot-home/dbdumps/files/nightly'
target_dir_suffix=`date +%Y/%m`
target_dir=$target_dir_root/$target_dir_suffix
target_dir_user='dbdumps'
target_dir_group='clients'
target_server='remote.server.com'
# passwordless login must be set between the user running this script and the user specified below
target_user='remote-ssh-username-here'



echo $0 "v0.1"
echo "==============="
echo $start_date
echo " "
echo " "


cleanup_tmp() {
    echo " Performing cleanup"
    echo " Cleaning up "$tmp_dir
    rm -rf $tmp_dir/$db_filename
    echo " Cleaned!"
    }

decent_exit() {
    cleanup_tmp
    echo " Exiting"
    echo " "
    exit 1
}


dbdump_failed() {
	echo " Error: Unable to generate db backup."
    echo " Please make sure that provided username, password and db name are correct"
    echo " Exiting"
    echo " "
   	exit 1
}


take_dbdump() {
	echo " Taking database dump on production as" $tmp_dir/$db_filename
	if [[ `mysqldump -d --force --opt --user="$source_db_user" --password="$source_db_password" --single-transaction --databases "$source_db_name" > $tmp_dir/$db_filename 2> /dev/null; echo $?` -ne "0" ]]; then
		 dbdump_failed
	fi

	tables=`mysql --user=$source_db_user --password=$source_db_password -e "USE $source_db_name;SHOW TABLES;" | tr -d "| " | grep -v Table`

	for tb in $tables; do
		#echo "Table is: " $tb
		if [ "$tb" != "MessagesToSend" ]; then
			if [[ `mysqldump --force --no-create-info --extended-insert=FALSE --compact --user="$source_db_user" --password="$source_db_password" --single-transaction "$source_db_name" "$tb" >> $tmp_dir/$db_filename 2> /dev/null; echo $?` -ne "0" ]]; then
			    dbdump_failed
		    fi
		fi
   	done

  	echo " Completed!"
    echo " "

}

copy_dbdump() {
	echo " Creating directory:" $target_server$target_dir
	if [[ `ssh $target_user@$target_server "mkdir -p $target_dir" > /dev/null; echo $?`  -eq "0" ]];
	then
        	echo " Created."
	        echo " Done!"
        	echo " "
	else
        	echo " Failed!"
	        echo " Unable to created target dir. Please make sure that provided credentials are correct."
        	decent_exit
	fi

	echo " Uploading database dump to" $target_server$target_dir
	if [[ `scp $tmp_dir/$db_filename $target_user@$target_server:$target_dir/ > /dev/null; echo $?`  -eq "0" ]];
	then
		echo " Uploaded."
		echo " Done!"
		echo " "
	else
		echo " Failed!"
		echo " Unable to copy archive. Please make sure that provided credentials are correct."
		decent_exit
	fi
}


fix_permissions() {
	echo " Fixing permissions on" $target_server/$target_dir
	if [[ `ssh $target_user@$target_server "cd $target_dir_root && chown -R $target_dir_user:$target_dir_group *" > /dev/null; echo $?`  -eq "0" ]];
	then
        	echo " Fixed."
	        echo " Done!"
        	echo " "
	else
        	echo " Failed!"
	        echo " Unable to fix permissions. Please make sure that provided credentials are correct."
        	decent_exit
	fi
}








take_dbdump
copy_dbdump
fix_permissions
decent_exit

echo ""
echo " All Operations executed successfully"
echo ""
exit 0;