This is exactly how I get my original data of cuimengsuo.com back online to cuiziqi.com/cui/en.
My method requires you have an idea of database, a little bit MySQL programming skills, and a DIY-holic spirit.
First allow me to introduce my situation, you know, the context.
1.I’ve been added a new sub site http://cuiziqi.com/cui/en (Thanks to the new WP 3.0, the steps are pretty easy, check out the official guide here. Naming it en ‘coz the posts are in English).
2.and I’ve had a database online where all my previous data of cuimengsuo.com are stored there.
3.Besides, I’ve uploaded all my uploads(/wp-contents/uploads) to my current hosting server(since I already knew that all subsites of WP 3.0 share the same uploads folder, I then uploaded them to that folder as well).
One more thing, a simple look at the database you’ll know that WP will save the all of your new sites’ data in the tables with the prefix wp_2_, like wp_2_posts, wp_2_terms, etc. Besides, further side by side table compare will tell you that which table/data are created by your installed plug-ins, other than WP itself(I assume here your new site are a fresh install until now, like I did).
Now time to figure out what exactly you wanna transfer.
In my case, all I care about is my posts, pages, tags, categories and comments. No links, since I didn’t have many. No user either, since I’m the only author.
Warning: I suggest you do a database backup before start. Log on to your phpMyadmin panel, open two tabs which shows your the old and the new database.
OK,now showtime.
first, the posts and the pages, accordingly the table wp_posts.
1. Modify the old wp_posts table.
>>rename it to wp_2_posts.
>>remove the field post_category, ‘coz there’s no such a field in the new wp_2_posts table accordingly.
>>update the domain name string in the post_content. In my case, from http://cuimengsuo.com to http://cuiziqi.com/cui/en.
update wp_posts set post_content=replace(post_content,’http://cuimengsuo.com’,'http://cuiziqi.com/cui/en’) WHERE instr(`post_content`,’http://cuimengsuo.com’)>0
>>export the data to a zipped sql file and save it to your local hard disk.
2.Modify the new wp_2_posts
>>clear the table
>>uncheck the field ID‘s AUTO_INCREMENT property.
3.Transfer the data.
>>goto the new database and import the data.
4.Enable the new table wp_2_posts.ID‘s AUTO_INCREMENT.
5.Done.
Visit your blog and you should see your posts back. Great, eh? Not finished yet, let’s move on.
Now comments, obviously the table is wp_comments(I just leave wp_commentmeta out and seems until now everything works fine. but if you think it wrong, please correct me via comments). Most of the steps are similar to what we’ve done in posts actually. Here they are.
1.Update the old wp_comments table.
>>rename wp_comments into wp_2_comments
>>Url replacement in two fields: `comment_author_url and comment_content.
update wp_2_comments
set `comment_author_url`=replace(`comment_author_url`,’http://cuimengsuo.com’,'http://cuiziqi.com/cui/en’),comment_content=replace(`comment_content`,’http://cuimengsuo.com’,'http://cuiziqi.com/cui/en’)
2.Prepare the new wp_2_comments table.
>>do the clear, like we do in the posts;
>>uncheck comment_ID‘s AUTO_INCREMENT.
3.Export the data from old table and then import it into the new one.
4.Restore the new table’s AUTO_INCREMENT.
Last, the tags and categories. They all are in the following tables: wp_2_terms,wp_2_term_relationships and wp_2_term_taxonomy.
Basically the steps are the same: clear data for the new table, disable AUTO_INCREMENT if necessary, do the export and import, finally enable AUTO_INCREMENT accordingly.
All done and enjoy.
Cui – On My Way
