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

[Mike Schinkel]

Hi Alex:

I was just doing some of that last night for an existing client site whose
source I just copied down to my laptop.  I've been working locally on
Windows for over a year and it's an order of magnitude more productive then
testing on the remote server.

A couple other things that might help you or others:

1.) I always define a local domain entry in my hosts file
(C:\Windows\System32\driver\etc\hosts on Vista) using the same name and a
TLD of ".loc", i.e.

if ($_SERVER['REMOTE_ADDR']=='127.0.0.1') {
   define('WP_SITEURL', 'http://example.loc');
   define('WP_HOME', 'http://example.loc');
}

Note that there is no ".loc" on the Internet (at least not that I'm aware
of) but that doesn't stop you from defining it locally for use on your own
computer as a convenience.  Using ".loc" allows me to quickly edit the URL
and swap between a live site (i.e. "example.com") and a local site (i.e.
"example.loc").

2.) Of course to do this you'll need to add something to your Apache
http.conf file (mine is located at c:\Program Files\Apache Software
Foundation\Apache2.2\conf\httpd.conf, yours might be elsewhere.) Don't
remember which of these are not already in http.conf but I know these are
what you need to support virtual hosts in Apache 2.2 (be sure to read the
comments below, and be sure to restart Apache after you make these changes):

ServerName 127.0.0.1:80

# SECRET SAUCE! NEEDED FOR HOSTING MULTIPLE VIRTUAL HOSTS
NameVirtualHost *

# BE SURE TO CHANGE [htdocs] directory
# Mine is htdocs="users/mikes/Desktop/Apache/htdocs"
DocumentRoot "C:/[htdocs]"

<Directory />
   Options FollowSymLinks
   AllowOverride all
   Order deny,allow
   Allow from all
   Satisfy all
</Directory>

# BE SURE TO CHANGE [htdocs] directory
# Mine is htdocs="users/mikes/Desktop/Apache/htdocs"
# Note I use live domain ("example.com") for subdir name
<VirtualHost *>
   ServerName example.loc
   ServerAlias www.example.loc
   DocumentRoot "C:/[htdocs]/example.com"
</VirtualHost>

I'm not an expert with Apache configurations so there may be a way to
improve this but it has worked well for me. Of course if anyone knows how to
make it better I'd appreciate learning more.

3.) If you want to support multiple domains locally (i.e. "example.loc", "
example.net", etc. you'll need to add another ServerAlias in the
<VirtualHost *> element of http.conf above and also update wp-config.php,
something like this which uses whatever SERVER_NAME that loads your code:

if ($_SERVER['REMOTE_ADDR']=='127.0.0.1') {
   $url = 'http:// <http:///>' . $_SERVER['SERVER_NAME'];
   define('WP_SITEURL', $url);
   define('WP_HOME', $url);
   define('DB_HOST', 'localhost');
}

4.) If your web host uses a dedicated domain for MySQL (as MediaTemple does)
you'll need to set DB_HOST too and delete the existing entry defining
DB_HOST:

if ($_SERVER['REMOTE_ADDR']=='127.0.0.1') {
   $url = 'http:// <http:///>' . $_SERVER['SERVER_NAME'];
   define('WP_SITEURL', $url);
   define('WP_HOME', $url);
   define('DB_HOST', 'localhost');
} else {
   // The following is for MediaTemple, yours will probably be different
   // Be sure to change [server#] to your account's MySQL server number
   define('DB_HOST', 'internal-db.s[server#].gridserver.com');
}

5.) Also I use open-source HeidiSQL for Windows (http://www.heidisql.com/)
and I open up access to the web host's MySQL for access via my IP address.
Using that I can connect with HeidiSQL and either export or import a
database. HeidiSQL has a cool feature that if you open on instance pointing
to one (remote or local) database and another instance pointing to another
(remote or local) database you can export from one database and import into
another.

For sites like brochureware sites where the data is not changing I can
quickly download the current database, work on it, then upload it back using
these features (I didn't explain it step-by-step, but hopefully what you
need to do once you use HeidiSQL should be self explanatory.)

You can also use HeidiSQL to get a quick database dump in text format; much
better than using phpMyAdmin.

6.) Lastly, probably the best thing you could do for your productivity is
get a great IDE with debugger such as PhpED like I use:
http://www.nusphere.com/products/phped.htm (Windows only.) It may seem
expensive but it is incredibly powerful and has definitely been worth the
$300. It easily saved me that much time my first week of using it (after I
got the debugger working.)

HTH.

-Mike Schinkel
http://mikeschinkel.com/custom-wordpress-plugins/