Export a remote database and save it locally

exportremote.sh

#!/bin/sh

#
# This is a shell script for exporting a remote database and
# saving it to a local directory.
#

# EDIT THESE ON A PER-PROJECT BASIS

PROJECT="some_project"
BACKUP_PATH="db_dumps/"

SSH_USER=""
SSH_HOSTNAME=""
SSH_PORT="22"

REMOTE_DB_HOST="localhost" #localhost, 127.0.0.1, or separate DB server address
REMOTE_DB_USER=$SSH_USER
REMOTE_DB_PASS=""
REMOTE_DB_NAME=""

# ONLY EDIT BELOW THIS LINE IF YOU KNOW WHAT YOU'RE DOING

DATE=$(date +"%Y-%m-%d_%H-%M%p")
BACKUP_FILENAME="stage_"$REMOTE_DB_NAME"-"$DATE".sql.gz"



echo "Backing up STAGING database to $BACKUP_PATH$BACKUP_FILENAME..."

ssh $SSH_USER@$SSH_HOSTNAME mysqldump $REMOTE_DB_NAME -h$REMOTE_DB_HOST -p$REMOTE_DB_PASS --add-drop-table | gzip -9 > $BACKUP_PATH$BACKUP_FILENAME