WordPress database synch between dev and prod

2 Views

Question has been asked before about how to synchronize files as well as the database between two WordPress installations.

For the database level, the answer is usually to basically dump one database and insert it onto another server. The problem with this is that you end up losing any changes that have potentially been made on the prod server. For instance, usage metrics, comments, etc…

Read More

With this in mind, I was starting to wonder whether the it would be possible to extend the WordPress ORM so that you can generate deltas and then inject those into the prod site.

Has anyone tried this, looking into it, or have any ideas or comments ?

Post Views: 2

Related posts

Leave a Reply

3 comments

  1. The reality is that what we want is this: http://www.liquibase.org/

    Liquibase is an open source (Apache 2.0 Licensed),
    database-independent library for tracking, managing and applying
    database changes. It is built on a simple premise: All database
    changes are stored in a human readable yet trackable form and checked
    into source control.

    However our development process doesn’t support it. We typically don’t modify the database through discrete scripts we write ourselves, we use plugins that we activate. We don’t write DML scripts to modify look-up data that we then check into source code control, we use a UI on the admin page and therefore have no source code for later use in replicating that change during migration.

    However, we can emulate some of it — using some of the tools listed on this page:

    https://stackoverflow.com/q/225772/149060

    For instance, liquidbase has a diff feature that also, optionally includes changes to data. We could, potentially, output the schema and data diff to a script, excluding (as possible) certain tables likely to include test data ( i.e. post, etc. ) and then apply the script to the production database.

    MySQLDiff (discussed on the StackOverflow question) does schema diffs, and it’s author recommends mysql_coldiff for table-wise data diffs – both are implemented in perl, if java tools (liquidbase) are too resource heavy for your servers — although bring both databases local and running the tool on your PC solves that problem …

    If we really want to do it right, we should log any sql that relates to settings, options, or other configuration changes, and any schema changes — and convert the logged code into a migration script to play against our production server. Play the migration script against the server, copy the wordpress site files (excluding uploads, if applicable) and we’re gold.

    So, it seems to me, that the best way out, is a developer’s migration-builder-plugin that traps the sql we need, stores it and then generates a migration script from the logged code, rather than to build a way to merge databases between staging and production. Seems a simpler problem to solve too.

    If we look at the code of @bueltge ‘s instrumenting hook calls plugin for inspiration: https://gist.github.com/1000143 (thanks to Ron Rennick via G+ for pointing me in the direction of SAVEQUERIES and the shutdown hook, that lead me to find it)

    -- alter it to get the SAVEQUERIES output instead 
-- only run while in admin 
-- filter out all selects 
-- save results out to table in the shutdown hook 
-- we could selectively toggle output trapping based on what we were doing at the moment.

    For example:

    Capture Name: Activate & Configure Plugin XYZ

    Capture State Toggle – on

    … install and configure plugin XYZ

    Capture State Toggle – off

    Export Migration Script for: Activate & Configure Plugin XYZ

    Press Export Button — to produce a popup text field with the filtered trapped SQL – ideally pre-formatted as a shell script with command-line call to mysql. Copy & paste it out to your migration code folder and add to your source code repository.

    Careful attention to toggling the capture on and off as you’re working and you’ll be able to generate the perfect migration script to take your production database to an equivalent configuration to your staging database.

    What’s better, you’ll have a script (or series of same) that you can TEST. Imaging having replicable, testable, migration scripts!!

    I’m in love already.

    Anyone else?

  2. The Database Sync WordPress plugin does a great job of syncing data between two servers.

    By default it overwrites ALL destination data, however I’ve just implemented some enhancements to the plugin which allow you to only sync specific database tables. This can help you retain comments, users and other such data that you don’t want overwritten. Does that give you the granularity that you need?

    I haven’t released my changes to the public yet, but if you’re interested in a copy, send me an email at simon-at-yump.com.au. If anyone finds this useful or has additional feature requests, let me know and I’ll see what I can do.

    UPDATE: I’ve also just found the WP-Sync-DB plugin, which is a fork of the commercial WP-Migrate-DB-Pro plugin. It does a very similar thing, although probably has more polish than Database Sync.