Changing URL’s in your database after migrating a WordPress site

The problem

After you migrate a site to a new location or use WordPress Export/Import to import some posts to a site the URL’s of files such as images still point to the original site. After migration the task is to update all the posts and custom fields to the URL of the production site. There are migration tools such as Backup Buddy that can take care of this for you, but not all hosting environments will allow them. This is the old school way and you can use it anywhere

When you insert a link or an image, WordPress will store it with its full path. All of these absolute references have to update when you’re moving to the new site. Otherwise, all inline lines and images still go to the previous server.

The solution

Use a database editing tool such as PHPMyAdmin. Navigate to the right database, if you have several. You can backup the database using PHPMyAdmin, using the ‘export’ tool.

These MySQL statements will replace URLs from old site to new. Obviously, you need to replace the sample URLs with your actual ones.

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_value NOT LIKE '%{%';
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE meta_value NOT LIKE '%{%';
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE post_content NOT LIKE '%{%';

This excludes keys that have the character { in them as they would be part of serialized arrays. If you replace parts of serialized data, the entire entry would become corrupt. These queries will replace only URLs outside of serialized arrays.

You also need to change the guid field in the wp_posts table.

UPDATE wp_posts SET guid = REPLACE(guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

2. Updating file paths from old to new sites

We’re almost there. The last thing is to change file directories. Often when you upload images, audio or video, WordPress will save the full path of these items in the database – normally in custom field values. You need to replace them. If your directories path did not change you can skip this

UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '/home/dev/wordpress ', '/home/prod/wordpress ') WHERE meta_value NOT LIKE '%{%';

Again, we’re making sure that only ‘regular’ entries are replaced and not serialized data.