bavatuesdays

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).

Click 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.

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?

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_30_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_30_

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_30_ etc. for examples, the two tables above would no longer be `wp_wm_layers` and `wp_wm_objects`, but rather `wp_30_wm_layers` and `wp_30_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_30_

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).

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.

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.

Related posts

• Self-Service Feed Aggregation with WPMu (8)
• Porting bavatuesdays to a mapped domain on WPMu (10)
• WPMu Text Encoding Hell (9)
• WPMu Subdomain Mapping (2)
• WPMu Sitewide Tags, Feeds, & Archives! Oh, My! (9)