[wp-hackers] Keep local DB in sync with remote DB

[Matt Martz]

On Sat, Nov 29, 2008 at 9:47 AM, Alex Hempton-Smith
<hempsworth at googlemail.com> wrote:
> Hi,
> I'm moving my development over to a local WAMP server, instead of uploading
> files and checking the changes every time.
> How would I keep my local mySQL database in sync with my remote mySQL
> database?
>
> The remote database is hosted at Dreamhost, so the database has a
> mysql.mydomain.com
>  address, rather than localhost, but when I use that on the local WAMP
> server it shows "Error establishing a database connection".
> Many thanks,
> Alex

I know this example doesn't really work for the staging or local
install to be on a windows machine but this I'll post a copy of my
bash script I use to keep my Production and Staging installs to be
duplicates of each other.  Keep in mind that both installs are also
using svn for the WordPress core files.

#!/bin/bash

DB_NAME="wordpress_prod"
DB_STG_NAME="wordpress_stage"
PROD_DB_HOST="localhost"
STAGE_DB_HOST="localhost"
DB_USER="db_username"
DB_PASS="db_password"
PROD_HOSTNAME="example.org"
STAGE_HOSTNAME="stage.example.org"
PROD_DIR="/home/user/html"
STAGE_DIR="/home/user/addons/stageexampleorg"

PROD_HOSTNAME_ESC=`echo $PROD_HOSTNAME | sed "s%\(\/\|\.\)%\\\\\\\\\1%g"`
STAGE_HOSTNAME_ESC=`echo $STAGE_HOSTNAME | sed "s%\(\/\|\.\)%\\\\\\\\\1%g"`
PROD_DIR_ESC=`echo $PROD_DIR | sed "s%\(\/\|\.\)%\\\\\\\\\1%g"`
STAGE_DIR_ESC=`echo $STAGE_DIR | sed "s%\(\/\|\.\)%\\\\\\\\\1%g"`

mysqldump \
        -h$PROD_DB_HOST \
        -u$DB_USER \
        -p$DB_PASS \
        --opt --no-create-db --complete-insert \
        --databases $DB_NAME | \
sed s/$DB_NAME/$DB_STG_NAME/g | \
sed s/$PROD_HOSTNAME_ESC/$STAGE_HOSTNAME_ESC/g | \
sed s/$PROD_DIR_ESC/$STAGE_DIR_ESC/g | \
mysql \
        -h$STAGE_DB_HOST \
        -u$DB_USER \
        -p$DB_PASS \
        -D $DB_STG_NAME

# CUT FROM HERE
cd $PROD_DIR
PROD_SVN_URL=`svn info | grep URL | awk -F': ' '{print $2}'`
PROD_SVN_REV=`svn info | grep Revision | awk -F': ' '{print $2}'`

cd $STAGE_DIR
STAGE_SVN_URL=`svn info | grep URL | awk -F': ' '{print $2}'`
STAGE_SVN_REV=`svn info | grep Revision | awk -F': ' '{print $2}'`

if [ $STAGE_SVN_URL == $PROD_SVN_URL ] && [ $STAGE_SVN_REV -ne $PROD_SVN_REV ]
then
        svn -r $PROD_SVN_REV update
fi
# TO HERE IF YOU DON"T USE SUBVERSION FOR THE CORE FILES

cp -rf $PROD_DIR/wp-content/* $STAGE_DIR/wp-content/

exit 0

-- 
Matt Martz
matt at sivel.net