#!/bin/sh

# Create a db dump and upload it to another server
#
BACK_SERVER='xxx.xxx.xxx.xxx'
BACK_SERVER_PORT='22'
BACK_SERVER_USER='user'

# Database credentials
DB_NAME='database'
DB_USER='user'
DB_PASS='password'

# store current date
NOW=`date +%Y.%m.%d.%H.%M`
YEAR=`date +%Y`
MONTH=`date +%m`
DAY=`date +%d`

# Working paths
TMP_PATH="/tmp/wbackup-$NOW"
DB_SQL_BACKUP="$TMP_PATH/$DB_NAME.$NOW.sql"
DB_BZ_BACKUP="$TMP_PATH/$DB_NAME.$NOW.sql.bz2"

REMOTE_PATH="~/backups/$YEAR/$MONTH/$DAY"

mkdir $TMP_PATH
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME > $DB_SQL_BACKUP
bzip2 -c $DB_SQL_BACKUP > $DB_BZ_BACKUP

# Backup on remote server
ssh -p $BACK_SERVER_PORT $BACK_SERVER_USER@$BACK_SERVER mkdir -p $REMOTE_PATH
scp -P $BACK_SERVER_PORT $DB_BZ_BACKUP $BACK_SERVER_USER@$BACK_SERVER:$REMOTE_PATH

rm -rf $TMP_PATH