What is the recommended best workflow syncing local and live wordpress database?

Question

Exporting and importing database from my phpMyAdmin (live to local - and vice versa) is becoming a tedious task and prone to inconsistency. There are times the I imported a wrong database file that was on my downloads folder, overwriting my database with a wrong recored, lucky I still have a backup.

Is there a better workflow how to sync database from your local to live server? I need to sync my live-to-local and local-to-live because the changes are happening in both sides, the local changes is for the developer working on the site (which is me) and the live is for the publisher and administrator who's publishing content or doing some administration tasks with the live site.

I wish there solution like it has a version control option, so, that if I messed up I can still go back to an earlier stage.

Incase it's a software solution, I'm running on OS X (10.8.3). I use wordpress, coda and Mamp.

Answer 1

My needs are nearly identical to yours, so I wrote a script to handle all my migration needs: https://github.com/jplew/SyncDB.

SyncDB is bash deploy script meant to take the tedium out of synchronizing local and remote versions of a Wordpress site. It allows developers working in a local environment (eg. MAMP) to rapidly "push" or "pull" changes to or from their production server with a single terminal command.

My setup is similar too, so there's a good chance it will work for you: Mac OS X 10.8.4, MAMP, Wordpress, and MacVim.

While it doesn't subject the dump files to version control, it automatically names them 130830-0923-my_database.mssql.bz2 or whatever. Thus, you can restore previous versions easily. By scripting it, it takes a lot of the human error out the process too.

Answer 2

We develop wordpress sites and have many developers working in multiple environments. We've found this plugin that makes our life extremely simple. (We didn't make it, and we aren't paid by them to promote it)

http://deliciousbrains.com/wp-migrate-db-pro/

We've found that for the small amount paid ($99 for us) it has saved us TONS of time, making up for it in billable hours we can actually earn on.