Migrate your WordPress site manually

2015-10-22

Change domain

If you are just transferring to another domain, it should be enough just to change the hard coded URL’s. These three commands will search and replace the old URL with your new one inside the post content, post GUID and the options table.

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://old_url', 'http://new_url');
UPDATE wp_posts SET guid = REPLACE(guid, 'http://old_url', 'http://new_url');
UPDATE wp_options SET option_value = 'http://new_url' WHERE option_name = 'home' OR option_name = 'siteurl';

Change table prefix

If you also want to change the table prefix, there’s some more work to be done. At first, we need to update the hard coded database prefixes. It doesn’t seem like a good idea to save these in the database, but WordPress has done it. Run these two commands to update the necessary data, and replace “wp_” with your current table prefix and “new_” with your new table prefix:

UPDATE wp_options SET option_name = 'new_user_roles' WHERE option_name = 'wp_user_roles';
UPDATE wp_usermeta SET meta_key = REPLACE(meta_key, 'wp_', 'new_') WHERE meta_key IN ('wp_user_level', 'wp_capabilities', 'wp_userlevel', 'wp_autosave_draft_ids');

Finally, we can update the table named themselves. run these commands to replace the old and new table prefixes in this example.

ALTER TABLE wp_links RENAME TO new_links;
ALTER TABLE wp_users RENAME TO new_users;
ALTER TABLE wp_usermeta RENAME TO new_usermeta;
ALTER TABLE wp_posts RENAME TO new_posts;
ALTER TABLE wp_postmeta RENAME TO new_postmeta;
ALTER TABLE wp_options RENAME TO new_options;
ALTER TABLE wp_comments RENAME TO new_comments;
ALTER TABLE wp_commentmeta RENAME TO new_commentmeta;
ALTER TABLE wp_term_relationships RENAME TO new_term_relationships;
ALTER TABLE wp_term_taxonomy RENAME TO new_term_taxonomy;
ALTER TABLE wp_terms RENAME TO new_terms;
-- Repeat for all other tables your plugins might have created.