get_remote_db.sh

#!/bin/sh

# Import a remote database into a local database
# ----------------------------------------------
#
# Based on http://danherd.net/quick-script-synchronise-from-a-remote-expressionengine-database/
#
# Don’t forget chmod +x to make the script executable.
#
# Change the extension to .command to run the script directly from OS X Finder.
#

# Settings
# --------

# Set SSH host
SSHHOST="username@example.com"

# Set mysqldump client
MYSQLDUMP="/Applications/MAMP/Library/bin/mysqldump"
# Set mysql client
MYSQL="/Applications/MAMP/Library/bin/mysql"

# Set local database host
LOCALDBHOST="localhost"
# Set local database user
LOCALDBUSER="dbuser"
# Set local database name
LOCALDBNAME="dbname"
# Set local database password
LOCALDBPASS="dbpassword"

# Set directory for local database backup
LOCALBACKUPDIR="../../tmp/"

# Set remote database host
REMOTEDBHOST="database.example.com"
# Set remote database user
REMOTEDBUSER="dbuser"
# Set remote database name
REMOTEDBNAME="dbname"
# Set remote database password (leave single quotes for special characters)
REMOTEDBPASS="'dbpassword'"


# Let’s go!
# ---------

# Run script in current directory
cd "`dirname "$0"`"

# Backup local database
$MYSQLDUMP \
-h $LOCALDBHOST \
-u $LOCALDBUSER \
$LOCALDBNAME \
-p$LOCALDBPASS \
| gzip > $LOCALBACKUPDIR/BACKUP_$(date +%Y-%m-%d_%H-%M-%S)_local_$LOCALDBNAME.sql.gz

# Connect to the remote machine and get a compressed dump of the database
ssh $SSHHOST \
"cd tmp &&
mysqldump \
-h $REMOTEDBHOST \
-u $REMOTEDBUSER \
$REMOTEDBNAME \
-p$REMOTEDBPASS \
--ignore-table=exp_security_hashes \
| gzip > $REMOTEDBNAME.sql.gz"

# Connect again and copy the compressed dump created above into the local temporary files folder
scp $SSHHOST:~/tmp/$REMOTEDBNAME.sql.gz /tmp/

# Decompress the file
gunzip \
-f /tmp/$REMOTEDBNAME.sql.gz

# Load the decompressed dump into the local database server
$MYSQL \
-h $LOCALDBHOST \
-u $LOCALDBUSER \
-p$LOCALDBPASS \
-D $LOCALDBNAME \
< /tmp/$REMOTEDBNAME.sql

# Delete the file from the local temporary files folder
rm /tmp/$REMOTEDBNAME.sql

# Connect again and delete the file from the remote temporary files folder
ssh $SSHHOST \
"rm tmp/$REMOTEDBNAME.sql.gz"