paths

How to Move a WordPress Website Using MYSQL and phpMyAdmin

There are plenty of pros and cons to consider before choosing the right Content Management System (CMS) to use from project to project: WordPress, Drupal, MODx etc. One nice feature of using a system like Drupal and MODx is the use of relative paths in URL link location when storing parameters in the database. WordPress on the other hand uses absolute paths in URL linking.

NOTE:
“Absolute Paths: An absolute path is created whenever your link uses the full URL of an object or page. For instance, http://www.communitymx.com is an absolute path to a specific web site. This method is the best choice whenever you need to send a visitor to another site or need to get content from another site. While you can use it within your own site, there is rarely ever a need to do so.

Relative Paths: As you can probably guess, relative paths are used much more frequently than absolute paths. Any time you need to send a visitor to another page within your site or include an object from your site (like an image) on one of your pages a relative link will work just fine. Which form of relative link you should use will depend on how the site is constructed. Document and root relative paths each have a place in the building of a web site. Read on for a break down of each type.” -  Bryan Ashcraft

If you decide to change the URL of your WordPress website, for instance:  http://www.example.com/ to http://www.new-example.com/ there are some steps that you need to consider.

Also within your WP blog post content itself users may also use the old URLs when creating reference backlinks. All these values in the database will need to be changed when WordPress is moved. I’ll show you which database fields have reference or values related to the website’s content URLs that you want to modify.

The first thing to do is make a backup copy and move all files over. Once this has finished the first step is to tell WordPress the new URL location. We’ll be using SQL statements based on mySQL replace() function to directly modify the database, so you will need access to Here is the way to modify the value via MySQL database. You will need access to your phpMyAdmin or login to the DB server and run MySQL client as root. Use the following SQL command to update the new location of your WordPress website URL:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.example.com', 'http://www.new-example.com') WHERE option_name = 'home' OR option_name = 'siteurl';

After that you will need to fix URLs of the WordPress posts and pages, which translated from post slug, and stored in database wp_posts table as guide field. The URL values in this field are stored as absolute URLs instead of relative URLs, so it needs to be changed with the following SQL query:

UPDATE wp_posts SET guid = replace(guid, 'http://www.example.com', 'http://www.new-example.com');

If you have created reference backlinks within blog posts or pages with absolute URLs, these links will point to wrong locations after you move the blog location. Use the following SQL commands to fix all the internal links:

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');

There you go, take a look through your WP website to make sure everything is working the way it should.

Hope this helps!

Comments
  1. OMG! I could have used this post two weeks ago to migrate a client’s blog from my dev. server to their live. I will definitely use these db replaces next time. Thanks!

  2. Ewen Cameron

    Hi this was very useful, but then I also read the statement below from the official WordPress Codex – Does the 2nd SQL Query you listed above change the guide settings as described below? If so it is stated that this should NEVER be changed, although being a newbie II could be getting mixed up.

    Important GUID Note

    When doing the above and changing the URLs directly in the database, you will come across instances of the URL being located in the “guid” column in the wp_posts tables.

    It is critical that you do NOT change the contents of this field.

    The term “GUID” stands for “Globally Unique Identifier”. It is a field that is intended to hold an identifier for the post which a) is unique across the whole of space and time and b) never, ever changes. The GUID field is primarily used to create the WordPress feeds.

    When a feed-reader is reading feeds, it uses the contents of the GUID field to know whether or not it has displayed a particular item before. It does this in one of various ways, but the most common method is simply to store a list of GUID’s that it has already displayed and “marked as read” or similar.

    Thus, changing the GUID will mean that many feedreaders will suddenly display your content in the user’s reader again as if it was new content, possibly annoying your users.

    In order for the GUID field to be “globally” unique, it is an accepted convention that the URL or some representation of the URL is used. Thus, if you own example.com, then you’re the only one using example.com and thus it’s unique to you and your site. This is why WordPress uses the permalink, or some form thereof, for the GUID.

    However, the second part of that is that the GUID must never change. Even if you shift domains around, the post is still the same post, even in a new location. Feed readers being shifted to your new feeds when you change URLs should still know that they’ve read some of your posts before, and thus the GUID must remain unchanged.

    Never, ever, change the contents of the GUID column, under any circumstances.

  3. I know this if off topic but I’m looking into starting my own blog and was curious what all is required to get set up? I’m assuming having
    a blog like yours would cost a pretty penny? I’m not very web savvy so I’m
    not 100% certain. Any recommendations or advice would be greatly appreciated. Appreciate it

    • It’ cost very little in terms of money, it costs you a lot of time, so if your going to start your own blog, you need to make sure you’ll have the time to take care of it!

      Hope it works out for you!
      Cheers.

  4. I’ve moved WordPress numerous times doing the search and replace manually in the exported .sql file. So… Just because I’m lazy and would rather find out by asking rather than trial or error… Does this method lose the theme options? My method breaks the serialization of the theme options panel found in most themes, which isn’t that huge of a deal it just takes me about 10 minutes (if there are a lot of options) to go in and redo them.

    Thanks in advance,
    Dave

    • Hi Dave,
      No, it shouldn’t break anything, all this is doing is replacing any leftover development domain alias ie www.dev.example.com and replacing them with www.example.com.

      Obviously first you should backup your data, but ya this is definitely one of the most effective technique. there are also a couple plugins that will do the work for you, root-relative-urls would work.

      Cheers.

      • Dave

        Thanks a lot Chris, I’ll give your method a try next time!

      • Dave

        Hi again Chris, I used this great free script to move a site online from my localhost today, very quick and easy. Give it a try sometime.

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

        • Thanks Dave, Looks good for sure, just have to be really careful with it, and not forget about it in your root or someone could really mess with you! :)

          Cheers

  5. Richard Leslie

    I have moved my wordpress blog from a server to my local laptop. The siteurl and home are set correctly to http://localhost:8888/wordpress in the wp_options table, and I have also changed the wp_posts table guid column to point to the correct url. All my website works apart from the home page……why is this? Any ideas? cheers

  6. Karen Stevenson

    Hi!

    I’m having problems seeing my relative href/src links after transfering my website from live to development on a WAMP localhost. I thought what you suggested above would solve but for me it must be something else.

    Images don’t show up with a relative url (i.e, src=”/wp-content/uploads/…/image.jpg”

    When I view the page source and click on the relative image link I get a 404 Not Found with the following message:
    The requested URL /wp-content/uploads/2013/03/DSC_0107.jpg was not found on this server.
    and the url in the address bar is:
    http://localhost/wp-content/uploads/2013/03/DSC_0107.jpg

    (it’s missing the wordpress directory after localhost)

    I thought the absolute path was built with the siteurl (which is http://localhost/wordpress ) but maybe not!?!?

    Thanks for any help or insight you might have!
    Karen

  7. Hurrah, that’s what I was looking for, what a stuff! existing here at this web site, thanks admin of this website.

  8. You actually make it seem really easy with your presentation but I
    to find this matter to be really one thing which I feel I’d never understand. It kind of feels too complex and extremely vast for me. I’m taking a
    look forward to your subsequent publish, I will
    try to get the cling of it!

  9. Hi there, just became alert to your blog through Google, and found that it
    is truly informative. I am gonna watch out for brussels.
    I’ll appreciate if you continue this in future. Many people will be benefited from your writing. Cheers!

ADD YOUR COMMENT