2 Neurones & 1 Camera

Olivier Thereaux

Synchronizing two instances of wordpress

staging and production servers.

Using subversion for the theme code itself did help us test changes to the theme scripts and style before pushing them to production, but the content itself was not duplicated: the development instance of the blog had some fairly antiquated content, while the production instance had all the latest article.

Synchronising the content from the production to the development server was not as easy as simply dumping and reimporting the whole SQL database. Be aware that if you import a whole WordPress database, you also import some field setup that will cause unwanted redirects.

Suppose you dump the wordpress database for http://www.myblog.example.com/ into a duplicate instance at http://dev.myblog.example.com/. Try accessing http://dev.myblog.example.com/ and wordpress will automatically redirect you to http://www.myblog.example.com/, which it thinks is the right URI for your blog. What to do? Go to the WordPress Dashboard and edit the settings? Not going to happen, since trying to access http://dev.myblog.example.com/wp-admin/ will also redirect you to http://www.myblog.example.com/.

The solution I found was to:

  1. Make sure you use separate databases for your production and development blog. This is much safer, anyway. Also, make sure your databases and files are properly backed up before attempting the following hack.

    Disable all plugins before the sync. Especially if you are using wp-cache, disable it. You’ve been warned.

  2. Dump the database from your production blog into the separate database for your development blog. With mysql, this will look like:

    mysqldump PROD_DATABASE_NAME -h PROD_DATABASE_HOST -u PROD_DATABASE_USER -p > wordpress_dump.sql

    then

    mysql DEV_DATABASE_NAME -h DEV_DATABASE_HOST -u DEV_DATABASE_USER -p < wordpress_dump.sql

    (You will be prompted for the database password both times. Make sure to replace the placeholders PROD_DATABASE_HOST with actual values for your setup.

  3. Edit the development database to tell wordpress the location of the dev blog. With MySQL again:

    echo 'update wp_options set option_value="http://staging.address.com/" where option_name="home";' | mysql DEV_DATABASE_NAME -h DEV_DATABASE_HOST -u DEV_DATABASE_USER -p

    and

    echo 'update wp_options set option_value="http://staging.address.com/wordpress" where option_name="siteurl";' | mysql DEV_DATABASE_NAME -h DEV_DATABASE_HOST -u DEV_DATABASE_USER -p

This worked for me, in two very distinct configurations last week. Any way this could have been done in a less hack-ish way? Tell me in the comments.

<!-- You can start editing here. -->

5 Responses to “Synchronizing two instances of wordpress”

  1. Mike Ebert says:

    Try this plugin–it’s a database backup tool that also includes the option to sync: http://www.bin-co.com/blog/2008/10/remote-database-backup-wordpress-plugin/

  2. [...] Artbeat – Synchronizing two instances of wordpress [...]

  3. brian says:

    i’ve been googling all over for something like this, thanks so much.

    i have a question though. in my case my dev and production sites are on different servers. i ended up manually ftp’ing the dumped sql file from one to the other, but is there a way to avoid this? i try putting the absolute url to the sql file in the second part of step 2 but it wasn’t working..

    thanks again.

    • olivier says:

      You might be able to dump the database from a given hostname by giving its address as the parameter coming just after the “-h” in the command. However, in most cases your database server will not allow you to connect from another machine, so (s)FTP is your best option.

  4. Hello,
    Thanks for this method. I have been synching my local MAMP-based WordPress site yesterday with ipublica.com. I went straight to phpMyAdmin and exported the database, while having checked the box for the drop tables (so that it can overwrite the database on the server). After importing the database on the server, I experienced, like you, the following.

    The browser persistently tried to go to ipublica.com:8888, which is the port that MAMP PRO uses. So I simply opened the sql file with a text editor and replaced all instances of ipublicacom:8888 with ipublica.com. And all worked fine, but I noticed that the login I had set for the server WordPress installation was overwritten with my local login information.

    I have not yet tried to use the dump command.
    Best regards,
    Pierre

Previous/Next

Le froid des rues et la chaleur des autres

2009-02-27

Je sais la venue du printemps mais elle n'existe pas dans ce monde figé en hiver, saisi par les bourrasques de vent glacial. Comme un nouveau-né de Novembre, je ne connais que le froid, la laine, les joues rougies et les envies de soupe chaude.

À suivre / 12 photos


La Chambre Blanche

2009-03-01

I can't remember when was the last time I went to see a performance of contemporary dance. Probably never did. Theatre, sure, opera too – although I clearly spent more time in the last decade at museums or rock concerts than opera houses, I am equally comfortable banging my head in a muddy radiohead concert or swoon in the “poulailler” of Paris' opera for Le Nozze de Figaro.

Read more