Scenario

During the development stage we set up a test enviroment, so the client can see the new features. We can test the application in a server configuration real close to the final one. We test PHP configurations, Squid rules and we test the cache.

Once the development is done we establish a release date. This date we install the application in the final enviroment and we release it to the public.

Problem

The migration process between servers, often requires changing the WordPress installation URL. This means we have to update the database. We should replace all the ocurrences of the old domain with the new one.

Solution

We use to execute a SQL sentence that replaces old domain URLs with new domain ones. You can copy the next code to notepad, replace old_domain.com and new_domain.com with your values.
After that copy the sentence to your favourite database manager (phpMyAdmin, HeidiSQL, Navicat, etc…) and execute it.

Caution:
Before doing any database modification be sure to make a backup. In case of error you should restore the backup.

Remember that if your WordPress installation has plugins that uses the PHP serialize() function to store Array with data in the wp_options table, it’s possible that they stop working. This is because the URL replacement breaks the serialized Arrays index (because of they are based on the string length).

#posts
UPDATE wp_posts SET guid = REPLACE (guid, 'old_domain.com/', 'new_domain.com/');
UPDATE wp_posts SET post_excerpt = REPLACE (post_excerpt, 'old_domain.com/', 'new_domain.com/');
UPDATE wp_posts SET post_content = REPLACE (post_content, 'old_domain.com/', 'new_domain.com/');
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'old_domain.com/', 'new_domain.com/');

#comments
UPDATE wp_comments SET comment_author_url = REPLACE (comment_author_url, 'old_domain.com/', 'new_domain.com/');
UPDATE wp_comments SET comment_content = REPLACE (comment_content, 'old_domain.com/', 'new_domain.com/');
UPDATE wp_commentmeta SET meta_value = REPLACE (meta_value, 'old_domain.com/', 'new_domain.com/');

#links
UPDATE wp_links SET link_url = REPLACE (link_url, 'old_domain.com/', 'new_domain.com/');
UPDATE wp_links SET link_rss = REPLACE (link_rss, 'old_domain.com/', 'new_domain.com/');

#options
UPDATE wp_options SET option_value = REPLACE (option_value, 'old_domain.com/', 'new_domain.com/');

#usermeta
UPDATE wp_usermeta SET meta_value = REPLACE (meta_value, 'old_domain.com/', 'new_domain.com/');

#posts
UPDATE wp_posts SET guid = REPLACE (guid, 'old_domain.com', 'new_domain.com');
UPDATE wp_posts SET post_excerpt = REPLACE (post_excerpt, 'old_domain.com', 'new_domain.com');
UPDATE wp_posts SET post_content = REPLACE (post_content, 'old_domain.com', 'new_domain.com');
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'old_domain.com', 'new_domain.com');

#comments
UPDATE wp_comments SET comment_author_url = REPLACE (comment_author_url, 'old_domain.com', 'new_domain.com');
UPDATE wp_comments SET comment_content = REPLACE (comment_content, 'old_domain.com', 'new_domain.com');
UPDATE wp_commentmeta SET meta_value = REPLACE (meta_value, 'old_domain.com', 'new_domain.com');

#Links
UPDATE wp_links SET link_url = REPLACE (link_url, 'old_domain.com', 'new_domain.com');
UPDATE wp_links SET link_rss = REPLACE (link_rss, 'old_domain.com', 'new_domain.com');

#options
UPDATE wp_options SET option_value = REPLACE (option_value, 'old_domain.com', 'new_domain.com');

#usermeta
UPDATE wp_usermeta SET meta_value = REPLACE (meta_value, 'old_domain.com', 'new_domain.com');