Copy MySQL database over SSH / SCP

copy_mysqldb.sh

#!/bin/bash

echo "copy_mysqldb.sh -- p.kilczuk@neumea.pl"
echo "version 0.1.0"
echo ""

REMOTE_DBNAME=""
REMOTE_USER=""
REMOTE_HOST=""
REMOTE_PORT="3306"

SSH_HOST=""
SSH_USER=""

LOCAL_DBNAME=""
LOCAL_USER=""
LOCAL_HOST=""
LOCAL_PORT="3306"

DUMP_OPTS="--opt"

################################################################################
# it shouldn't be necessary to edit below this line ############################
################################################################################

OLDDIR=`pwd`
SELF=`dirname $0`

TMP_FILENAME="`date | md5sum | cut -d\  -f1`"

DUMP_SCRIPT="
mysqldump $DUMP_OPTS -B $REMOTE_DBNAME -h $REMOTE_HOST -p -P $REMOTE_PORT -u $REMOTE_USER > /tmp/$TMP_FILENAME.sql
cd /tmp/
tar -czf $TMP_FILENAME.tar.gz $TMP_FILENAME.sql
rm $TMP_FILENAME.sql
"
CLEANUP_SCRIPT="rm /tmp/$TMP_FILENAME.tar.gz"

echo "Connecting via SSH <$SSH_USER@$SSH_HOST>, you will now be promped for DATABASE password"
ssh $SSH_USER@$SSH_HOST "$DUMP_SCRIPT"
echo ""

echo "Downloading via SCP from $SSH_HOST"
scp $SSH_USER@$SSH_HOST:/tmp/$TMP_FILENAME.tar.gz /tmp/
echo ""

echo "Inserting into local database"
cd /tmp/
tar -xzf $TMP_FILENAME.tar.gz
mysql $LOCAL_DBNAME -h $LOCAL_HOST -p -P $LOCAL_PORT -u $LOCAL_USER < $TMP_FILENAME.sql
echo ""

echo "Cleaning up..."
ssh $SSH_USER@$SSH_HOST "rm /tmp/$TMP_FILENAME.tar.gz"
rm $TMP_FILENAME.sql $TMP_FILENAME.tar.gz