Migrating Drupal Database to WordPress – JA Media Solutions | GIS Services, Software and Web Development

Migrating Drupal Database to WordPress

Home » Blog » Migrating Drupal Database to WordPress

Written by Aldwin Galapon posted on Thursday, January 9th, 2014

0.00 avg. rating (0% score) - 0 votes

I had a previous task of moving a database of a Drupal 7.1 website to a temporary WordPress 3.8 installation. Through roughly few hours of searching the web and testing scripts on my desktop (Environment: Windows 7 Professional, Apache 2.2.25, PHP 5.3.27, MySQL 5.6, phpMyAdmin 4.0.5, Drupal 7.10 [2011-12-05] database, and a fresh WordPress 3.8 installation), I got everything moved almost exactly as from the original Drupal site except for small details where the site created other post / page types besides ‘pages’ (e.g. it had ‘trip’, ‘webform’, ‘area_link’, etc.). To address this issue I had to implement some changes in my ‘Permalink’ and installed other plugins to maintain original links from Drupal to WordPress.

For this example / walk through, I set up my database names as ‘wordpress’ for WordPress 3.8 installation database and ‘drupal’ for the Drupal database imported to my local database server.

Note: Before you try doing this, please make sure you test it first in your local machine. I do not recommend doing it on a live website without going through the test. Your installation and environment maybe different from this tutorial and any untoward or unexpected errors may happen.

Substitute ‘wordpress’ and ‘drupal’ to your own database names.

Drupal Database to WordPress Database: Walk Through

  1. Make sure you have Drupal database has been restored in your database either through phpMyAdmin, MySQL Workbench or via command line.
  2. Prepare your freshly install WordPress 3.8 database by truncating default posts / pages / comments after installation. Paste and run query below in phpMyAdmin or on your MySQL Browser:
    TRUNCATE TABLE wordpress.wp_comments;
    TRUNCATE TABLE wordpress.wp_links;
    TRUNCATE TABLE wordpress.wp_postmeta;
    TRUNCATE TABLE wordpress.wp_posts;
    TRUNCATE TABLE wordpress.wp_term_relationships;
    TRUNCATE TABLE wordpress.wp_term_taxonomy;
    TRUNCATE TABLE wordpress.wp_terms;
    
  3. Depending on your source database if you have a number of users to migrate you may need to run the following scripts, otherwise you can skip this step:
    DELETE FROM wordpress.wp_users WHERE ID > 1;
    DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;
    
  4. Migrate tags:

    INSERT INTO wordpress.wp_terms (term_id, name, slug, term_group)
    SELECT
    	d.tid,
    	d.name,
    	REPLACE(LOWER(d.name), ' ', '-'),
    	0
    FROM drupal.taxonomy_term_data d
    INNER JOIN drupal.taxonomy_term_hierarchy h USING(tid);
    

    Migrate taxonomy terms / vocabulary:

    INSERT INTO wordpress.wp_term_taxonomy (term_id, taxonomy, description, parent)
    SELECT
    	d.tid `term_id`,
    	'category' `taxonomy`,
    	d.description `description`,
    	h.parent `parent`
    FROM drupal.taxonomy_term_data d
    INNER JOIN drupal.taxonomy_term_hierarchy h USING(tid);
    
  5. Copy over your Drupal posts to your WordPress database. Note that the issue I raised before can be seen here. The all post types will be inserted to your destination database:
    INSERT INTO wordpress.wp_posts (id, post_author, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, post_status, to_ping, pinged, post_content_filtered)

    SELECT DISTINCT
    	n.nid `id`,
    	n.uid `post_author`,
    	FROM_UNIXTIME(n.created) `post_date`,
    	r.body_value `post_content`,
    	n.title `post_title`,
    	r.body_summary `post_excerpt`,
    	IF(SUBSTR(a.alias, 11, 1) = '/', SUBSTR(a.alias, 12), a.alias) `post_name`,
    	FROM_UNIXTIME(n.changed) `post_modified`,
    	n.type `post_type`,
    	IF(n.status = 1, 'publish', 'private') `post_status`,
    	'',
    	'',
    	''
    FROM drupal.node n, drupal.field_data_body r, drupal.url_alias a
    WHERE n.vid = r.entity_id AND a.source = CONCAT('node/', n.nid);
    

    Note that the fields to_ping, pinged, post_content_filtered have been added to the insert query since error shows they have ‘no default value’ when we try to run the script.

    If you would like to see all copied posts in your WordPress just simply run this script:

    UPDATE wordpress.wp_posts
    SET post_type = 'post'
    WHERE post_type <> 'page' OR post_type <> 'post';
    
  6. Update post to tag / category relationship:

    INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
    SELECT
    	nid,
    	tid
    FROM drupal.taxonomy_index;
    

    Update tags / category post count:

    UPDATE wordpress.wp_term_taxonomy tt
    SET `count` = (SELECT COUNT(tr.object_id)
    FROM wordpress.wp_term_relationships tr
    WHERE tr.term_taxonomy_id = tt.term_taxonomy_id);
    
  7. Insert comments to posts, if your original website database have disabled comments or use third-party commenting solutions lke Disqus, you won’t need to run these scripts:

    INSERT INTO wordpress.wp_comments (comment_post_ID, comment_date, comment_content, comment_parent, comment_author, comment_author_email, comment_author_url, comment_approved)

    SELECT DISTINCT
    	nid,
    	FROM_UNIXTIME(created),
    	comment_body_value,
    	thread,
    	name,
    	mail,
    	homepage,
    	((status + 1) % 2)
    FROM drupal.comment, drupal.field_data_comment_body;
    

    Update post comments count:

    UPDATE wordpress.wp_posts
    SET `comment_count` = (SELECT COUNT(`comment_post_id`)
    FROM wordpress.wp_comments
    WHERE wordpress.wp_posts.`id` = wordpress.wp_comments.`comment_post_id`);
    
  8. The following code is suppose to help fix taxonomy – that is if it was set up correctly in the original Drupal site.

    UPDATE IGNORE wordpress.wp_term_relationships, wordpress.wp_term_taxonomy
    SET wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_taxonomy_id
    WHERE wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_id
  9. Insert authors to your database, note that your admin account has already the ID ‘1’, therefore users table from drupal is filtered only with IDs greater than ‘1’. Otherwise if you have no other users in your Drupal database, you can already skip this step:

    INSERT IGNORE INTO wordpress.wp_users (ID, user_login, user_pass, user_nicename, user_email, user_registered, user_activation_key, user_status, display_name)

    SELECT DISTINCT
    	u.uid,
    	u.mail,
    	NULL,
    	u.name,
    	u.mail,
    	FROM_UNIXTIME(created),
    	'',
    	0,
    	u.name
    FROM drupal.users u
    INNER JOIN drupal.users_roles r
    USING (uid)
    WHERE (1 AND u.uid > 1);
    

    Asign author roles / permissions to users:

    INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
    SELECT DISTINCT
    	u.uid,
    	'wp_jams_capabilities',
    	'a:1:{s:6:"author";s:1:"1";}'
    FROM drupal.users u
    INNER JOIN drupal.users_roles r
    USING (uid)
    WHERE (1 AND u.uid > 1);
    INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
    SELECT DISTINCT
    	u.uid,
    	'wp_jams_user_level',
    	'2'
    FROM drupal.users u
    INNER JOIN drupal.users_roles r
    USING (uid)
    WHERE (1 AND u.uid > 1);
    

    Assign and give administrator rights / privileges:

    UPDATE wordpress.wp_usermeta
    SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
    WHERE user_id IN (1) AND meta_key = 'wp_jams_capabilities';
    UPDATE wordpress.wp_usermeta
    SET meta_value = '10'
    WHERE user_id IN (1) AND meta_key = 'wp_jams_user_level';
    
  10. We are not quite finished yet. If you’ll need to move you Drupal images and files inside your WordPress as I did, I created an ‘uploads’ folder in wp-content, then ‘old’ to have ‘/wp-content/uploads/old/’ and copy all Drupals files, images etc from ‘/sites/default/files/’ and then fix image and file urls with this script:

    UPDATE wordpress.wp_posts
    SET post_content = REPLACE(post_content, ‘”/sites/default/files/’, ‘”/wp-content/uploads/old/’);
  11. Finally, we can fix permalinks by using plugins such as ‘Permalink Editor’ to customize page / post urls. There are also other plugins where you can customize tags and categories slugs too.

Posted on Thursday, January 9th, 2014 at 12:06 pm Print this Article Email this Article