MySQL – Skere9

I just migrated this Skere9 blog from a Drupal implementation at SiteGround to a WordPress instance at Amazon Web Services. I’ve been a customer of AWS for three years and finally decided to move this blog to the same platform where I’ve been doing other work. It’s cost-effective and AWS provides significantly greater control over numerous aspects of the deployment.

Note: I have one author – myself – and I did not migrate any comments with this move. I will add those later.

The Drupal version is 7.4.1. The WordPress version is 4.5.3.

To migrate the blog posts, I took the following steps.

I temporarily installed phpMyAdmin at the Drupal site to export the “node” and “field_data_body” tables from Drupal. (Note: this could have also been done using MySQL directly.)
I installed phpMyAdmin at the WordPress site to import the Drupal tables. (Note: this could have also been done using MySQL directly.) I edited the phpMyAdmin config file to restrict access to phpMyAdmin to my own IP address.
I executed the script below via phpMyAdmin at the WordPress site to pull data from the imported Drupal tables and insert that data into the WordPress database.

INSERT INTO wp_posts
(       id
      , post_author
      , post_date
      , post_date_gmt
      , post_content
      , post_title
      , post_status
      , comment_status
      , ping_status
      , post_name
      , post_modified
      , post_modified_gmt
      , post_parent
      , guid
      , menu_order
      , post_type
)
SELECT
        a.nid+100
      , 1
      , FROM_UNIXTIME(a.created)
      , FROM_UNIXTIME(a.created)
      , b.body_value
      , a.title
      , 'publish'
      , 'open'
      , 'open'
      , CONCAT('dr-', a.nid+100)
      , FROM_UNIXTIME(a.changed)
      , FROM_UNIXTIME(a.changed)
      , 0
      , CONCAT('http://skere9.com/?p=', a.nid+100)
      , 0
      , 'post'
FROM     node a LEFT JOIN (field_data_body b)
            ON (a.nid = b.entity_id)
WHERE    type = 'blog'
ORDER BY a.nid

A few important notes about the above code:

I had fewer than 100 existing posts in the WordPress instance- it was a brand new installation. This is why I only added 100 to the ID, to create unique identifiers.
I transformed the data formats from UNIX to plain text, as required by the source and target tables.
I chose to enter each value into the WordPress “postname” field with a “dr-” prefix – for “Drupal”. But of course that could have been anything, or nothing at all – no prefix is required here.
The “_gmt” fields in the target database are being treated by my WordPress implementation as the same as the local time. Presumably GMT refers to Greenwich Mean Time, comparable to UTC. I’m in the USA eastern time zone (New York City zone – I’m in Washington, DC), so presumably the offset should be 5 hours but the existing WordPress implementation I have isn’t treating the times differently. Therefore I didn’t treat them differently either – I made the “created” timestamp the same for the local and GMT entry, and did the same for the “changed” timestamp.

It all worked like a charm.

Tag: MySQL

MySQL Migration: from Drupal to WordPress