How-to: Migrate data from WordPress 2.9 to WordPress 3.0(subsite)

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.

Use PuTTY to Connect to iPhone on Windows by SSH

Before this post, I tried WinSCP as the SSH client mentioned by How to install Secure Shell (SSH) in HackTheiPhone. At the first time, it did works. But later when i wanna try to run some hacks, WinSCP can never connect to iPhone any more. Here’s what it says all the time:

WinSCP Error

I decide to find a replacement.

I googled around and soon i found one called by PuTTY created by Simon Tatham. “PuTTY (the Telnet and SSH client itself) ” — Download Link(444KB).”

After downloading, I connect my iPhone with my laptop(IBM-T40, Windows XP Sp2). Then I launched PuTTY. After typing into my iPhone’s ip address, PuTTY soon found my iPhone and asked for user and password. I gave them to it and then it immediately connected to iPhone successfully. And here’s the screenshot:
PuTTY On iPhone

Cool, isn’t it?! 😀

Update: For some reason, WinSCP works now. One more choice, isnt’ it :-)

Update(Dec.25,2007): When you cannot connect with iphone via WinSCP, please double check:

1. if you enter iphone’s IP address correct or not
Sometime, for my own experience, the IP from Settings->Wifi is NOT CORRECT. I have to get the right one from my router’s client connection list.

2. if iphone’s SSH Server is running or not
Most of time it is running, if you don’t do too much to your iphone. Anyway, you can check by this cool application – sysInfo.

Update(Mar.26,2008): get your exact iphone’s IP address by running ifconfig under Term-v100 or Putty. If WinScp cannot connect to the iPhone for the first time, give it another try. Sometime it’s caused by the time SSH uses to generate the key beyoned the default time Winscp uses to establish the connection. The second try will fix it.