MySQL – Find and replace string (Useful for WordPress migrations)

Posted by & filed under PHP, Programming, Web Development.

I recently deployed a WordPress site. As part of the development cycle, we first built the site on staging.example.com, then moving it to the primary domain at launch. One issue that this can cause is when creating content, WordPress will create links with the full site’s URL. In our case the staging domain was linked on most images and links. When we went live, this caused some issues. It’s not a uncommon thing to run into, and fortunately there is a simple solution. The following PHP script will connect to the database, searching all tables for the specified string (in our instance a domain name) and replacing it with another string.

Simply update the username, password, database, string_to_replace and new_string with the appropriate values and you are off! I would recommend backing up the database to be safe.

 

Thanks to jimmy.zoger on Stack Overflow for the useful solution.

 

A follow up to this, is that if the values in the database are serialized, a find/replace can wreak havoc on things as it will likely break the serialization unless the character count is the same. A very nice utility I found is the following and it handles the serialization perfectly: https://interconnectit.com/products/search-and-replace-for-wordpress-databases/