Sync your development environment with a remote one

While the developing process you need sometimes to sync you local development database with a remote one (i.e. production).

Below there is a script allowing to recreate the development database and fill in with data from the remote server.

MySQL

#!/bin/sh

DBUSER_REMOTE="root"
DBPASSWORD_REMOTE="root"
DBUSER_LOCAL="root"
DBPASSWORD_LOCAL="root"
DBNAME_REMOTE=""
DBNAME_LOCAL=""
TMPFILE="$(mktemp db.XXXXXXX)"
HOST="www.example.com"

ssh $HOST "mysqldump -u$DBUSER_REMOTE -p$DBPASSWORD_REMOTE $DBNAME_REMOTE | gzip" | gunzip > $TMPFILE

mysqladmin -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL -f drop $DBNAME_LOCAL
mysqladmin -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL create $DBNAME_LOCAL
mysql -u$DBUSER_LOCAL -p$DBPASSWORD_LOCAL $DBNAME_LOCAL < $TMPFILE
rm $TMPFILE

PostgreSQL

#!/bin/sh

DBUSER_REMOTE="postgres"
DBUSER_LOCAL="postgres"
DBNAME_REMOTE=""
DBNAME_LOCAL=""
TMPFILE="$(mktemp db.XXXXXXX)"
HOST="www.example.com"

ssh $HOST "pg_dump -U $DBUSER_REMOTE -Fc $DBNAME_REMOTE" > $TMPFILE

dropdb -U $DBUSER_LOCAL $DBNAME_LOCAL
createdb -U $DBUSER_LOCAL $DBNAME_LOCAL
pg_restore -U $DBUSER_LOCAL -Fc -d $DBNAME_LOCAL $TMPFILE
rm $TMPFILE

Sync media files

$HOST="www.example.com"
$PATH="/absolute/path/to/media"
$LOCAL_DIR="."
rsync -rltDvH $HOST:$PATH $LOCAL_DIR