Migrating blogsome blogs

As I have mentioned here before, its been quite fun having the blog hosted at blogsome. The service there is pretty good but after two years, I fell for my desire to have a dedicated instance of [tag]wordpress.[/tag] The good support folks over there have it as a standard practice to provide you with a sql dump of your blog, if you ask nicely. Having a SQL dump of your blog means that you can migrate all of your blog’s posts and comments to your dedicated wordpress install.

The importing is not quite as easy one might think at first. Having done this, here are the steps I followed. These are somewhat unix-based instructions (vi, sed, etc).

1. Get a sql dump. Use the blogsome forums here and a search for sql dump should give you some hits to find out how.
2. Make a backup copy. You don’t want to screw it up and go asking for another dump. Its nice enough they gave you once.
3. Upon examining the SQL file, you will see why it is not a simple import. The table names are in the wp_username_tablename format, suggesting that it is a wordpress-mu based engine that is being used. WordPress uses the naming schema of wp_tablename. In my case, I moved from http://kreaper.blogsome.com to http://rajeev.name. My username there was kreaper. So, for e.g., the categories table in wordpress-mu is wp_kreaper_categories.

   It is simpler if your username has not changed during migrations. Open up the sql file in your editor and do a search and replace for wp_username_ and change them to wp_, basically getting rid of the username from the table names.

4. The sql file contains all the tables and all the data. If your posts contained your blog address, then you may want to change that too. On both blog sites, I was using the same permalink structure, called “Pretty Permalinks“. So if a post on my old blog site had a permalink of http://kreaper.blogsome.com/2007/03/23/filestat-the-featured-tool-on-netapp-tech-ontap/, then I would want my new link to read http://rajeev.name/2007/03/23/filestat-the-featured-tool-on-netapp-tech-ontap. That is also easy to do with a simple search and replace of kreaper.blogsome.com to rajeev.name.
5. On your new site, install wordpress and change the permalink structure to Pretty Permalinks. My hosting provider allows me to administer the mysql databases with phpMyAdmin, so I can compare the table structure. The schema has to be modified before we can import the blogsome sql into wordpress.

   To aid in this, what I did is split up the sql file into individual table sql files. One file per table. Each sql file should contain these corresponding commands/lines from your dump file. DROP, CREATE, LOCK, INSERT and UNLOCK. Taking the categories table for example, it should contains the following lines:
   DROP TABLE IF EXISTS `wp_categories`;
CREATE TABLE `wp_categories` (
`cat_ID` bigint(20) NOT NULL auto_increment,
`cat_name` varchar(55) NOT NULL default '',
`category_nicename` varchar(200) NOT NULL default '',
`category_description` longtext NOT NULL,
`category_parent` int(4) NOT NULL default '0',
PRIMARY KEY (`cat_ID`),
KEY `category_nicename` (`category_nicename`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOCK TABLES `wp_categories` WRITE;
INSERT INTO `wp_categories` VALUES ...
UNLOCK TABLES;


   Referring to the wordpress database description page, you will see that the wp_categories table structure is different.

   Which means that we’ll have to modify the schema on the import files and the add default values to those columns before we can use these sql files to import into wordpress. This should explain why I have opted for splitting out multiple files, one for each table. I chose to import these tables one by one, that way I can attend to those errors without re-doing the whole import again.

6. Depending upon what database administration capabilities you have on the wordpress server, there are multiple options available to you. Either way, work on one table at a time so you won’t lose time and sleep over it.

   Method One:

   1. If you have access to [tag]phpMyAdmin[/tag] setup to administer your wordpress installation, I would probably use this. (I took this route). Using phpMyAdmin, first import a sql table. This will cause the actual wordpress schema to be overwritten by the blogsome/wordpress-my table schema and its values.

   2. Go into phpMyAdmin, select the wordpress database and click on the table you just imported from the left sidebar. It should show you the imported table and its structure. Modify the schema here using the GUI so that it matches the wordpress database schema. Using the gui will also allow you add the default values to the existing rows.

   Method Two:

   1. If you are a command line kind-of-person, then you can modify the SQL files that you have generated for each table. Modify the schema first. Most tables require one or two new columns. Since you already have rows (data), you’ll have to add some default values to those rows that represent the newly added columns.

      I also believe that if you change the INSERT syntax and actually specify the column names that you are insert values into, then you do NOT have to pad the rows with default values for the new columns you have added.

   There is not much danger if you screw up a table — just drop it and re-create it ! You have the schema..

7. Once all the tables have been imported without any errors, go to the Administration section and change the settings accordingly. That should be it.