7 Life-saving SQL queries for Wordpress migration

#wordpress #sql #database

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

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:

Use the Velvet Blues Update URLs or Better Search Replace plugins if you can access your Dashboard.
Use WP-CLI’s search-replace if your hosting provider (or you) have installed WP-CLI.
Use the Search and Replace for WordPress Databases Script to safely change all instances on your old domain or path to your new one. (** only use this option if you are comfortable with database administration ** )

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