WordPress Migration

Moving WordPress sites from host to host isn’t as difficult as you may think. Following these simple steps will help guide you through the entire process: copy/move site files, copy/move the database, and most importantly update your database/table values with this special MySQL query.

Moving WordPress sites from host to host isn’t as difficult as you may think. I tend to take a straight-forward and direct approach to transfer WordPress to a new host/environment. I copy and move all the site files as well as the database/tables. I then update several database/table values to ensure that the moved WordPress site will work properly.

The Basic Steps

Copy WordPress site files
Copy WordPress database/tables (duplicate them, export/import)
Migrate WordPress site files to the new host
Migrate WordPress database/tables to the new host
Update old WordPress database/table values (this step is very important, see video)

Copy and Migrate WordPress Site Files

You will need access to your current site files, typically you will do this through FTP. Login and begin to copy your site files locally (on to your hard drive). Once you have a backup of your site files, you will want to copy them to the web root directory on your new host.

Copy and Migrate WordPress Database/Tables

In this step you will need access to your database. Most hosts/providers will have a control panel and/or access to phpMyAdmin. If you are already familiar with this step, you may be using Navicat or MySQL Workbench to do your copying.

Essentially you will want to dump (export/backup) your entire WordPress database. After exporting your database, you will need to import it into your new host. This step may require that you setup a new MySQL database and database username/password (your hosting provider will have a control panel to do this). To import you will most likely be using a similar tool like phpMyAdmin, Navicat or MySQL Workbench.

Note: Remember to update your wp-config.php file with your new database information.

Updating Old WordPress Database/Table Values

Tip Select 720 HD mode and watch it fullscreen or click the YouTube button to watch it on YouTube.

This step is very important, simply moving your database and linking it up to WordPress isn’t enough. Your old database/table (export/backup) will have all its old values: old host names and urls. To update these I run the followng MySQL query:

SET @from_host = 'www.oldhost.com';

SET @to_host = 'www.newhost.com';

UPDATE `wp_options` SET `option_value` = REPLACE(`option_value`, @from_host, @to_host);

UPDATE `wp_posts` SET `guid` = REPLACE(`guid`, @from_host, @to_host);

UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, @from_host, @to_host);

UPDATE `wp_comments` SET `comment_author_url` = REPLACE(`comment_author_url`, @from_host, @to_host);

UPDATE `wp_comments` SET `comment_content` = REPLACE(`comment_content`, @from_host, @to_host);

UPDATE `wp_links` SET `link_url` = REPLACE(`link_url`, @from_host, @to_host);

UPDATE `wp_postmeta` SET `meta_value` = REPLACE(`meta_value`, @from_host, @to_host);

UPDATE `wp_usermeta` SET `meta_value` = REPLACE(`meta_value`, @from_host, @to_host);

UPDATE `wp_commentmeta` SET `meta_value` = REPLACE(`meta_value`, @from_host, @to_host);

This will essentially do a search and replace of the old host/domain name to the new one. Change the two variables at the top, before you run the query (@from_host and @to_host).

Troubleshooting

Some WordPress installations will have plugins, that when transfered will prevent a site from properly working, until that plugin is deactivated and then reactivated. Additionally certain plugins have saved values in the wp_options table, these values may be old site directory paths, which are likely to break on a new host. A quick way of dealing with this is to deactivate all your plugins. You can do this by running the following SQL query:

DELETE FROM `wp_options` WHERE `option_name` = 'active_plugins';

As you reactivate, each plugin should reinitialize with correct directory paths and other values.

Warning some plugins may reset to their original default values, however some will continue to use their existing values.

22 responses to “WordPress Migration”

kamal March 16, 2011 at 8:32 am | Permalink

Thanks a lot for all those informations.
And thanks for all other posts who help us a lot.
Bob March 21, 2011 at 10:49 pm | Permalink

Very handy, I just updated my blog. Many thanks 🙂
yanti March 23, 2011 at 12:41 am | Permalink

hi ….
hi
I’m still confused wordpress problems, whether in wordpress we can make the search process itself, such as making the search process cost?
pls helpme …
thanks
Dimas March 24, 2011 at 10:34 am | Permalink

@yanti, I am not quite sure I understand what you are trying to say.
Joren Rapini July 1, 2011 at 3:20 pm | Permalink

Hello Dimas, we have a bit of an issue with migrating your WP Alchemy class. We started using it on a few websites, and it works brilliantly, however when we moved the website to another server, none of the WP alchemy data showed up in the new metaboxes on the new server, even though all we did was export/import database from phpmyadmin and then find/replace all the fields for the right domain, and then upload the files via FTP.

As soon as we pull a page up, all of the wp alchemy boxes are blank, even though we can compare databases on both servers and the wp_postmeta fields are all exactly the same. Is there anything we can do without having to re-enter in all the data? Thanks. And thanks for the awesome wordpress php class.
Dimas July 2, 2011 at 11:21 am | Permalink

Joren, this is the issue someone recently brought to my attention: Because WPAlchemy (and other WordPress plugins) store data as a serialized array … when you do a search and replace of the domain name, if the string lengths change (ie. http://www.example.com vs staging.example.com) the serialized data gets corrupt (The reason is, if you’ve ever looked at php serialized data, the lengths of strings are also recorded).

This is an issue I am aware of at least with the current process mentioned above (I’ll add a note about it).

To avoid this: It is best to not save the domain name of the site in WPAlchemy data, rather save an absolute path instead. Additionally, if doing a search and replace like you mention, trying to keep the domain names the same length (ie. http://www.example.com, dev.example.com, sometimes this is unavoidable though).

I do not have a good solution at the moment. However as a developer, I do understand the need to be able to export/import. With that said, my mind is in think mode for a good solution.
Ma'moun July 2, 2011 at 2:50 pm | Permalink

I think this is not applicable when using EXTRACT mode, right Dimas?
Dimas July 2, 2011 at 3:30 pm | Permalink

@Ma’moun, this is more of an issue with the technique, that is, using SQL to do a mass search and replace. Whether using WPAlchemy or not one should take caution as to what fields are being searched and replaced.

Using EXTRACT mode in WPAlchemy helps but it isn’t entirely immune. WPAlchemy EXTRACT mode extracts the variables only at a single level, so using it for certain types of repeating fields will still produce serialized data.
Joren Rapini July 5, 2011 at 7:48 am | Permalink

Dimas, thanks for your reply about exporting/importing WP Alchemy data. I don’t know why I didn’t realize that! Thanks, it’s not perfect but we at least know what to look out for.
Matt August 5, 2011 at 1:53 pm | Permalink

@Dimas

Do you have any solution yet to the problem of the custom meta data not showing when migrating a site to another server? Is there anything I can do to rescue the situation?

Unfortunately the problem has happened to me and I’m going to have to go through hundreds of articles re-adding the custom meta data again.
Dimas August 5, 2011 at 2:03 pm | Permalink

@Matt, see this comment, this is likely the reason why.

If indeed this is the case, I think writing a quick piece of code to do a safe-search-and-replace on the wp_postmeta table wouldn’t be difficult. Let me know if this is your particular circumstance, and I’ll see what I can do for you.
Stewart Duffy August 22, 2011 at 9:31 pm | Permalink

Hi all,

I have had a similar problem execpt it effected all the sidebar widgets which is also stored in a serialised-array.

I believe this maybe the solution to your problem – http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

I have had mixed results with it, but on the whole it seems to work sweet, keeping my widgets intact. This should be the case for the meta class too.
Dimas August 22, 2011 at 9:44 pm | Permalink

@Stewart, I was thinking for writing a quick search and replace routine myself … thanks for the link, I will check it out
Kevin Leary December 5, 2011 at 7:42 am | Permalink

I can vouch for the interconnectit.com script working. I use it to preserve sidebar widget and other WP options during a migration from localhost development to staging.
zignorp January 20, 2012 at 10:22 pm | Permalink

Hey Dimas,
I just switched my site, tried to switch from dev.xxx.org to http://www.xxx.org, but my server didn’t like that (bluehost) so I did the replace with the shorter domain name and am testing, everything seems fine, all my metabox data is preserved, and there’s lots of it. (I didn’t store the domain there). Is there a danger of things getting corrupted later?
Dimas January 20, 2012 at 10:32 pm | Permalink

If you’ve watched the video above I go into meta data corruption a bit, so there are some dangers … however if you do not have domain/hostname data in your post meta you should be fine …

Important: The data corruption is not noticeable by merely looking at the data in the database, it is only evident when you are looking at the data through the UI, wordpress (php) can not read the corrupted serialized meta data, so the meta data shows up as blank values in the UI.

A comment above has made a suggestion for script that may solve this problem, but I have not tested it myself.
Pete Saia March 22, 2012 at 10:32 am | Permalink

Here is a client side HTML5 solution which makes the database conversion instantly.

http://petesaia.com/work/peach/
Dimas March 22, 2012 at 10:49 am | Permalink

@Pete, very nice I like it … works well and takes care of the serialized data lengths!
Lincoln Lemos June 1, 2012 at 12:47 pm | Permalink

@Dimas

I have domain data on my post meta…
I do all steps, but still not working.

I generate the code on http://farinspace.github.com/wp-migrate-gen/?oh=http%3A%2F%2Flocalhost%3A8888%2Fmonet&nh=http%3A%2F%2Fwww.monetporcelanatos.com.br%2Fv1&ss=1&dp=1&sp=&s=Submit

I run the code on my sql, later this i use the plugin search and replace for change the domain names…

Later then i use the plugin, all meta post data it’s gone.
Lincoln Lemos June 1, 2012 at 1:01 pm | Permalink

I use the:
http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

and this work now…
Fournier November 9, 2012 at 1:12 am | Permalink

Hi,

I watched your very interesting video and would like to use the tool you present.
I am not very used to that sort of things so I would like to be sure of something as I don’t see any manual and I apologize if my question looks stupid.

Should I place the index.html file onto the root of my sever before running it ?
Steven Cook December 15, 2012 at 12:34 pm | Permalink

FYI, on my site the HTML5 ( http://petesaia.github.com/Peach/ ) solution didn’t find all my serialized data lengths but Stewart’s solution worked like a charm:

http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

Thanks