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

Posted by on Apr 29, 2014 in Blog, Wordpress | One Comment

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.

Warning: We’re going to bulk-edit the database next. Don’t do this before you’ve made backup of the entire database.

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.

Also, when you copy/paste from here, be sure that quotes are all regular, single-quote characters. In English keyboards, they are located next to the ENTER key, together with the double-quote chars. The quotes that you copy from this page will most likely be the wrong character.

UPDATE wp_options SET option_value = REPLACE(option_value, '', '') WHERE option_value NOT LIKE '%{%';
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '', '') WHERE meta_value NOT LIKE '%{%';
UPDATE wp_posts SET post_content = REPLACE (post_content, '', '') WHERE post_content NOT LIKE '%{%';

Hint: After you run each of these queries, PHPMyAdmin will tell you how many entries were updated. Check that this number makes sense.

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, '', '');

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.

1 Comment

  1. Toko Bunga
    April 1, 2015

    Nice Share, But I Have Replace word in post content, You can help Me ?


Leave a Reply