What's a better workflow for database migration?

Question

When asked to work on an existing WordPress Site, I generally take a copy of the whole site and work on it locally. For quite a few years I have used a script such as the following after importing the database locally.

UPDATE wp_options SET option_value = replace(option_value, 'http://clientsite.co.uk', 'http://local.wordpressdev') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = REPLACE (guid, 'http://clientsite.co.uk', 'http://local.wordpressdev');
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://clientsite.co.uk', 'http://local.wordpressdev');    
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://clientsite.co.uk', 'http://local.wordpressdev');

I've seen advice to never mess with post guids but the above has always worked for me and has, to my knowledge, never caused any problems.

My question is, is this still a valid migration method with Wordpress 4? Also do plugins like 'Search & Replace' or tools such as WP-CLI do this better?

Answer 1

I use this for search and replace on databases - https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

Simply move files with FTP, export and import database and then run the script searching for old URL and replacing with the new URL. Then I resave permalinks and I'm done. I've moved WP thousands of times using this.

I've got the old script which is just a single file, I'm not so keen on the new version, it's a bit slower and more idiot proof which I actually don't like.

Answer 2

wp-cli has this built in. If you have wp-cli installed, from the command line you can run:

wp search-replace 'http://oldurl.com' 'http://newurl.localhost'

or whatever URLs you use in your local environment and this will update the local site database, taking serialised arrays into account (which the OP doesn't do).

You can create aliases in your .bash_profile to run a series of wp-cli commands to create a database, reset a database, import a database, search-replace on a database etc. So in the end you could have one alias you run on the command line that does all the work of importing a new database to a site for you.