#!/bin/sh

#######################################
#
# SET CREDENTIALS HERE
#

LOCAL_BACKUP_HOMEDIR=/var/remotebackups/_scripts/mydomain_01.de_mysql_db01/

REMOTE_SERVERNAME=mydomain_01.de
REMOTE_USERNAME=remotesshuser

REMOTE_DIR_FOR_TEMP_DUMP=/tmp

MYSQL_DBNAME=db01
MYSQL_USER=db01user
MYSQL_PASS=xyz123

MYSQL_PARAMS=--add-drop-table


#######################################
#
# NOTHING TO DO BELOW THIS LINE
#

#change dir to my_home_dir
cd $LOCAL_BACKUP_HOMEDIR


#write startlog
MY_DATETIME=`date '+%Y-%m-%d %H:%M:%S'`
echo "| START $MY_DATETIME" >> execution.log


#generate mysqldump on remote machine
echo "| ($REMOTE_SERVERNAME $MYSQL_DBNAME) generate mysqldump on remote machine..."
if [ $? = 0 ]
  then
    echo "|  create mysqldump" >> execution.log
    ssh $REMOTE_USERNAME@$REMOTE_SERVERNAME "cd ~; mysqldump --user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_PARAMS $MYSQL_DBNAME > $REMOTE_DIR_FOR_TEMP_DUMP/$MYSQL_DBNAME.sql; gzip $REMOTE_DIR_FOR_TEMP_DUMP/$MYSQL_DBNAME.sql"
  else
    echo "|  ** error: last exit-code=$?" >> execution.log
fi


#retrieve gzipped dump file via scp
echo "| ($REMOTE_SERVERNAME $MYSQL_DBNAME) retrieve dump file via scp..."
if [ $? = 0 ]
  then
    echo "|  scp dump file" >> execution.log
    scp $REMOTE_USERNAME@$REMOTE_SERVERNAME:$REMOTE_DIR_FOR_TEMP_DUMP/$MYSQL_DBNAME.sql.gz ${LOCAL_BACKUP_HOMEDIR}data/${MYSQL_DBNAME}.sql.gz
  else
    echo "|  ** error: last exit-code=$?" >> execution.log
fi


#delete dumpfile
echo "| ($REMOTE_SERVERNAME $MYSQL_DBNAME) delete dumpfile on remote machine..."
if [ $? = 0 ]
  then
    echo "|  rm remote temp file" >> execution.log
    ssh $REMOTE_USERNAME@$REMOTE_SERVERNAME rm $REMOTE_DIR_FOR_TEMP_DUMP/$MYSQL_DBNAME.sql.gz
  else
    echo "|  ** error: last exit-code=$?" >> execution.log
fi


#write end log
MY_DATETIME=`date '+%Y-%m-%d %H:%M:%S'`
echo "'- END $MY_DATETIME" >> execution.log


#######################################