Database synchronization between dev/staging and production

2 Views

I have a problem with WordPress database synchronization between development and production and I am wondering how other people solving it. I am aware about this question but it doesn’t really cover the nastier and more realistic use case.

Say I have a live WordPress website. I took a dump of everything, replicating it on our dev environment. I started making changes. 1 week later I am ready to deploy my updates. In the meantime, database on production site has changed(new posts, new comments, etc.). How do I synchronize changes between production and development during the rollout and is it possible to automate(somewhat at least) this process?

Post Views: 2

Related posts

Leave a Reply

4 comments

  1. There may be a better way that I am missing but I am going to give you 2 options:

    1.Use XML Export to export your new posts and comments. Then use the WordPress Importer to import the new posts and comments back into the dev database

    It’s best to import into dev then move the database over to production because when you import it will download all the new media files from production.

    In the meantime production has changed(new posts, new comments, etc.)

    This would solve your problem of bringing in any changed content.

    2. Use the INSERT IGNORE INTO MySql command to add the new tables from dev. or the REPLACE command to overwrite duplicate rows in the same table.

    Before using MySql make a backup of both databases and move the gz database to the production server and upload the dump (change the name of dev if it’s the same as production.

    INSERT IGNORE INTO `_wp_production_db`.`wp_cool_plugin_options`
SELECT *
FROM `_wp_dev_db`.`wp_cool_plugin_options`

    I’m not comfortable with MySql commands so I would go with option 1.

  2. If it’s just more of the exact same type of data (some new blog posts, new comments) I’m not sure why you need to sync it really. It’s not like it will change the way the code on the site works since it’s just more of the same. I typically don’t worry about it unless it’s a new type of data.

    I just always make sure I have a good sample of the data for the site no every post, page, comment from the live site.

  3. As soon as you touch the topic of doing changes in parallel you touch the area of configuration management. With lots of patterns, own communities (http://www.cmcrossroads.com/) and tools not so much for version management (as svn/git) but for support of configuration management (patterns) like clearcase. (totally different areas).

    In this case it is still a simple situation and you will find it to work with some limitations and some manual work and some lists.

    The scenario I am thinking of to make it more descriptive of the ideal solution: multiple developers working on the same codebase, multiple test environments, multiple acceptance environments, multiple production acceptance environments possibly in all corners of the world.

    If you would want to do this a little bit more professional:

    a) write down a list of all Configuration Items you encounter, this could be the WordPress code itself, plugins from externals, content, metadata and decide which ones of these you want to bring under some kind of “management”, which ones matter.

    b) describe the workflows that can happen e.g. what happens with a fix, what happens with something new being development, in what case do you change content on your side, what is that called, and who does it, who is the owner of it e.g. a new post or a new plugin.

    c) for parallel working first describe which CI’s you want to manage, decide if the flow is always from development to production or if it is really needed to do all of it two ways.

    d) for each of the CI types under (a) write a resolution. E.g. for ALL that is text ( or exported text like php files but ALSO plain text in XML files) merging is possible. This is really no problem but you need a good merge tool.
    e.g. With ClearCase you would get in a 3 way merge the following situations:
    1) trivial merges: it will do these automatically
    2) non trivial automatic: it will do these automatically BUT you need to check it
    3) non trivial non automatic: this is a conflict e.g. on 1 line several changes have been made.
    The non trivials are the minimal part that you need to care of manually, a good merging tool will lead you in this e.g. the one in clearcase (which also does word merging and where you can link in other commercial or non commercial mergers for specific file types).
    Furtermore if you have identified under (a) files that should be copied-only then their behaviour would be to not be merged but just be copied one way overwriting the other version without a merge (e.g. plugins that you have not modified). Many of these types are possible with different behaviours. But write down relations between CI’s,

    Then for the non text based merges you need to make a decision on how to handle them e.g. images that have been changed in 2 places. You could decide here that production always has preference (at least that is what i would think), which makes it simple.

    So… to solve this problem you need a version management tool that support different streams. Each stream would represents one part. (this can be immensely complex depending on your needs but in this case I think it is very simple).

    If you now can manage to have these streams under you WordPress installations and sync them also with the content of the database, etc… then you can perform the merges in the CM / versioning tool and then export it back in the other environment.

    Thing is… you need to write this down first. This is not a technical hack. It is a default pattern around Config Management so nothing strange here also but you need to write it down. You might find e.g. that an installed plugin makes changes in the database with some data that is different in another environment, so you need to have an extra procedure around this.

    Technically almost always everything is possible check http://www.cmcrossroads.com/forums for scenario’s that are dozens or hundreds of times more complex though always using the same approach and using the same set of CM patterns.

    in short: put a version management layer under it, automate the merges and handle the conflicts, then import in target environment. Think up a stream strategy that fits here and write it down. Perform a teeny weeny bit CM management. That would be the professional solution otherwise install some db copy hack, scripts etc…

  4. I just made a post about how i synchronize out production data to our staging, check out my blog post about it at: http://blog.wp.weightpoint.se/2012/01/04/synchronizing-wordpress-multisite-database-from-production-to-staging-enviorment/

    If you want to synchronize the code and other stuff too, i would recommend creating a git or mercurial repository with the relevant ignore file.

    If you want to make differential updates to prod from staging, then i guess creating migration scripts is the safest and best way.