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?
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.