loading...
Cover image for 7 Life-saving SQL queries for Wordpress migration

7 Life-saving SQL queries for Wordpress migration

lucagrandicelli profile image Luca Grandicelli Updated on ・3 min read

When you need to manually migrate a Wordpress installation from one domain to another, you must perform some several database queries in order to make the new installation properly working.

A common misconception is that it is enough to just change the site_url and home values in the WP admin panel, but that's just not.

Those are two core value Wordpress uses to build its own base_url, but when a site is already filled with content, there are a few more tables involved in the process (wp_posts, wp_comments and wp_postmeta) and in which Wordpress stores data. if you don't update those too, you'll end up with a site with all media still pointing to the old domain as well as links and other data.

So here are the life-saving SQL queries you need to perform when doing a manual transfer. You only have to change the placeholders below, from:

original.domain.com to new.domain.com

NOTE: if you're migrating a site from a local enviroment (localhost), skip the last query and execute its alternative labeled "Alternative Query 7 for migrations from localhost enviroments".

-- Query 1: update the wp_options table
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://original.domain.com', 'https://new.domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

-- Query 2: update the wp_posts table
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://old.domain.com', 'https://new.domain.com');

-- Query 3: update the wp_posts table
UPDATE wp_posts SET post_excerpt = REPLACE (post_excerpt, 'http://old.domain.com', 'https://new.domain.com');

-- Query 4: update the wp_postmeta table
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://old.domain.com','https://new.domain.com');

-- Query 5: update the wp_comments table
UPDATE wp_comments SET comment_content = REPLACE (comment_content, 'http://old.domain.com', 'https://new.domain.com');

-- Query 6: update the wp_comments table
UPDATE wp_comments SET comment_author_url = REPLACE (comment_author_url, 'http://old.domain.com','https://new.domain.com');

-- Query 7: update the wp_comments table
UPDATE wp_posts SET guid = REPLACE (guid, 'http://old.domain.com', 'https://new.domain.com') WHERE post_type = 'attachment';

-- Alternative Query 7 for migrations from localhost enviroments
-- UPDATE wp_posts SET guid = REPLACE (guid, 'http://old.domain.com', 'https://new.domain.com');
Enter fullscreen mode Exit fullscreen mode

Important notice
The most important reference when moving Wordpress from one domain to another, is this CODEX page here.

Also consider that in some cases, as metioned before, plugins may store serialized data in the wp_postmeta table. In order to prevent a potential break within that data, follow the following CODEX advice:

If you do a search and replace on your entire database to change the URLs, you can cause issues with data serialization, due to the fact that some themes and widgets store values with the length of your URL marked. When this changes, things break. To avoid that serialization issue, you have three options:

Note: Only perform a search and replace on the wp_posts table.
Note: Search and Replace from Interconnectit is a 3rd party script


Found a typo? Make a PR

Discussion

pic
Editor guide
Collapse
dimasmagadan profile image
Dmitriy

well, in some cases data in wp_postmeta could be serialized.
running serach&replace query will break all if new domain length is different.

way better to use WP-CLI for that

Collapse
lucagrandicelli profile image
Luca Grandicelli Author

True. Gonna update the post /w a couple of infos directly from the Codex.