SQL script to migrate from Movable Type to WordPress

Posted by & filed under code.

While trying to migrate a large blog from Movable Type to WordPress, I found the built-in export and import functionality unable to handle volume of content on the blog or to properly preserve the primary keys needed for permalinks.

With assistance from Alvaro on the MisesDev list, we came up with the following MySql SQL script to import the entries directly from the Movable Type (5.01) database to WordPress (2.9.2). What would take many hours otherwise can be done in a minute or two. This is especially important if you don’t want to lose data during the time it takes to migrate the blog, as the script can be run immediately before the switch. This script also includes additional stuff like IP addresses and url-friendly names.

Note: this script is not generic. You must set the DB name and all the hard-coded URL’s for it to work with your blog:

USE `WordPress`;
 
/*  !!! Truncate tables to prevent primary key conflicts !!! */
TRUNCATE TABLE wp_posts;
TRUNCATE TABLE wp_comments;
TRUNCATE TABLE wp_users;
 
/* users from author */
INSERT INTO wp_users (
ID,
user_login,
user_pass,
user_nicename,
user_email,
user_url,
user_registered,
/* user_activation_key, */
user_status,
display_name
 
) (SELECT
author_id,
author_name,
author_password, /* fingers crossed */
IF (author_basename IS NOT NULL,author_basename,author_name) ,
author_email,
IF (author_url IS NOT NULL,author_url,' '),
author_created_on,
/* user_activation_key, */
author_status,
IF (author_nickname IS NOT NULL,author_nickname,author_name)
 FROM mtutf.mt_author); 
 
/*  post from entry */
INSERT INTO wp_posts (
ID,
post_author,
post_date,
post_date_gmt,
post_content,
post_title,
post_excerpt,
post_status,
comment_status,
ping_status,
/* post_password  */
post_name,
post_modified,
post_modified_gmt, /* --post_modified_gmt,  */
/* --post_content_filter,  */
/* --post_parent,  */
/* --guid,  */
/* --menu_order,  */
/* --post_type,  */
/* --post_mime_type,  */
comment_count,
to_ping,
pinged
) (
SELECT
entry_id,
entry_author_id,
entry_created_on,
CONVERT_TZ(entry_created_on,'+00:00','-06:00'), /* GMT */
CONCAT(entry_text,'<!--more-->',entry_text_more) ,
entry_title,
entry_excerpt,
TRIM(CAST(entry_status AS CHAR)),
TRIM(CAST(entry_allow_comments AS CHAR)),
TRIM(CAST(entry_allow_pings AS CHAR)),
/* --post_password  */
entry_basename,
/* --to_ping  */
/* --pinged  */
entry_modified_on,
CONVERT_TZ(entry_modified_on,'+00:00','-06:00'), /* -- post_modified_gmt,  */
/* --post_content_filter,  */
/* --post_parent,  */
/* --guid,  */
/* --menu_order,  */
/* --post_type,  */
/* --post_mime_type,  */
entry_comment_count,
'',
''
 FROM mtutf.mt_entry);
 
INSERT INTO wp_comments (
comment_ID,
comment_post_ID,
comment_author,
comment_author_email,
comment_author_url,
comment_author_IP,
comment_date,
comment_date_gmt,
comment_content,
/* comment_karma, junk_score? */
/* comment_approved, comment_junk_status?? */
/* comment_agent, */
/* comment_type, */
comment_parent,
user_id
 ) ( SELECT
comment_id,
comment_entry_id,
comment_author,
comment_email,
comment_url,
comment_ip,
comment_created_on,
CONVERT_TZ(comment_created_on,'+00:00','-06:00'), /* comment_date_gmt, */
comment_text,
/* comment_karma, */
/* comment_approved, */
/* comment_agent, */
/* comment_type, */
comment_parent_id,
comment_created_by
 FROM mtutf.mt_comment WHERE comment_junk_status = 1);
 
UPDATE wp_posts SET post_status = 'publish', comment_status='open', ping_status='open';
 
/*  More... */
 
UPDATE
wordpress.wp_posts, mises_blog.mt_entry
 
SET post_content = CONCAT(CONCAT(entry_text,'<!--more-->'), entry_text_more)
 
WHERE LENGTH(entry_text_more) &gt; 0
AND
mises_blog.mt_entry.entry_id = wp_posts.id
 
/* Specific to our DB: */
 
UPDATE wordpress.wp_posts SET guid = CONCAT('http://blog.mises.org/archives/', RIGHT(CONCAT('000000', ID),6), ".asp");
 
/* Set User Contributor Levels */
 
INSERT INTO wp_usermeta
(
user_id,
meta_key,
meta_value
)
(
SELECT
id,
'wp_user_level',
1
FROM wp_users
WHERE id  NOT  IN (2,295,3)
);
 
INSERT INTO wp_usermeta
(
user_id,
meta_key,
meta_value
)
(
SELECT
id,
'nickname',
display_name
FROM wp_users
WHERE id  NOT  IN (3,295)
)
 
INSERT INTO wp_usermeta
(
user_id,
meta_key,
meta_value
)
(
SELECT
id,
'wp_capabilities',
meta_value = 'a:1:{s:6:"author";b:1;}'
FROM wp_users
WHERE id  NOT  IN (2,295,3)
)

6 Responses to “SQL script to migrate from Movable Type to WordPress”

  1. Ben

    I just wanted to let you (and anyone else who visits this site looking for a MT>WP SQL importer) to know there is quite a bit of code in this script that is specific to your site.

    In other words, running this on a more generic MT install will not work (for example it looks like your url structure reflects files ending in .asp, which is v unusual).

    I commend you for open sourcing this script, I’m just wanting to let others know – especially if they not completely proficient with SQL – that this script *will not work* out of the box on another site without some major editing.

    Thanks,

    Reply
  2. massimo

    hallo,
    great work. but, as Ben said, it laks abstraction from the context. in particular, you should omit all the stuff related to your specific website/db name. more, it seems that you do nothing to import categories converting into wordpress “terms” and term_relationship.
    but you did a great work anyway :)

    Reply
  3. John

    Is there a “generic” script that *can* complete a successful import? I’m looking but cannot find a way to accomplish this. :-(

    Reply
  4. David Davidson

    There is no generic script – that would require some code to get the required variables. But this script should work for any site with just a few changes.

    Reply

Leave a Reply to Hans Mestrum