Importing a single WP blog to a WPMu Installation

As promised, I am going to go through the steps I followed bringing this blog from a single WordPress installation into a WPMu environment. For some background on all of this check out these posts here and here.

So, I mentioned previously here just how unhappy I have been with the WordPress import and export functions. Not only is the importer inconsistent, but it also creates quirky XML that isn’t as clean as it should be and imports only some of your precious data, while leaving out the blogroll, plugin tables, and a variety of other necessities germane to an export/import tool. These issues led me to experiment with importing the actual database tables of an existing single-installation of WordPress into a WPMu database. It wasn’t all that hard, but there might be a few things to keep in mind as you give it a shot.

But before we get started there is one disclaimer: If you don’t back up all your databases and core files for both your single WP and WPMu installs before trying anything like this, then you’re a crack baby!

Let us go, now…

Step 1: Export Your Single WP Installation Database

First thing you need to do is go into your CPanel and look for the phpMyAdmin interface on your server (this is my GUI of choice because I am line-command impaired) and export a copy of your WP single installation database. This is pretty straight-forward, just find the database for your blog, if you have several WP blogs set up and don’t know which is which, then browse the wp-options table and you should see the blog title listed in this table (see example here).

Image of phpMyAdmin Database ExportClick on image for larger version.

Also, be sure to save the file you your disk so we can open it up and start changing a few things.
Image of Download dialog box

Step 2: Open Up the Exported SQL file in a Text Editor

Open up the SQL file you downloaded in a text editor of some kind. Keep in mind that if you choose the .gz format (a compression algorithm which is quite useful for large SQL files) you will have to double-click on the file to de-compress it before you start editing. If you use a Mac I recommend TextMate ($) or Text Wrangler (Free), and while the editing we will be doing to the .SQL file is very basic, the searching, finding, replacing of text and general formatting options will be easier with a good editor. As for PC based text editors, I’m not so sure, some good recommendations anyone?

SQL File in text editor

Click on image above for larger version.

Step 3: Explanation of WPMu database table structure

The WPMu Tutorials site has two posts about the basic structure of the WPMu database. It is a useful overview that explains how the deafult blog in a WPMu database has the table prefix wp_1_ as logic would have it the next blog you create on a WPMu installation will be wp_2_ etc. This is a different from a single WP installation in that the single installation only has the wp_ prefix without the numbers (which connote a blog id in WPMu). For example, whne importing my single installation in WPMu, I already had several blogs within that environment, so when I mapped the new domain for bavatuesdays, the database table structure was already up to 30:

wp_options is the table name on WPMu vs. the wp_options that is in my single installation. So, in short, just about every database table you have in a single WordPress installation (which will not include plugin databases only you have already set them up on the new WPMu blog you are importing to) will have a corresponding table in WPMu with an appropriately number table prefix, in my case wp_

Step 4: Editing the SQL database file

Before we go in and change the table names, one thing you might consider is cleaning out some on the kipple in your database. For me, this amounts to getting rid of all the spam caught by Spam Karma 2, and deleting the <code>sk_2_blacklist</code>, <code>wp_sk2_logs</code>, and anything else that takes up a lot of unnecessarily table space. For example, the 404 error log I run on my site (which is a plugin as is SpamKarma 2 and if they are not installed on your site they will not see these tables, and chances are your database will be far cleaner and lighter than mine is).

As for deleting database tables from an SQL text file, it is pretty simple because SQL files a very specific format that you can easily follow in a text document, here is an example:


-- --------------------------------------------------------


— Table structure for table `wp_wm_layers`

CREATE TABLE `wp_wm_layers` (
`id` mediumint(9) NOT NULL auto_increment,
`layer_title` tinytext NOT NULL,
`layer_name` tinytext NOT NULL,
`layer_type` tinytext NOT NULL,
`layer_url` mediumtext NOT NULL,
`layer_params` mediumtext NOT NULL,
`layer_bounds` mediumtext NOT NULL,
`layer_options` mediumtext NOT NULL,
`layer_size` mediumtext NOT NULL,
`layer_unavailable` tinyint(1) NOT NULL default ‘1’,
`layer_hide` tinyint(1) NOT NULL default ‘1’,
`rss_cachetime` mediumint(9) NOT NULL default ‘0’,
`layer_author` bigint(20) NOT NULL default ‘0’,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `wp_wm_layers`

— ——————————————————–


— Table structure for table `wp_wm_objects`

CREATE TABLE `wp_wm_objects` (
`id` mediumint(9) NOT NULL auto_increment,
`object_name` tinytext NOT NULL,
`layer_name` tinytext NOT NULL,
`object_type` tinytext NOT NULL,
`object_bounds` tinytext NOT NULL,
`object_color` tinytext NOT NULL,
`object_width` tinytext NOT NULL,
`object_hide` tinyint(1) NOT NULL default ‘1’,
`object_author` bigint(20) NOT NULL default ‘0’,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


— Dumping data for table `wp_wm_objects`

— ——————————————————–

As you can see from the two example of seaparate database tables above, SQL has a very regular logic to how it separates tables with hasmarks, and creates new tables, and populates the relevant information. So, having no knowledge of SQL, which is my current state, i just got rid of the tables that were extraneous (like Spam Karma and the 404 Error log) and renamed all of the wp_ prefixes to wp_ etc. for examples, the two tables above would no longer be `wp_wm_layers` and `wp_wm_objects`, but rather `wp_wm_layers` and `wp_wm_objects`. I didn;t have to edit any of the actual SQL code, but just rename the tables that are being created to match the blog id on WPMu. Does this make any sense?

Step 5: A quick find and replace all for table names

So, the quick and easy way to do this replacement is to use the text editor to find `wp_ and replace all instances with `wp_

This should do the trick, but this is also why it is very important to do a database backup of both installations just in case something goes wrong. Also, if anyone out there who knows more about SQL than me, and there are millions of you, pleeeeeeease peer review this post 🙂

Save the SQL file and now we move to deleting the existing tables for this blog in WPMu and importing the ones from the modified SQL file we have been working on.

Step 6: Deleting existing tables

The sixth step may seem counter-intuitive (and there may be a better way), but in my limited experience I found I had to go into the WPMu database and delete all the tables for the blog I created and want to import the single-install database to. For example, the new blog on WPMu for bavatuesdays has the database prefix wp_30 (your may very well be different depending on how many blogs you have created and what the corresponding number is). So, I go into my WPMu database, find all the tables that have the prefix wp_30 and drop them (scary I know, but trust me, I’m an amateur).

Dump blog database by relevant prefix

Please note: You will need to dump all of the tables associated with the blog you want to import. Try not to be confused by my example here because I have already done this and some tables are related to plugins. for a list of the tables you should be deleting with only the wp_# prefix, for example:

wp_#_categories
wp_#_comments
wp_#_link2cat
wp_#_links
wp_#_options
wp_#_post2cat
wp_#_postmeta
wp_#_posts
wp_#_terms
wp_#_term_relationships
wp_#_term_taxonomy

Where the # is the number of the blog you have created to which you will be importing the SQL file. Am I being clear here?
As another note, wp_#_terms, wp_#_term_relationships, wp_#_term_taxonomy are all specific to WP version 2.3, so be sure your blog is updated to the lastest version before you try this!

Step 7: Importing the new SQL blog database
Once you have dropped all the relevant tables, you will then go to the import tab in phpMyAdmin and upload the modified SQL file we have been working through. This should be relatively painless, return to the root of the wpmu database, make sure you are not within a specific table, and then click on the import button, find the saved SQL file we have been modifying and import it.

Image of SQL Import in phpMyAdmin

And that should be it, but let me say two things before I end this post:
a) I’m a hack and this worked for me, but may prove unsuccessful for you, so if you try it please backup your stuff
b) I hope far smarter than me who have done this before and know the intricacies better will chime in and correct any flagrant errors or misleading passages that could mislead or somehow screw someone’s attempts up.

With that said, go to it and let me knwo if you have an issues that I can try and help out with.

This entry was posted in Uncategorized, WordPress, wordpress multi-user, wpmu and tagged , , , , , , , , , . Bookmark the permalink.

73 Responses to Importing a single WP blog to a WPMu Installation

  1. Andrea says:

    Looks pretty thorough to me. I think I’m going to point people over here. 😀

  2. Pingback: WPMU Tutorials » Wordpress to WordpressMU importing

  3. Justin says:

    I’m gonna try this out soon, fingers crossed

  4. Lens says:

    I just tried to import a WP2.3.3 into WPMU 1.3.3 (/blog) following the steps above. But now I can’t access the backend of that particular blog.

    I removed all plugins in the plugins folder, and set active_plugins in wp_#_options to a:0:{}, but still the same.

    Firefox says:
    —-
    The page isn’t redirecting properly

    Firefox has detected that the server is redirecting the request for this address in a way that will never complete.

    * This problem can sometimes be caused by disabling or refusing to accept cookies.
    —-

    I guess I’m missing something somewhere…

  5. Jim says:

    Hi Lens,

    One question, are the domains for the two blogs the same?

    If not you bneed to go into the wp-options table in the blog database and change option_id 1 and option_id 40 (these are tables) with the new address of your blog. For example, if your blog was http://myblog.com and is now http://home.myblog.com you need to change these values. Does this make sense?

  6. Jim says:

    Also Lens,

    Am I right that wp_# is really the relevant number of your blog in WPMu, for example wp_1, wp_2, wp_3, etc. It shouldn’t be wp_# for that is a generic symbol for that actual blog id number.

  7. Lens says:

    Hi Jim,
    I changed the urls in wp_#_options (1 and 40) before I tested. (# is 67 in this case)

    By the way, I had to add a row to wp_blog_versions with my blogid and database version (6124). Somehow it was not added when I created the blog.

    I think the problem has something to do with users/roles/capabilities. When I try to edit the blog from the site admin (wpmu-blogs.php), I can add a user but the list with roles is empty.

    By the way, I deleted wp_#_users and wp_#_usermeta because WPMU has its own tables for that. No effect, though.

  8. Jim says:

    Lens,

    Check out the wp_67_user_roles within the wp_67_options table, this should have something like this in it:

    That was a stupid suggestion on my part, sorry. I am still looking into this, let me know if you figure anything out.

    Update: I guess this was related to lens’s issue, so see the image below on Flickr.

  9. Lens says:

    It turns out it wasn’t stupid at all, Jim.

    After a few hours of scratching my pentablet I took a look at the wp_67_options table and noticed that I hadn’t changed option_name wp_user_roles into wp_67_user_roles.
    That did the trick, now I can login to the backend.

    Another thing about the options: I had to manually add WPLANG and change the upload_path. So maybe there are more options that could cause trouble when importing a single blog into WPMU.

  10. reverend says:

    Lens,

    Thanks for letting me know, I was thinking the wp_user_roles table was just pre-populated info you were sure to have, so a struck that suggestion. Glad to see my blindness didn’t totally lead you astray 🙂

    Based on your suggestions here, I am going to update this post and cover some of these finer details, so thanks a million for the feedback and keep it coming.

    Jim

  11. Pingback: élet és könyvtár » Blog Archive » Ajánlott olvasmány 2008.04.01.

  12. Craig says:

    I just tried this for the first (and smallest) of the six blogs I’m bringing into one WPMU install.

    The other sites are remaining live whilst I ensure everything is working on a different domain. I found that leaving the _options table means I have minimal changes to make whilst not having Len’s troubles above.

    Everything seems to be working perfectly: thanks for the tutorial. Just five more to go.

  13. Reverend says:

    Very cool Craig, and glad to be of service. Let me know if you run into any snags, it is always useful to know, because with every new version this stuff changes quick.

  14. Craig says:

    No problems at all: I simply didn’t export the wp_options table which caused me problems in my first attempts.

    The only hassle I had was trying to create a blog called domain.tld/blog (yes, I’m using THAT structure). It didn’t like that at all; kept redirecting me to the “home” admin pages or throwing 404’s. Strange but true.

  15. donny says:

    I’m new to PHP and recently setup my local machine with PHP and MySQL for doing development. I was sort of stuck when I needed to post my work for the user to test and review. After looking around a bit I found a site that hosts PHP and MySQL apps. I was surprised that it was free – it seems they’re offering the service at no cost until 2012. At that point they’ll change over to a fee-based service. However, in the meantime, it’s a great place to do anything from demo and sandbox right up to posting sites for real.

    Their pitch is as follows:

    “This is absolutely free, there is no catch. You get 350 MB of disk space and 100 GB bandwidth. They also have cPanel control panel which is amazing and easy to use website builder. Moreover, there is not any kind of advertising on your pages.”

    Check it out using this link:

    http://www.000webhost.com/83188.html

    Important: There’s one catch in that you must make sure you visit the account every 14 days – otherwise the account is marked ‘Inactive’ and the files are deleted!!!

    Thanks and good luck!

  16. Adrian says:

    Hi there

    Thanks so much for this how-to: it’s saved me a lot of brain-ache. I was trying to export from a stand-alone WP install to WPMU, but 95% of the time WPMU wouldn’t import, and when it did import (after making sure all line breaks in the file were of the same kind), it missed random things out for some reason. After a few little tweaks in the SQL file (changing the uploads directory for example) it’s working perfectly.

    Cheers!

    Adrian

  17. Reverend says:

    Adrian,

    Excellent, glad to be of service, and I agree with you entirely that the process should be a bit smoother than it currently is, here’s to hoping.

  18. Paamayim says:

    Thanks for the guide.

    What about users? If I want to migrate users from the single WP to WPMU that ‘s already filled with other users?

    I think I have to manually add new users and change post IDs to link to new added users?

    Involved WPMU tables are wp_users and wp_usermeta?

  19. Adrian says:

    @Paamayim

    I don’t have an answer to your specific question, but having tried to import users to a WPMU installation from a stand-alone WordPress installation, one thing that I couldn’t work out was how to import their passwords – they didn’t seem to work out-of-the-box on the new system, which I suppose may have something to do with LOGGED_IN_KEY, AUTH_KEY and SECURE_AUTH_KEY settings in wp-config.php for both installations. Not knowing a great deal about that kind of thing I just gave up, but it might be something to watch out for.

    @Reverend

    A little glitch that happened on my WPMU installation after importing a WordPress installation using PHPMyAdmin was that the categories on one of my blogs got a bit screwy. Adding a category called “Editorial” would always add a category called “press association”, which was in fact a tag on a post in that blog. My (not very elegant) solution was to keep adding gobbledygook categories until they stopped being renamed (which took a while, I can tell you), then deleting them all, and also deleting all the categories in the (I think) site_categories table. I don’t know whether it was the transfer of the blogs that caused this, or whether it was a Batch Category Edit plugin that I was using (I’ve used it since without any problems), but if you have problems after this kind of transfer, having a look at the site_categories table might be a good starting point.

    Cheers

    Adrian

  20. pepijn says:

    hi,

    this is already a lot better than all this import/export stuff.
    but i am actually looking for the reverse thing.

    i have a local installation of wpmu, in wich i manage local copies of websites for testing and developing, i don’t want to install and update al those local copies of wordpress.

    this works great, except… now i have to use the export function to get the developed installation online.

    maybe one should write a plugin to import/export between single wp en wpmu.
    i guess it should be possible to select/update all tables with a given id.
    something like “SELECT * FROM LIKE ‘wp_” . $id . “_%’ but i don’t think you’re allowed to use like on table names…

  21. Don Callaway says:

    try this to get your tables…

    SELECT table_name, column_name, data_type [,…]
    FROM information_schema.columns
    WHERE table_schema = dbname and table_name like ‘…’;

    …and then iterate through the record set to do what you want to each table.

  22. Jake says:

    This worked great, I do have one question though that I am unsure of. When I did this and then looked at the blog it was almost as if I iFramed it. In other words the theme and all followed it and in the address bar was the URL back to the actual blog. Did I miss somehing?

  23. Reverend says:

    Jake,

    Yeah, you need to go to the blog in the Site-Admin tab and click on the edit link. The edit the addresses there so that they reflect the new blog address, that would be very important. If this isn’t clear, give me an explanation of the wpmu setup, is it a mapped domain? Is it a new subdomain blog? Etc.

  24. achmad.cn says:

    it’s totally difficult for me, but I’ll try it. thanks !

  25. Pingback: One day in June (why not today…) | Iva is me.

  26. Hello Reverent,

    I just moved my largest blog and I notice that all the users are gone in the new location WP.Mu,

    The blog has multiple authors and only my user shows the right post count the rest are in the air.

    anyway to get this fix?

    The rest worked perfectly

    Thanks,
    W.

  27. Kristi says:

    Ok, I’m attempting to do this for a friend and it’s freaking me out since I don’t have to much PHP and SQL knowledge. 🙂 Who can I hire to do this for me? LOL – It looks over my head!

  28. Reverend says:

    Kristi,

    You can do it, and let me know if you run into issues and I’ll help. In fact there is a whole internet out there full of help and resources. Use the force!

  29. Reverend says:

    Waldemar,

    Did you solve your issue?

  30. Hello Reverent,

    Yes everything is fixed, the users have to be in place before doing the import.

    Thanks

  31. I have a question about step 5 where you say “Save the SQL file and now we move to deleting the existing tables for this blog in WPMu and importing the ones from the modified SQL file we have been working on.”

    Surely up to this point we’ve been working with our standard wordpress install and have yet to install wordpressmu? Do I not need to delete the database, install wpmu and then import?

  32. pixelgirl says:

    Hi,

    thanx for the tutorial. 🙂 But if I only want to import posts, users and comments, can I delete all other stuff from the sql file and import then?

  33. oscar says:

    Jim,

    Thanks for the tutorial.
    Does this migration include copying over the images from the old site?

    Thanks.

  34. Reverend says:

    Oscar,

    Copying over images from the old site is an issue I’m not sure I address here, but if it is a new blog on WPMU, here is ho I handled that:

    On a stand alone blog it is
    https://bavatuesdays.com/wp-content/uploads/2009/09/batman_clock-480×244.jpg

    IN WPMu, you would need to do a quick find and replace of the SQL file you will be importing from this url
    https://bavatuesdays.com/wp-content/uploads/2009/09/batman_clock-480×244.jpg
    to this
    https://bavatuesdays.com/wp-content/uploads/2009/09/batman_clock-480×244.jpg

    So, in other words, I would find and replace everything in the database that was https://bavatuesdays.com/wp-content/uploads/
    with https://bavatuesdays.com/wp-content/uploads/

    Make sense?

    Let me know,
    Jim

  35. Reverend says:

    @pixelgirl

    I don’t see why you couldn’t—did you try this approach?

  36. Claire says:

    I think it may be worth putting a note on here about migrating users.

    You CANNOT simply drop the wp_users and wp_usermeta tables and re-upload the WordPress ones. If you do this, not only are you missing two fields that MU needs (‘spam’, ‘deleted’) but you also LOSE the WPMU ‘Site admin’ account and can only manage your default blog. There is NO way of getting these permissions back. The account is set using complicated metadata which can only be recovered by wiping everything and starting again.

    I spent about three hours following your (excellent) guide but gave up when I realised I couldn’t go any further because I’d dropped the WPMU admin account and couldn’t reinstate it.

    I’m tempted to wait until WP and WPMU merge since one slip of the mouse can render hours of work unusable 🙁 If only there were an automated upgrade script.

  37. Claire says:

    Sorry, please ignore/delete the above comment, I had a separate problem which was not related!

  38. Reverend says:

    @Claire,

    What was your issue, if you post it here it could help others searching for a solution. Either way, glad it worked out.

  39. Claire says:

    Short answer: I was running before I could walk!

    Basically, admin usernames can be specified in the Site Admin section, but I couldn’t get to the Site Admin section because I’d already deleted the Admin account!

  40. Mark says:

    Reverend Jim, just to be clear here: All uploaded images have to be moved from the ‘uploads’ directory to the ‘files’ directory and other file links suitably changed(?)

    Thanks.

  41. Kat says:

    Thank you so much for this tutorial; once I changed the wp-options ids 1 and 40, it was perfect!

  42. Pingback: iTech Engine » Migrate from WordPress to WordPress MU – How to

  43. great. thanks for the info. i’m in progress to upgrade my wordpress single to the multisite one.

  44. DailyNewsZw says:

    After migrating data from wordpress to wordpressmu, each time I try to add or modify a category on the WPMU admin dashboard, the category is created with a differen name than what I would have given it. The names seem to be picked out from the wp_X_terms table. I have to go into that table in myphpadmin, and change the name to the correct name I intend to use, in order for it to stick on the WPMU dashboard. Attempting to correct the category name on the dashboard only leads to the next term in the wp_x_terms table being picked out instead, to replace whatever was there before, and what I type in is not taken. Please provide response and help in as layman terms as possible, so I know how to implement solution. Thanks,

  45. Reverend says:

    @DailyNewsZw,
    Pretty sure WPMu deals with categories from a sitewide table, rather than blog-by-blog, might that be the issue?

  46. Pingback: Forum cheat sheet - WPMU Tutorials

  47. Pingback: How to Convert a WordPress Blog into WordPress MU | Wordpress Arena

  48. wparena says:

    Well explained but there is one more easy way to convert and you can find on following link…you link also mention there
    http://wparena.com/how-to/how-to-convert-a-wordpress-blog-into-wordpress-mu/

  49. Tyler says:

    OK, I did find this useful.

    But I stayed on this page all day because I loved the header pic…

    Then when I tried to grab it I found the rotator and holy shit you have the best header pics ever!

    Thanks, and now I just need to track down some of those films.

  50. Pingback: The Bava Headers at bavatuesdays

Leave a Reply to Lens Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.