Overview
That’s the one main caveat of WordPress development, that even a seasoned WordPress developer still wonders what’s the perfect automation for syncing database content from local environment to live server.
You can’t just upload your database dump from your local development environment to the server and be happy with the results.
WordPress actually stores the resulting URLs in the database.
So let’s say your local development URL is http://localhost/awesome. Your posts as it stored in the database has also stored your development URL(i.e. http://localhost/awesome) in every post. The URL is also stored in the wp_options table. So when you import your local database dump to the server you need to replace the existing URL to the server URL in order for the post links to work.
A not so elegant solution:
One of the solutions that I personally use is Search Replace DB by interconnect/it.
To use the script, download the zip file from below, extract the folder called search-replace-db-master (assuming you are using the 3.1.0 version), renaming it to something secret of your choosing, upload it via FTP, SFTP or SCP to your web server’s public facing directory, then navigate to that folder in your browser. The script will automatically attempt to find and populate the database field but you must check that the details are correct and that it is for the database you wish to carry out a search/replace operation on.
A typical WP install with this script would have the folders as follows:
- /your-secret-search-replace-folder
- /wp-admin
- /wp-content
- /wp-includes
NOTE: Please read the Installation and Usage of the tool in their website, and make sure the details are correct upon proceeding with the replacement.
How does it work?
Your:
Development URL: http://localhost/awesome
Live Server URL: http://awesome.com
You provide the development URL to the Replace Textbox and the live server URL to the With Textbox. Make sure to have a backup of your database, then hit the button dry run or live run to perform the action.
Conclusion
Does it work all the time? Well, not so quick, it actually depends on your requirements, but so far in all my WordPress projects and its varying requirements it works as expected.
There’s a lots of solutions out there and one of them is mentioned in your question(i.e. WP Migrate DB). You just got to bare the tedious workflow of WordPress development, as great as CMS WordPress has been throughout its existence there’s still this one main caveat that lets us want to be freed.