Skip to content

Instantly share code, notes, and snippets.

@jdeeburke jdeeburke/pull-db.sh
Last active Jan 19, 2016

Embed
What would you like to do?
This is a script which connects to a remote database -- either directly or over SSH, exports a database, stores a backup copy of it in your local folder, then imports that database into the local DB of your choosing.
#!/bin/bash
######## Configuration
BACKUPS_DIR='database_backups'
LOCAL_HOST=''
LOCAL_USER=''
LOCAL_PASS=''
LOCAL_DB=''
REMOTE_DB_HOST=''
REMOTE_DB_USER=''
REMOTE_DB_PASS=''
REMOTE_DB_NAME=''
USE_SSH=false
SSH_HOST=''
SSH_USER=''
# This script assumes you'll use the default SSH identity.
# You can specify an alternate identiy to be used on this
# given domain in your ~/.ssh/config file if you choose.
REMOTE_PATH_TO_MYSQLDUMP='mysqldump'
LOCAL_PATH_TO_MYSQLDUMP='/Applications/MAMP/Library/bin/mysqldump'
LOCAL_PATH_TO_MYSQL='/Applications/MAMP/Library/bin/mysql'
######## End Config
BACKUP_NAME="$(date +%c).sql"
mkdir -p $BACKUPS_DIR
MYSQL_DUMP_ARGS="--default-character-set=utf8 --hex-blob -v -u $REMOTE_DB_USER -p'$REMOTE_DB_PASS' -h $REMOTE_DB_HOST $REMOTE_DB_NAME | bzip2 -c"
# Backup Remote DB
if [ "$USE_SSH" = true ] ; then
ssh $SSH_USER@$SSH_HOST $REMOTE_PATH_TO_MYSQLDUMP $MYSQL_DUMP_ARGS | bunzip2 -c > "./$BACKUPS_DIR/$BACKUP_NAME"
else
DUMP_DB="$LOCAL_PATH_TO_MYSQLDUMP $MYSQL_DUMP_ARGS | bunzip2 -c > \"./$BACKUPS_DIR/$BACKUP_NAME\""
eval $DUMP_DB
fi
# Load Backup into Local DB
LOAD_BACKUP="cat \"./$BACKUPS_DIR/$BACKUP_NAME\" | $LOCAL_PATH_TO_MYSQL -v -h $LOCAL_HOST -u $LOCAL_USER -p'$LOCAL_PASS' $LOCAL_DB"
eval $LOAD_BACKUP
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.