Delicious is one of those services that few people know about but loved (and probably hated in equal measure) by those who use it. Their offering is easy to grasp: Delicious is a social bookmarking service that lets you "Save, organize, and remember the links you find interesting or useful around the web." I'm sure you've been in a situation where you've tried to remember the source for a particular factoid or needed to cite an article but lost track of the link. The obvious place to save links is in the browser bookmarks but this quickly becomes unwieldy if, for example, you're doing research on lots of different topics. Unless you install bookmark syncing software, browser-based bookmarks are also cumbersome if you run different browsers and computers. I used Delicious to store thousands of links over the years, all of which where tagged and easy to call up when needed. They say in their about page, "Delicious remembers so you don't have to. It's easy to build up a collection of links, essentially creating your own personal search engine." It was great at that and absolutely invaluable if, like me, you have a poor memory. Programmers, bloggers, researchers, anyone who needs to save a link for future recall needs something like Delicious. Founded in 2003, it's also a veteran in an industry where anything that lasts more than a couple of years is considered successful.
Sadly, this glowing opening isn't to recommend the service. You'd think its longevity would be a sign of a rock-solid, mature platform but no: Delicious' loyal user-base has been rewarded with outages, lost data and broken features. That people persisted despite years of problems shows just how useful a service it provides. When the site went down again earlier this year (2016), I decided it was time to make a move. Unfortunately I couldn't find an alternative trustworthy enough for several years-worth of carefully curated bookmarks. Free web services are notorious for disappearing into a black hole with all your data. As the saying goes, you get what you pay for.
After a long search, I began to realise there wasn't anyone out there who could be trusted. This isn't just Delicious and bookmark curation problem though. These past few years has shown that The Cloud hasn't lived up to its promise. Data breaches, surveillance, data collection, outages, and catastrophic meltdowns; we can't really trust a third-party service, especially if they don't have a clear business model. I grudgingly conceded that cobbling together my own solution was the only option.
The rest of this post describes my solution, why I chose WordPress and how you can convert your WordPress site to host a personal bookmarking service. This will be valuable even for those who've never used Delicious but would like a way to curate links.
Exporting the bookmarks
Before doing anything else, the bookmarks need to be in form that's easy to manipulate. Delicious had a feature to export all your links, tags and notes into a HTML file Netscape bookmark format. At the time of writing this has been 'temporarily disabled due to server load' (really, Delicious?). Fortunately I was in the habit of making frequent backups due to Delicious' unreliability so my export file was only a few weeks old. I threw together a quick Python script called DeliciousPy to scrape the file and save the results into a local MySQL database. Feel free to grab the source over on GitLab.
It was a one-use tool so a little rough and undocumented. (Sorry, it wasn't written with the intention of public release!) Anyone with basic Python knowledge should be able to easily figure out how it works. Essentially, I parse the Delicious export file using Thibauld Nion's Netscape bookmark parser that is part of his Water On Mars! project. Everything is then inserted into three MySQL database tables:
bookmarks: the table storing bookmark title, url, note, creation time and privacy flag.
tags: all the tags associated with the Delicious bookmarks.
tag_relationships: the relationships between the bookmarks and tags.
After doing a pip install to install the requirements, you run DeliciousPy with the command:
$ ./deliciouspy.py -p /path/to/delicious/export_file.html
Running the script exports:
Bookmark title
URL
Tags
Note
Created date
Getting the Delicious bookmarks into a database opens up options for importing the data into a different platform. You can roll your own custom tool or use a content management system. I chose WordPress.
WordPress as a personal social bookmarking service
My wish list for a Delicious replacement was short:
Save links with accompanying notes.
Tagging and search to easily call up previously saved links.
Simple, bare-bones functionality free of unnecessary features.
Set links to private for personal research or public for sharing.
Avoid relying on a third-party service.
It didn't take me long to settle on WordPress. I already have expertise with importing data into WordPress from my Drupal to WordPress migration service. It's grown into a mature content management platform with a huge user and developer base, apparently powering 25% of all websites. It has excellent documentation, can run on a standard hosting package, has a simple user-interface and a flexible taxonomy system. The code is open source so if you self-host the site, you won't run into the third-party service problems of getting locked out of your data. WordPress has all the components for a home-brew bookmarking service.
My Bookmarks plugin
I'd originally hoped to avoid building my own plugin but none of those in the WordPress Plugin Directory suited my needs. Most were too feature-packed and I wanted something lightweight: just have some way of saving and tagging links for later retrieval. Putting together simple bookmark storage isn't difficult: 'bookmarks' can be nothing more than a post with a single custom field to store the URL. To keep them separate from standard blog posts, I created a custom bookmark content type and packaged it into a plugin called My Bookmarks. (I'm not very imaginative when naming things.)
Delicious has a social bookmarking aspect that lets you publish your links, follow other users and discover pages those in your network have saved. Social bookmarking wasn't compelling for me so I didn't attempt to include this functionality. In my opinion, publishing the links on a blog is social enough and any anyone who wants to 'follow' can subscribe to the RSS feed built into WordPress.
Bookmark sharing id controlled through the normal WordPress Visiblity setting. Set to Public so the bookmark gets posted on your blog; set to Private so it's visible only after logging in to WordPress.
One great thing about Delicious was a handy bookmarklet that allowed you bookmark your browser's current open page. Rather annoyingly, this feature broke for me at some point after it was acquired by one of its many owners. I implemented this functionality in the form of a WordPress-style Press This bookmarklet. Code from the now seemingly abandoned Linkmarklet plugin by Jonathan Christopher does most of this work.
Experience so far
The My Bookmarks plugin hasn't been published to the WordPress Plugin Directory as of now. I'm still trialling it to make sure everything works as expected before releasing but you're welcome to grab the source and tinker with it for your own needs. Note: As of December 2016, the My Bookmarks plugin is temporarily hosted over on GitLab.
Currently the plugin is installed on my internal Wordpress-based Knowledge Management System on my local network. It serves up thousands of bookmarks from around 10 years worth of links. I replicated everything that made Delicous so useful for me without all the drawbacks of a third-party service. So far so good. The bookmarks that I've spent years curating are now fully under my control—and under my responsibility. Backups are still important but bookmarks are now included with my usual automated WordPress database dumps. All-in-all, I think it was a good move to finally migrate from Delicious.
Book photo by Katia Grimmer-Laversanne from freeimages.com
If you are using my Drupal to WordPress Migration Tool or have hired me for Drupal to WordPress migration service, it’s important that you gather as much information as you can about your Drupal installation. The more detailed you are in your investigation, the fewer surprises there will be during the actual migration. You may have hired a web development company or freelancer to build your site and they’ll sometimes be able to provide you with the necessary information. However, gathering this information can involve some work so they may be reluctant to provide help without an extra fee.
You can use the questions below to help structure your investigation process. I can help you with this if you’re not able to run the investigation yourself. Please ask for details.
Drupal Installation Questions
What are the FTP login details of your hosting provider?You will need these details to make a backup of your Drupal site. When the migration is complete, you’ll need them to install WordPress on your server.
FTP Credentials
Host
Port
Protocol
Username
Password
Send through secure channel e.g. password manager
What are the database login details of your hosting provider?You’ll need these to export your Drupal database to perform the migration.
Database Credentials
Host
Port
Database name
Username
Password
Send through secure channel e.g. password manager
Do you know how to export your database to a dumpfile?If you’re unsure how to export your database to a ‘dumpfile’, please contact your hosting provider. They will likely have tools such as phpMyAdmin and help articles to guide you through the process.
Yes/No
I know how to export my database to a dump file
I need help exporting my database to a dump file
What version of Drupal are you running?The database column names differ slightly between Drupal versions. This means that a different set MySQL queries will be needed to perform the migration. You can sometimes find this information using your installation’s CHANGELOG file at http://[YOURDOMAIN.COM]/CHANGELOG.txt. Some developers remove this file as part of their security hardening process so it may not exist in your installation.
Drupal version
List your Drupal modules and site functionalityCan the functionality can be handled with existing WordPress plugins? Will you need to develop custom plugins?
Drupal modules and functionality
How many custom content types do you have set up?Additional migration MySQL queries will be needed for each Drupal custom content type. WordPress supports page and post content types as standard. Additional development work will be needed to support other content types.
Drupal custom content types
How many custom fields do you have set up?As with custom content types, custom fields in your Drupal installation will need additional work to support them under WordPress.
Drupal custom fields
Do you have blocks, views and panes with important content?Many Drupal sites display content in blocks, views and panes. These will also need additional work to support under WordPress.
Drupal blocks, views and panes
How many nodes do you have?The number of Drupal nodes you have does not usually play a big factor in the complexity of the migration. However, it will still be useful to get an idea of the number since many nodes can have an impact on how long it takes to troubleshoot migration problems.
Approximate number of Drupal nodes
Do you expect to have multiple aliases referencing the same post?Drupal supports multiple URL aliases for nodes. These will need to be resolved when migrating to WordPress. For more information, please see my article Preserving SEO during a Drupal to WordPress migration.
Brief description of what to do with multiple aliases
It’s OK if you don’t know. We can discuss your options.
Brief description of what to do with duplicate terms
It’s OK if you don’t know. We can discuss your options.
Will your terms exceed the WordPress’ 200 character length?WordPress has a 200 character limit for terms. Any Drupal terms longer than 200 characters will need to be truncated.
Brief description of what to do with problem terms
It’s OK if you don’t know. We can discuss your options.
How many users do you have?The number of users doesn’t play a big factor during a migration but it may be useful to know.
Approximate number of users
How many user roles do you have?Your Drupal user roles may need to be converted into WordPress roles.
List and brief description of user roles
It’s OK if you don’t know. We can discuss your options.
Aside from migrating to WordPress, do you also plan to redesign the site?Redesigning the site will obviously involve more work as it will require a custom WordPress theme. Some site owners are happy to use a free or premium off-the-shelf theme template.
Ideas for the WordPress theme
e.g. list the names, URLs and prices of any pre-made template candidates
Are there existing WordPress plugins that match your Drupal functionality?If you’re happy to be flexible, you may find WordPress plugins that closely match any Drupal modules you have installed on your existing site. Set aside some time to search the WordPress Plugin Directory. If you don’t find anything suitable, you may have to consider setting aside some budget for custom plugin development.
List some possible WordPress plugins you may have found
This article is about preserving SEO during a Drupal to WordPress migration. It’s not about which platform is better. A web search will bring up many articles about the relative SEO merits of Drupal versus WordPress. I’ve found the best summary on that topic in a forum post by Drupal.org user and developer, John Birchall:
“The consensus that WordPress has better SEO is a guess…WordPress fans will guess one way, Drupal fans the other. In my opinion, WordPress SEO plugins work better for sites which are short on skill or time or money. If you want to give your clients hand-made quality, Drupal does rather a good job. This also goes for SEO. Nevertheless, lack of developer skill and client budget does sometimes mean we are better off simply ‘reheating’ and rolling out the wonderful prepackaged solutions available for WordPress.” (Slightly paraphrased for clarity.)
If you’re reading this post, I will assume that you’ve already decided to migrate your site to WordPress. Rather than re-hashing the Drupal versus WordPress SEO debate, I’ll focus on some technical details that affect your SEO efforts when migrating from Drupal to WordPress. First, let’s start with some basics.
Can I preserve my SEO during the migration?
The quick answer is, ‘Yes’. It is possible to preserve search engine optimisation during a migration. However, it can be easy to overlook the idea that you might end up with better SEO by revisiting your approach entirely. I’ve had site migrations where replicating optimisations from the Drupal version wouldn’t have made a significant impact in the business—or at the very least, where SEO was something that could be improved after the migration. If your site falls into this category, I would not recommend putting too much into preserving your Drupal optimisations. The money spent hiring me or anyone else may be better spent on improving the WordPress SEO after exporting your Drupal content.
There are some easy and low-cost approaches you can take such as:
Using or building an SEO-friendly and responsive WordPress theme.
Installing SEO WordPress plugins.
Creating higher quality content.
Improving page load times. WordPress is a lighter CMS so you should get this ‘out of the box’ but you can lose any gains if, for example, you replicate calls to third-party libraries.
Of course, you should consult an expert if you rely heavily on search traffic. Preserving SEO may be vital for your site but it’s worth making a conscious assessment about the investment needed.
What kind of work is involved in preserving SEO?
The underlying differences between Drupal and WordPress mean preserving SEO often takes the bulk of a migration budget. The amount of work needed depends on what you’re trying to preserve. To give an example from a past migration project where SEO was vital, tasks included:
Pre-migration auditing, mapping and analysing links on the Drupal site;
Developing an SEO-friendly custom theme;
Creating redirects for every URL that would change on WordPress;
Making further adjustments as directed by an SEO consultant after she analysed her webmaster tools.
Remember that SEO isn’t a one-time effort. The project needed multiple months of on-going work. Further, I was taking direction from the client’s SEO consultant who incurred her own separate fees. Thus the client allocated a sizeable budget for SEO alone. In his case it was worth the money because the Drupal site earned him many times that amount on revenue from search traffic. Minimising any negative impact on SEO after migrating to WordPress was a priority.
How much time and money you’ll need to allocate for your migration depends on a range of factors that are very specific to your site. You’ll only know after doing some in-depth analysis and documentation. Here’s an article on Search Engine Journal that I send to clients when they ask about the SEO impact of a CMS migration: How To Avoid SEO Disaster During a Website Redesign. It’s worth a read and afterwards you can think about how much SEO should play a part of your site migration project.
SEO considerations
What exactly will affect SEO during a typical Drupal to WordPress migration? Here are a few SEO considerations to keep in mind for your migration project. It’s certainly not an exhaustive list but paying attention to these points should cover the basics.
On-page SEO
A lot of the on-page SEO comes over as a natural side-effect of most content migration processes. For example, page titles, content keyword optimisation, sub-heading tags, image file names and internal linking are all embedded into the Drupal node content. These remain intact when the nodes are imported into WordPress, unless content cleaning or other manipulation is part of the project.
Other areas, such as URL structure, meta data and load speed will need work due to the differences of the two systems.
Off-site SEO
Off-site SEO will be unaffected by a CMS migration as long as you have the necessary redirects set up to catch URL changes for any backlinks. This is an effort that’s external to the site so not within the scope of this article.
Drupal aliases vs WordPress permalinks
URL structure is one of the SEO areas that will change after a CMS migration. A big part is because of some important differences between how Drupal and WordPress handle URLs. Drupal creates URLs using either the node ID or a URL alias stored in its database’s url_alias table. Take a look at some sample entries.
You’ll see an entry for node/28 associated with a URL alias projects/april. This means that under Drupal, the page with node ID 28 can be accessed using either http://example.com/node/28 or http://example.com/projects/april. Not shown is that content can have multiple aliases. The site could therefore also have another entry node/28 associated with URL alias projects-april and yet another with my-projects/april. You will always be able to find a page by appending the URL alias entry in the url_alias table to the site’s domain name.
WordPress has a different mechanism for creating URLs. Rather than relying on database entries, it generates rewrite rules and builds the permalink dynamically depending on your permalink settings. WordPress’ closest equivalent to the Drupal url_alias is its permalink slug stored as the post_name in the wp_posts table. Under my standard Drupal to WordPress migration, I migrate over the Drupal aliases by converting them into WordPress post name slugs. (See the table mapping in a separate article, Drupal to WordPress migration: posts table mapping.) If you have the Post name structure set in the WordPress Permalinks Settings, you’d then find the Project April page at either http://example.com/?p=28 or http://example.com/april/. Note that this would cause an SEO problem because the new WordPress link does not match the old Drupal link.
We would have to do some extra work to solve this problem. For example, if the april node was a page, we could create and link to a parent Projects page to create the Drupal http://example.com/projects/april structure. Alternatively, if it was a post, we could create a projects category or tag and set the Category base or Tag base setting. We could also create .htaccess redirects or use one of the many WordPress redirection plugins. The exact solution would depend on your project’s requirements.
WordPress permalinks have more constraints
Aside from the differences in how URLs are stored and generated, you should also be aware of WordPress permalink constrains. These will have a big impact on the amount of work needed to preserve your Drupal SEO when migrating to WordPress. I’ll briefly summarise them below.
The Drupal URL alias has a 255 character limit but the WordPress the post_name field stores only 200 characters. Make sure to keep track of any truncated posts names and create a corresponding redirect.
Since there’s no WordPress equivalent of the Drupal url_alias table, you’ll need to decide which alias will be converted into the post name. You should create separate redirects for the other aliases.
WordPress slugs which create the permalink are essentially a URL-friendly version of the post title. WordPress automatically cleans up the post title to create slugs for new content but you may encounter problems when migrating Druapl aliases into slugs. Characters that would be valid within a Drupal alias are invalid as a WordPress slug. For example, you cannot have forward slashes, accented characters and quotes in your WordPress slug. You’ll need to clean up and keep track of any problem aliases, otherwise WordPress will not be able to serve the page.
As previously mentioned, under Drupal you will always be able to find a page by appending the URL alias entry to the site’s domain name. WordPress does not store any records of its URL structure. You will end up with broken links and a potential SEO nightmare if you change the Permalink structure settings without consideration.
Remember your Drupal taxonomies
It’s easy to forget Drupal’s powerful taxonomy system is capable of generating listings that attract valuable traffic. Use webmaster tools to audit your site traffic to pick up on any such listing pages. Unlike Drupal which allows for multiple vocabularies, WordPress only offers one set of categories and tags out of the box. Replicating your Drupal taxonomy listings in WordPress may take considerable development but a quick fix may be to manually replicate the important listings pages. Obviously the size of your site will dictate if this will be feasible.
Menu and breadcrumb navigation
Menu structure and breadcrumb navigation is often an important part of your SEO. Ensure that your selected WordPress theme both supports breadcrumbs (some don’t) and the same type of breadcrumbs e.g. hierarchy, attribute and history-based breadcrumbs.
Migrate metatags generated by Drupal metatag modules
Also remember that you may have installed modules that generate metatag information contributing to your SEO. Export these from the relevant Drupal table and import them into the WordPress database using the format for your preferred WordPress metatag plugin. For example, if you used the Drupal metatag module and would like to use the WordPress Add-Meta-Tags plugin, you’ll need to extract data from the metatags table and insert them into wp_postmeta table as a meta_key and meta_value pair. Keep in mind that some investigative work will be necessary to find out how your preferred modules and plugins store their data.
Search engine visibility
WordPress has a setting to discourage search engines from indexing site. It can be found in Settings > Reading > Search Engine Visibility. It’s normal to have this checked during development but make sure you disable it when launching.
Load speed
WordPress, in general, requires fewer hosting resources than Drupal. You could very well find that a slow site on an under-performing server running Drupal becomes more responsive under WordPress. Don’t take this for granted, however. Use the migration as an opportunity to make improvements. Thoroughly vet plugin candidates to make sure they’re not pulling in third-party scripts that will increase page load times. Also avoid bloated page-builder themes. In my experience, these can be terrible for load speed as they try to cram in every conceivable bit functionality, thereby drastically slowing down what could have been a nimble site.
Preserving SEO during a Drupal to WordPress migration can be lots of work
As may have gathered by now, the amount of work and budget needed will reply on many factors. It’s not possible to prescribe what will be necessary for your site without in-depth detail about your configuration and requirements. Nevertheless, I hopefully will have given you enough information to get started with planning an approach.
A little plug to keep the lights running. If you think all of this work is too much trouble, please consider hiring me for your Drupal to WordPress migration project.
I'm a huge fan of Bare Bones Software's Yojimbo, an extremely useful tool for keeping information organised. It has been one of my most important applications since around 2008. I rely on Yojimbo as my 'Anything Bucket' to save scraps of information, from code snippets and troubleshooting notes to project logs and research sources. A downside is that it's only available for Apple Mac OS X and therefore was a factor in tying me to the Mac OS X platform.
When I started moving back to Linux for day-to-day development work, finding a Yojimbo replacement was a top priority. A colleague pointed me towards the GNOME project's Tomboy application which runs on Linux, Unix, Windows, and Mac. Tomboy is a basic though perfectly usable cross-platform alternative but of course, I still needed to find a way of migrating my old Yojimbo notes.
Fortunately, Yojimbo has a sharing feature called Sidekick that exports your data to a set of web pages. Digging in to the Tomboy Note XML format showed me that Sidekick together with a bit of Python and some BeautifulSoup magic would provide a quick solution. I threw together pyYojimbo2Tomboy which I'm releasing under the The MIT License on GitHub in case anyone else needs it.
Keep in mind that Sidekick does not export the Yojimbo note metadata, like tags and modification times, but you can to migrate the title and note body over to Tomboy. Time permitting, I would like to find a way to save the extra metadata in a future version.
Though Bare Bones Software hasn't released any updates and new features in a while, I still highly recommend Yojimbo for anyone on Mac OS X who needs an information organiser. It's a little dated and doesn't offer modern features you'd expect like an easy way to sync to the cloud and across devices. Personally, I find this one of Yojimbo's strengths. An app in 2016 that doesn't keep pestering you to push your information to some remote data centre with questionable privacy policies is somewhat refreshing.
Hopefully having a way to export data to a multi-platform open source alternative will reassure anyone worried about getting locked-in.
To read the mapping, you look up the Drupal table on the left listing the fields we use for a migration. Directly to its right is the WordPress table with the corresponding field in the same row. So for example, the nid in Drupal’s node table is exported to the id field in the WordPress wp_posts table.
I have listed all the fields used in the query. If a Drupal field shows no mapping in the WordPress table, it is being used to match entries in another table for a join. Here we use the vid field in node and node_revisions for an INNER JOIN.
Table mapping for WordPress wp_posts
Drupal 6.x
WordPress 3.x
Notes
node
wp_posts
nid
id
id
post_author
created
post_date
Create date from UNIX timestamp
title
post_title
changed
post_modified
Create date from UNIX timestamp
type
post_type
status
post_status
vid
node_revisions
body
post_content
teaser
post_excerpt
vid
node
url_alias
nid
dst
post_name
If dst field is NULL, use nid
src
to_ping
Whitespace string
pinged
Whitespace string
post_content_filtered
Whitespace string
Query
REPLACE 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’,
DATE_ADD(FROM_UNIXTIME(0), interval n.created second) ‘post_date’,
r.body ‘post_content’,
n.title ‘post_title’,
r.teaser ‘post_excerpt’,
IF(a.dst IS NULL,n.nid, SUBSTRING_INDEX(a.dst, ‘/’, -1)) ‘post_name’,
DATE_ADD(FROM_UNIXTIME(0), interval n.changed second) ‘post_modified’,
n.type ‘post_type’,
IF(n.status = 1, ‘publish’, ‘private’) ‘post_status’,
‘ ‘,
‘ ‘,
‘ ‘
FROM drupal.node n
INNER JOIN drupal.node_revisions r USING(vid)
LEFT OUTER JOIN drupal.url_alias a
ON a.src = CONCAT(‘node/’, n.nid)
WHERE n.type IN (
/* List the content types you want to migrate */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);
Table mapping for WordPress terms
This table mapping exports the Drupal terms into WordPress.
Drupal 6.x
WordPress 3.x
Notes
term_data
wp_terms
tid
term_id
name
name
name
slug
Make lower case and convert spaces to underscores
vid
term_group
Not used in a default WordPress installation
term_data
wp_term_taxonomy
tid
term_taxonomy_id
tid
term_id
taxonomy
String: ‘post_tag’ or ‘category’
description
description
parent
0 (No parent)
In the WordPress Taxonomy documentation, “term_group is a means of grouping together similar terms.” During a standard migration, the WordPress term_group is set to the Drupal vocabulary ID, which seems to make sense. Nevertheless, a default WordPress installation does not actually use the value for anything. It may have been included by the developers for future expandability or use by plugins.
term_group=0 is the default value when creating a term using the Drupal user interface.
Below, we associate posts with the newly migrated terms.
Drupal 6.x
WordPress 3.x
term_node
wp_term_relationships
nid
object_id
tid
term_taxonomy_id
Table mapping for WordPress users
This maps Drupal user export to WordPress.
Drupal 6.x
WordPress 3.x
Notes
users
wp_posts
uid
ID
name
user_login
Format to lowercase, replace spaces with underscores
pass
user_pass
name
user_nicename
mail
user_email
created
user_registered
Formatted from UNIX time
name
display_name
user_status
Whitespace string
user_activation_key
Set to 0
Table mapping for WordPress user meta values
User information like capabilities and roles in the wp_usermeta table.
users
wp_usermeta
uid
user_id
meta_key
Set to string e.g. ‘wp_capabilities’
meta_value
Set to string e.g. ‘a:1:{s:6:”author”;s:1:”1″;}’
More information about the settings for appropriate meta_key and meta_value can be found in the WordPress Codex:
Drupal stores both node authors and comment authors in the users table. WordPress handles things differently. Page and post authors are stored in the wp_users table but comment authors are stored in wp_comments together with the comment data.
Table mapping for WordPress comments
Drupal 6.x
WordPress 3.x
Notes
comments
wp_posts
cid
comment_ID
nid
comment_post_ID
timestamp
comment_date
Converted from UNIX timestamp
comment
comment_content
pid
comment_parent
name
comment_author
mail
comment_author_email
homepage
comment_author_url
Truncated to WordPress limit of 200 chars
status
comment_approved
Comment authors
A note about the different ways Drupal and WordPress store comment author information: Drupal stores comment authors in its users table alongside site users like node authors. In WordPress, comment authors are stored in its wp_comments together with the comment data. WordPress comment authors are not entered into the wp_users table.
Table mapping for WordPress comments
Drupal 6.x
WordPress 3.x
Notes
comments
wp_posts
cid
comment_ID
nid
comment_post_ID
timestamp
comment_date
Converted from UNIX timestamp
comment
comment_content
pid
comment_parent
name
comment_author
mail
comment_author_email
homepage
comment_author_url
Truncated to WordPress limit of 200 chars
status
comment_approved
Comment authors
A note about the different ways Drupal and WordPress store comment author information: Drupal stores comment authors in its users table alongside site users like node authors. In WordPress, comment authors are stored in its wp_comments together with the comment data. WordPress comment authors are not entered into the wp_users table.
Table mapping for WordPress terms
This table mapping exports the Drupal terms into WordPress.
Drupal 6.x
WordPress 3.x
Notes
term_data
wp_terms
tid
term_id
name
name
name
slug
Make lower case and convert spaces to underscores
vid
term_group
Not used in a default WordPress installation
term_data
wp_term_taxonomy
tid
term_taxonomy_id
tid
term_id
taxonomy
String: ‘post_tag’ or ‘category’
description
description
parent
0 (No parent)
In the WordPress Taxonomy documentation, "term_group is a means of grouping together similar terms." During a standard migration, the WordPress term_group is set to the Drupal vocabulary ID, which seems to make sense. Nevertheless, a default WordPress installation does not actually use the value for anything. It may have been included by the developers for future expandability or use by plugins.
term_group=0 is the default value when creating a term using the Drupal user interface.
Below, we associate posts with the newly migrated terms.
Drupal 6.x
WordPress 3.x
term_node
wp_term_relationships
nid
object_id
tid
term_taxonomy_id
Table mapping for WordPress users
This maps Drupal user export to WordPress.
Drupal 6.x
WordPress 3.x
Notes
users
wp_posts
uid
ID
name
user_login
Format to lowercase, replace spaces with underscores
pass
user_pass
name
user_nicename
mail
user_email
created
user_registered
Formatted from UNIX time
name
display_name
user_status
Whitespace string
user_activation_key
Set to 0
Table mapping for WordPress user meta values
User information like capabilities and roles in the wp_usermeta table.
users
wp_usermeta
uid
user_id
meta_key
Set to string e.g. 'wp_capabilities'
meta_value
Set to string e.g. 'a:1:{s:6:"author";s:1:"1";}'
More information about the settings for appropriate meta_key and meta_value can be found in the WordPress Codex:
Drupal stores both node authors and comment authors in the users table. WordPress handles things differently. Page and post authors are stored in the wp_users table but comment authors are stored in wp_comments together with the comment data.
In this post I will give a step-by-step explanation of my Drupal to WordPress migration SQL queries. For general information about migrating from Drupal to WordPress, please see instead my Drupal to WordPress Migration Guide.
Since I offer site migration as a paid service, readers might be wondering why I’m giving away some of my secret sauce. The simple answer is that the ingredients of the sauce are anything but secret. A web search brings up blog posts and tutorials detailing how to go about it. In fact, the first version of my own Drupal to WordPress migration tool was based on a blog post by another web company.
However, while the knowledge is freely available, the whole process can be a real pain, especially when you’re dealing with a Drupal installation with lots of content and customisations. In my experience, migrating an established site from Drupal to WordPress requires all of the following:
An intermediate to advanced level of technical skill;
Time to plan, run the migration and do post-migration clean-up;
A great deal of patience.
If you are a developer with the right skills, you still need to consider the time investment and effort needed to understand both Drupal and WordPress database schemas. Often, the man-hours required will make a client think twice about proceeding with the project. Fortunately (or unfortunately) for me, I’ve needed to put in the time because my maintenance agreement for some clients covered exactly this sort of work. I’ve therefore built up enough experience to offer competitive quotes for migration of even complex sites. In my very biased opinion, you’ll make better use of your time and budget by getting me to do the task!
Of course, there are still cases where, for whatever reason, it’s not viable to offload the work to someone else. If this sounds like your situation, you’ll probably figure things out eventually so I might as well help you along.
Prerequisites for the Drupal to WordPress migration script
To run this migration you will need:
A working installation of Drupal 6;
A clean installation of WordPress 3.5 or above;
Access to the Drupal and WordPress MySQL databases;
The ability to run SQL queries on both databases;
Both databases on the same MySQL server;
Access to the Drupal and WordPress installations.
Ideally, you should:
Run the migration from a development server (it’s best not to risk running this on your live server);
Backup your live Drupal database before beginning the migration;
Be comfortable with running database operations;
Have planned the migration beforehand (e.g. which content types and taxonomies should be migrated).
If you have an older version of Drupal, simply upgrade to Drupal 6 first, then run the migration. I mention WordPress 3.5 as a prerequisite because that’s where I’ve done most testing. You can probably get away with migrating straight into a more recent version but to avoid any problems, I suggest you start off with WordPress 3.5. It’s easy to upgrade to the latest WordPress version after converting the Drupal content.
The Drupal to WordPress migration SQL queries
Keep in mind that this article is based on my Drupal to WordPress migration tool but with some additional queries written for the specific needs of a client. It therefore includes some values which will not apply to your site. I’ve stripped out any identifying information but left generic data to provide an example. You will need to manually look up the correct values in the Drupal database for your installation.
Another thing to keep in mind is that I’ve favoured readability over efficiency. For example, it’s possible to write more complex queries to avoid creating working tables but that would make debugging more difficult. Having a trail of data changes can help with content analysis if the end results aren’t what you expected.
CAUTION: Make a backup of both your Drupal and WordPress databases before running these queries. USE IS ENTIRELY AT YOUR OWN RISK. I’m offering this information with no warranty or support implied.
Example data in this scenario
wordpress: the WordPress database name.
drupal: the Drupal database name.
acc_ table prefixes: these are working tables I create to help with migrating data.
Clear out some Drupal and WordPress tables
For most migration projects to date, I’ve needed to run through several passes of the queries as I make incremental adjustments based on the client’s feedback. I define a ‘pass’ as one iteration of the entire migration process and inspecting the results in a WordPress installation. Depending on your project requirements, you may need to do this several times, making little tweaks to the MySQL queries as you go along.
These Drupal and WordPress tables may be empty or non-existent if you’re running the queries for the first time but it makes sense to clear them out at the start.
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;
TRUNCATE TABLE wordpress.wp_users;
DROP TABLE IF EXISTS drupal.acc_duplicates;
DROP TABLE IF EXISTS drupal.acc_news_terms;
DROP TABLE IF EXISTS drupal.acc_tags_terms;
DROP TABLE IF EXISTS drupal.acc_wp_tags;
DROP TABLE IF EXISTS drupal.acc_users_post_count;
DROP TABLE IF EXISTS drupal.acc_users_comment_count;
DROP TABLE IF EXISTS drupal.acc_users_with_content;
DROP TABLE IF EXISTS drupal.acc_users_post_count;
For some installations, I make changes to the wp_usermeta table so that needs to be cleared too.
TRUNCATE TABLE wordpress.wp_usermeta;
Vocabularies and taxonomies
Delete unwanted vocabularies. You’ll need to look in your Drupal vocabulary table for the appropriate vids. In this case, I’m deleting vocabularies 5, 7, 8, 38 and 40.
DELETE FROM drupal.vocabulary WHERE vid IN (5, 7, 8, 38, 40);
Delete terms associated with unwanted vocabularies. Here I’m keeping the terms for vid 38. Sometimes you might want to keep some terms of unwanted vocabularies for later conversion into into WordPress tags. (Please see the next query.)
DELETE FROM drupal.term_data WHERE vid IN (5, 7, 8, 40);
You may want to merge terms. In this example, I am merging the previously saved terms for News which has vid 38 to the Tags vocabulary terms which has vid 2.
We will need to deal with duplicates. For example, in the Drupal installation, ‘science’ could appear in both News (vid 38) and Tags (vid 2). This will cause a problem when exporting to WordPress since we can’t have duplicate terms. Here I create working tables for both term groups.
CREATE TABLE drupal.acc_news_terms AS SELECT tid, vid, name FROM drupal.term_data WHERE vid=38;
CREATE TABLE drupal.acc_tags_terms AS SELECT tid, vid, name FROM drupal.term_data WHERE vid=2;
Create a working table from duplicates.
CREATE TABLE drupal.acc_duplicates AS
SELECT t.tid tag_tid,
n.tid news_tid,
t.vid tag_vid,
n.vid news_vid,
t.name
FROM drupal.acc_tags_terms AS t
INNER JOIN (drupal.acc_news_terms AS n)
ON n.name=t.name;
Append a string to News terms duplicates so they won’t clash during migration. Here I used a fixed string but this won’t work if you have more than two terms with the same name. If you expect many terms with the same name, it would be better to generate a unique number. For example, using the tid would make it unique since these are unique primary keys. Use whatever string makes sense for your project.
Note that we’re overwriting the source data in the Drupal term_data table so proceed with care. Make sure you have a backup of your pre-migration Drupal tables in case you need to run the conversion again.
UPDATE drupal.term_data
SET name=CONCAT(name, ‘_01′)
WHERE tid IN (SELECT news_tid FROM drupal.acc_duplicates);
Convert Drupal News terms to Drupal Tags. We’ll migrate the whole lot into WordPress tags later.
UPDATE drupal.term_data SET vid=2 WHERE vid=38;
Create a table of WordPress tags. Exclude any terms from Drupal vocabularies that you might later migrate into WordPress categories. See the MySQL queries below where I create WordPress categories and sub-categories.
Here, all Drupal vocabularies except 37, 36 and 35 will be converted WordPress tags.
CREATE TABLE drupal.acc_wp_tags AS
SELECT
tid,
vid,
name
FROM drupal.term_data
WHERE vid NOT IN (37, 36, 35);
Now create the tags in the WordPress database. A clean WordPress database will have term_id=1 for ‘Uncategorized’. Use REPLACE as this may conflict with a Drupal tid.
We are assuming that this point the Drupal term_data table has been cleaned of any duplicate names. Any duplicate terms will be lost when running this MySQL query.
REPLACE INTO wordpress.wp_terms (term_id, name, slug, term_group)
SELECT
d.tid,
d.name,
REPLACE(LOWER(d.name), ‘ ‘, ‘_’),
d.vid
FROM drupal.term_data d WHERE d.tid IN (
SELECT t.tid FROM drupal.acc_wp_tags t
);
In WordPress, tags and categories are all stored in the wp_term_taxonomy table. The taxonomy field specifies whether it’s a tag or category by setting the field string to either ‘post_tag’ or ‘category’.
Here I convert these Drupal terms into WordPress tags.
REPLACE INTO wordpress.wp_term_taxonomy (
term_taxonomy_id,
term_id,
taxonomy,
description,
parent)
SELECT DISTINCT
d.tid,
d.tid ‘term_id’,
‘post_tag’, /* This string makes them WordPress tags */
d.description ‘description’,
0 /* In this case, I don’t give tags a parent */
FROM drupal.term_data d
WHERE d.tid IN (SELECT t.tid FROM drupal.acc_wp_tags t);
Create the categories and sub-categories in the WordPress database. This may be unnecessary depending on your setup.
Add terms associated with a Drupal vocabulary into WordPress. Note that in this case, these are the same vids that I excluded from the tag table above.
REPLACE INTO wordpress.wp_terms (term_id, name, slug, term_group)
SELECT DISTINCT
d.tid,
d.name,
REPLACE(LOWER(d.name), ‘ ‘, ‘_’),
d.vid
FROM drupal.term_data d
WHERE d.vid IN (37, 36, 35);
Convert these Drupal terms into WordPress sub-categories by setting the parent field in the wp_term_taxonomy table.
REPLACE INTO wordpress.wp_term_taxonomy (
term_taxonomy_id,
term_id,
taxonomy,
description,
parent)
SELECT DISTINCT
d.tid,
d.tid ‘term_id’,
‘category’,
d.description ‘description’,
d.vid
FROM drupal.term_data d
WHERE d.vid IN (37, 36, 35);
Now add the vocabularies to the WordPress terms table. There’s no need to set term_id as vocabularies are not directly associated with posts.
INSERT INTO wordpress.wp_terms (name, slug, term_group)
SELECT DISTINCT
v.name,
REPLACE(LOWER(v.name), ‘ ‘, ‘_’),
v.vid
FROM drupal.vocabulary v
WHERE vid IN (37, 36, 35);
Insert Drupal vocabularies as WordPress categories.
INSERT INTO wordpress.wp_term_taxonomy (
term_id,
taxonomy,
description,
parent,
count)
SELECT DISTINCT
v.vid,
‘category’, /* This string makes them WordPress categories */
v.description,
v.vid,
0
FROM drupal.vocabulary v
WHERE vid IN (37, 36, 35);
Update the WordPress term groups and parents.
Before continuing with this step, we need to manually inspect the table to get the term_id for the parents inserted above. In this case, vids 37, 36 and 35 were inserted as into the wp_term_taxonomy table as term_ids 7517, 7518 and 7519. I will use them as the parents for their respective terms. In other words, terms that formerly belonged to the Drupal vocabulary ID 37 would now belong to the WordPress parent category 7519.
UPDATE wordpress.wp_terms SET term_group=7519 WHERE term_group=37;
UPDATE wordpress.wp_terms SET term_group=7518 WHERE term_group=36;
UPDATE wordpress.wp_terms SET term_group=7517 WHERE term_group=35;
UPDATE wordpress.wp_term_taxonomy SET parent=7519 WHERE parent=37;
UPDATE wordpress.wp_term_taxonomy SET parent=7518 WHERE parent=36;
UPDATE wordpress.wp_term_taxonomy SET parent=7517 WHERE parent=35;
UPDATE wordpress.wp_term_taxonomy SET term_id=7519 WHERE term_taxonomy_id=7519;
UPDATE wordpress.wp_term_taxonomy SET term_id=7518 WHERE term_taxonomy_id=7518;
UPDATE wordpress.wp_term_taxonomy SET term_id=7517 WHERE term_taxonomy_id=7517;
Re-insert the Uncategorized term replaced earlier in the conversion process. We may have replaced or deleted the Uncategorized category during a previous MySQL query. Re-insert it if you want an Uncategorized category.
INSERT INTO wordpress.wp_terms (name, slug, term_group)
VALUES (‘Uncategorized’, ‘uncategorized’, 0);
INSERT INTO wordpress.wp_term_taxonomy (
term_taxonomy_id,
term_id,
taxonomy,
description,
parent,
count)
SELECT DISTINCT
t.term_id,
t.term_id,
‘category’,
t.name,
0,
0
FROM wordpress.wp_terms t
WHERE t.slug=’uncategorized’;
Converting Drupal nodes to WordPress posts
Now create WordPress posts from Drupal nodes. This may take a while if you have many Drupal nodes. Wait until the query completes before continuing. It could take several minutes.
REPLACE 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’,
DATE_ADD(FROM_UNIXTIME(0), interval n.created second) ‘post_date’,
r.body ‘post_content’,
n.title ‘post_title’,
r.teaser ‘post_excerpt’,
IF(a.dst IS NULL,n.nid, SUBSTRING_INDEX(a.dst, ‘/’, -1)) ‘post_name’,
DATE_ADD(FROM_UNIXTIME(0), interval n.changed second) ‘post_modified’,
n.type ‘post_type’,
IF(n.status = 1, ‘publish’, ‘private’) ‘post_status’,
‘ ‘,
‘ ‘,
‘ ‘
FROM drupal.node n
INNER JOIN drupal.node_revisions r USING(vid)
LEFT OUTER JOIN drupal.url_alias a
ON a.src = CONCAT(‘node/’, n.nid)
WHERE n.type IN (
/* List the content types you want to migrate */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);
Set the Drupal content types that should be migrated as WordPress ‘posts’. In this case, I want ‘page’, ‘story’, ‘blog’, ‘video’, ‘forum’ and ‘comment’ in Drupal to be converted to posts in WordPress.
UPDATE wordpress.wp_posts SET post_type = ‘post’
WHERE post_type IN (
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);
Now convert the remaining content types into WordPress pages.
UPDATE wordpress.wp_posts SET post_type = ‘page’ WHERE post_type NOT IN (‘post’);
Housekeeping queries for terms
Here I associate the content with WordPress terms using the wp_term_relationships table.
INSERT INTO wordpress.wp_term_relationships (
object_id,
term_taxonomy_id)
SELECT DISTINCT nid, tid FROM drupal.term_node;
We need to update tag counts.
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);
Now set the default WordPress category. You’ll need to manually look in the database for the term_id of the category you want to set as the default WordPress category.
UPDATE wordpress.wp_options SET option_value=’7520′ WHERE option_name=’default_category’;
UPDATE wordpress.wp_term_taxonomy SET taxonomy=’category’ WHERE term_id=7520;
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);
Migrate Drupal Authors into WordPress
In this case I am migrating only users who have created a post. This was a requirement for my project but may be unnecessary for you.
First delete all existing WordPress authors except for admin.
DELETE FROM wordpress.wp_users WHERE ID > 1;
DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;
Now set Drupal’s admin password to a known value. This avoids hassles with trying to reset the password on the new WordPress installation. Resetting a user password in WordPress is more convoluted and cannot be done using a simple MySQL query as in Drupal.
UPDATE drupal.users set pass=md5(‘password’) where uid = 1;
Create a working table of users and the number of posts they’ve authored. I am only considering authors who have created posts of the content types I want to migrate.
CREATE TABLE drupal.acc_users_post_count AS
SELECT
u.uid,
u.name,
u.mail,
count(n.uid) node_count
FROM drupal.node n
INNER JOIN drupal.users u on n.uid = u.uid
WHERE n.type IN (
/* List the post types I migrated earlier */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’)
GROUP BY u.uid
ORDER BY node_count;
Now add these authors into the WordPress wp_users table.
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,
REPLACE(LOWER(u.name), ‘ ‘, ‘_’),
u.pass,
u.name,
u.mail,
FROM_UNIXTIME(created),
”,
0,
u.name
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_post_count);
First set all these authors to WordPress “author” by default. In the next MySQL query, we can selectively promote individual authors to other WordPress roles.
INSERT IGNORE INTO wordpress.wp_usermeta (
user_id,
meta_key,
meta_value)
SELECT DISTINCT
u.uid,
‘wp_capabilities’,
‘a:1:{s:6:”author”;s:1:”1″;}’
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_post_count);
INSERT IGNORE INTO wordpress.wp_usermeta (
user_id,
meta_key,
meta_value)
SELECT DISTINCT
u.uid,
‘wp_user_level’,
’2′
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_post_count);
During the course of the migration, some posts may end up not having an assigned author. Here I reassign authorship for these posts to the WordPress admin user.
UPDATE wordpress.wp_posts
SET post_author = 1
WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users);
Comment authors
In Drupal, comments are treated as nodes and comment authors are stored along with other node authors in the Drupal users table. WordPress treats comments and comment authors differently. Comment authors in WordPress are not stored in the wp_users table. Instead, they’re stored along with the comment content itself in the wp_comments table.
We may need to run additional query to import users who have commented but haven’t created any of the selected content types. To do this, I create some working tables required for some later MySQL queries:
acc_users_with_comments: empty copy of wp_users
acc_users_add_commenters: empty copy of wp_users
acc_wp_users: copy of wp_users from wordpress database containing users
Running the following MySQL queries will throw errors if you haven’t created the required tables above.
First create a working table of Drupal users who have created a Drupal comment.
CREATE TABLE drupal.acc_users_comment_count AS
SELECT
u.uid,
u.name,
count(c.uid) comment_count
FROM drupal.comments c
INNER JOIN drupal.users u on c.uid = u.uid
GROUP BY u.uid;
Now add the author information for these users into another working table.
INSERT IGNORE INTO drupal.acc_users_with_comments (
ID,
user_login,
user_pass,
user_nicename,
user_email,
user_registered,
user_activation_key,
user_status,
display_name)
SELECT DISTINCT
u.uid,
REPLACE(LOWER(u.name), ‘ ‘, ‘_’),
u.pass,
u.name,
u.mail,
FROM_UNIXTIME(created),
”,
0,
u.name
FROM drupal.users u
WHERE u.uid IN (SELECT uid FROM drupal.acc_users_comment_count);
Using the above table, next build a working table of Drupal users who have commented but have not already been added to the WordPress wp_users table.
INSERT IGNORE INTO drupal.acc_users_add_commenters (
ID,
user_login,
user_pass,
user_nicename,
user_email,
user_registered,
user_activation_key,
user_status,
display_name)
SELECT DISTINCT
u.ID,
u.user_login,
u.user_pass,
u.user_nicename,
u.user_email,
u.user_registered,
”,
0,
u.display_name
FROM drupal.acc_users_with_comments u
WHERE u.ID NOT IN (SELECT ID FROM drupal.wp_users);
Combine the tables into another working table acc_wp_users.
INSERT IGNORE
INTO drupal.acc_wp_users
SELECT *
FROM drupal.acc_users_add_commenters;
The acc_wp_users working table helps when inspecting the user list. For example, you might want to clear out inactive users or spam posters. Once finished, remember to replace your WordPress wp_users with the cleaned acc_wp_users table. You may prefer to amend the above query to insert directly into the WordPress wp_users table.
I realise this is a rather round-about way of migrating comment authors from Drupal into WordPress but find that having working tables helps with debugging.
Housekeeping for WordPress options
Update file path for the WordPress installation.
UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ‘”/files/’, ‘”/wp-content/uploads/’);
Set your WordPress site name using the Drupal ‘site_name’ variable.
UPDATE wordpress.wp_options SET option_value = ( SELECT value FROM drupal.variable WHERE name=’site_name’) WHERE option_name = ‘blogname’;
Set your WordPress site description using the Drupal ‘site_slogan’ variable.
UPDATE wordpress.wp_options SET option_value = ( SELECT value FROM drupal.variable WHERE name=’site_slogan’) WHERE option_name = ‘blogdescription’;
Set the WordPress site email address.
UPDATE wordpress.wp_options SET option_value = ( SELECT value FROM drupal.variable WHERE name=’site_mail’) WHERE option_name = ‘admin_email’;
Set the WordPress permalink structure. Here we’re using /%postname%/ but you may set it according your own needs.
UPDATE wordpress.wp_options SET option_value = ‘/%postname%/’ WHERE option_name = ‘permalink_structure’;
Create URL redirects table
This table will not be used for the migration but may be useful if you need to manually create redirects from Drupal aliases. You will need the entries here for search engine optimisation (SEO) of your new WordPress site.
DROP TABLE IF EXISTS drupal.acc_redirects;
CREATE TABLE drupal.acc_redirects AS
SELECT
CONCAT(‘drupal/’,
IF(a.dst IS NULL,
CONCAT(‘node/’, n.nid),
a.dst
)
) ‘old_url’,
IF(a.dst IS NULL,n.nid, SUBSTRING_INDEX(a.dst, ‘/’, -1)) ‘new_url’,
’301′ redirect_code
FROM drupal.node n
INNER JOIN drupal.node_revisions r USING(vid)
LEFT OUTER JOIN drupal.url_alias a
ON a.src = CONCAT(‘node/’, n.nid)
WHERE n.type IN (
/* List the post types I migrated earlier */
‘page’,
‘story’,
‘blog’,
‘video’,
‘forum’,
‘comment’);
Finalising the conversion
Now that we’ve finished converting the content over from Drupal to WordPress, we have the rather (not very) fun job of checking the content, setting up any WordPress plugins and widgets, then finally going live. Depending on the complexity of your Drupal installation, this process can be extremely time-consuming and perhaps form a separate project in its own right.
You can use my Drupal to WordPress migration notes to help with going live. Of course, the search for equivalent WordPress plugins and conversion from the old Drupal modules will have to be done according to your specific set-up.
Accepting limitations
Before finishing this article, one point I’d like to convey is the importance of accepting the limitations of any migration process. You, or your client, may be insistent on turning your new WordPress site into an exact copy of your former Drupal installation. While this may be technically possible, there comes a point of diminishing returns where the work you’d need to put in just isn’t worth the value of the data.
It may also be more productive in some instances to make manual adjustments via the WordPress control panel than to try anything clever using the backend database. Sometimes the time needed to write, test and debug MySQL queries far exceeds the boring but more reliable editing using the web-front end.
Expect to kill your SEO if you’re not careful with the migration. A site that relies heavily on revenue from search engine rankings will need extra steps to preserve SEO. This is a huge topic so I will not cover it here but you should carefully plan the conversion steps before starting with the migration. Pay particular attention to preserving Drupal path aliases, taxonomy listing pages and internal links.
Drupal to WordPress migration activity diagram
This UML activity diagram illustrates the steps during the migration.
Migration steps listing
Prepare tables: This is where we reset the development database tables to a known state, ready for another migration pass.
Delete unwanted vocabularies
Delete unwanted terms
Merge terms? Yes: go to step a; No: go to step 5
Create tables for each vocabulary to merge
Create duplicate table for each vocabulary
Make duplicate terms unique
Merge terms
Create tags
Create categories and sub-categories
Set uncategorized term
Create posts from nodes
Set posts and page types
Associate posts with terms
Update tag counts
Set default category
Migrate comments
Migrate authors
Site-specific settings and customisation: this would include WordPress site information settings and URL redirects
Good luck!
So that’s it. A Drupal to WordPress migration can be a great deal of effort. I’ve had one project that initially looked like only couple of hours work balloon to over 50 billable hours in total. Previously installed modules caused problems requiring custom MySQL queries and PHP scripting to resolve. On the other hand, I’ve had a number of sites that completed in 15 minutes after running my Drupal to WordPress migration tool.
Overall, the majority of my clients prefer WordPress over their previous Drupal site. I personally find WordPress quicker to update and manage. Any short-term hassles with migrating have been outweighed by the long-term advantages of easier maintenance.
I’d love to receive corrections, bug fixes and suggestions for improvements. Please contact me or submit an issue on GitHub.
CAUTION: Make a backup of both your Drupal and WordPress databases before running these queries. USE IS ENTIRELY AT YOUR OWN RISK. I’m offering this information with no warranty or support implied.
A primary email address tied to your email provider could set you up for a great deal of inconvenience if they shut down. Registering your own domain helps control your email regardless of which company you're currently using.
On Thursday, 8th August 2013, a secure email service provider called Lavabit suddenly suspended operations. Its founder, Ladar Levison, wrote in an open letter on the company's website that he would rather shut the company down than "become complicit in crimes against the American people." Although Mr Levison took what he believed to be a principled stand, Lavabit customers were understandably angry at being blocked from accessing their emails. Without warning, long-time customers lost years worth of archived messages. Active users who relied on the company to host their primary email now face the inconvenience of updating their contacts and online accounts with a new address.
One may be tempted to think that a simple solution would be just to set up another email account elsewhere. After all, there are many free email providers offering reliable services. If you're in this camp, ask yourself how your day-to-day life will be affected if you suddenly and unexpectedly lose access to your email account.
Do you conduct business over email? How much productivity will be lost re-establishing communication with clients?
Have you saved passwords, document attachments and important account information in your webmail folders? What happens if you can't log in to the webmail account?
How much time will it take to inform all your relatives, friends and contacts of your new email address, especially if your address book was also hosted with the lost email service?
How easy is it to reset the passwords of your other online accounts (internet banking, Facebook, Skype, etc.) without that lost email address?
Keeping control of your email address
There are some important lessons we can learn from the Lavabit incident and two things can save you from similar trouble:
Register your own domain and link it to your email provider. That way, you can switch providers while retaining the same email address.
Do not rely on webmail as your only method of accessing your messages. Set-up an email client on your computer and regularly download copies of your email.
Exactly how you go about using your own domain and downloading emails depends on your existing set-up and requirements. I'll give a quick overview in this post but please note that it only briefly touches on some steps which can be quite technical.
Step 1: Register your own domain
An email address under your own domain keeps it independent of the email host. Your current email provider may go out of business, get bought-out or become unreliable but having your own domain means that you can switch to another while retaining the same email address.
To get an email address under your own domain, you first need to register a name with a domain name registrar. (See this post for more information.)
You can register your domain with the following companies but a web search for "domain registration" will bring up a list of other providers:
Another Cup of Coffee Limited - we'll handle the details of domain registration under your name for £9.99 GBP per year
123-reg.co.uk - a popular UK-based registrar and hosting company
namecheap - a US-based registrar that seems to have a good reputation for customer service (I personally haven't used them)
Network Solutions - one of the oldest and well-known registrars but quite expensive
Regardless of which domain registrar you choose, the whole process should only take a few minutes to complete. However, depending on their system, it could take a few hours to a day or more before it's available for use.
Step 2: Link your domain to your email provider
Linking your domain to an email provider can be intimidating for non-technical people. To make matters more complicated, some end up with different combinations of registrar, free web-based email, business email hosting, and web hosting. Everything can be under one roof or you may have different companies handling each component. The exact steps needed will depend on your subscription packages so covering them in a short tutorial is not practical. (That's why companies like us exist!)
In general, your registrar will give you an online control panel. This lets you specify settings to hand over control of the domain's email to an external email provider. Alternatively, it may offer an email forwarding service that automatically redirects messages to another address, such as Gmail or Yahoo Mail.
Changing email providers then becomes a matter of adjusting the control panel to reflect the new company's settings.
Here are some help pages for a few of the popular email providers:
For many people, their main method of checking and sending email is through their provider's webmail interface. It's very convenient because there are no programs to set up on your computer. All that's needed is to open up a web browser and log in. The downside is that you do not retain any copies of your messages. As some of the Lavabit customers found, you will lose everything if the provider suddenly ceases operations.
The solution is to set up an email program (also known as an email client), like Mac Mail, Microsoft Outlook or Mozilla Thunderbird to download emails from your server. Even if you prefer webmail, periodically connecting from your email client ensures that you save the latest messages on your computer's hard-drive.
Most email providers offer you a choice of 'POP' or 'IMAP' as mechanisms for retrieving your email. POP will simply download all the messages and if set in your email client, delete the messages after they're read. IMAP synchronizes your email client with the server so it copies the same structure of read, unread, sent messages and saved folders. (This Rackspace article gives more detail on the difference between the two.) I find IMAP to be the most convenient option. If you mostly use webmail, you should also use IMAP if it's available.
Too much trouble?
These steps might seem daunting but you don't need to be a computer expert to get everything set-up. Business users usually have more complex configurations that may need an IT administrator to get everything working properly. However, for personal users and micro-businesses with simple needs, a little bit or research and background reading should allow you to get the job done without any help.
Of course, if you'd rather not go to the trouble of doing this yourself, we'll be very happy provide you with a quotation. This is not a big budget job as the whole process is fairly quick for those familiar with what's required.
Some background on the Lavabit incident
I'll make a slight digression from technical matters as the Lavabit incident may have wider implications for anyone using US-based internet services.
Lavabit offered encrypted email services and was reported in the press to have been used by the NSA whistleblower Edward Snowden. Unlike most email systems, the company's technology meant that there was no way for them to directly read user emails. While we may never know the truth, it seems likely they were ordered to participate in ongoing surveillance in a form that the founder believed to be against the United States Constitution. Levison was issued with a 'gag order' preventing him from giving details on the matter. Shortly after the Lavabit news broke, Silent Circle, another secure email provider, pre-emptively shut down its own service in order to protect its customers.
There is increasing industry speculation that the US government's surveillance is jeopardizing the country's businesses since they can no longer be trusted to protect their users' privacy.
It's clear that no matter which country you're in, if your email is hosted with a US provider, you need to assume that the US government will want (or already has) backdoor access to them. Whether or not this is acceptable is a discussion outside the scope of this post. Regardless of where you stand, it's important to realize that the industry landscape is changing and we can no longer be complacent about safeguarding our data.
I’m releasing my in-house tool for Drupal to WordPress migration. You can go directly to the download page for the latest version of the utility.
If you’d rather let someone else do the work, you can find more information at my dedicated migration service page.
When I started Another Cup of Coffee, we specialized in setting up Drupal since it was—and still is—a very versatile content management system suitable for anything from simple ‘brochureware’ sites to online shops and social networking. One of my working assumptions was that website owners would appreciate a platform that offers more flexibility as their needs expanded. However, over the past few years, I’ve received quite a few requests from new and existing clients to migrate sites from Drupal to WordPress. At first it seemed a little odd that it was almost always in this direction and rarely the other way around.
Some investigation quickly brought out an obvious answer: there is a Drupal module called wordpress_migrate that supports migrating WordPress blog exports into Drupal, but no simple mechanism to convert Drupal nodes into WordPress posts and pages.
After many years of experience with running Drupal sites, I can understand why a certain type of website owner would prefer WordPress. It’s just much more suited to the non-technical user who just wants a simple site with fairly standard functionality. (Incidentally, this describes our target market.) Since I’d first started using WordPress in 2004, it has evolved from simple blogging software into a fully-fledged content management system. Further, it’s more user-friendly out-of-the-box than Drupal’s default utilitarian interace and has a simpler update system. From my own experience, Drupal maintenance can be quite demanding with its frequent core and module updates. WordPress, on the other hand, is refreshingly easy to maintain.
Releasing our in-house utility
Unfortunately for us, manually moving a client’s site from Drupal to Wordpress was quite tricky and time consuming, especially when lots of content types and entries are involved. We obviously needed some sort of automated tool but to my surprise, all that I could find online were either articles about people experimenting with SQL scripts, or paid-for services like GoWordPress and cms2cms. Our margins are pretty tight and paying for the service just wasn’t an option so I decided to hack together some scripts to build our own in-house tool. A blog post by Scott Anderson at Underdog of Perfection provided the best steps to get me started.
After using our tool to migrate a fair number of sites, I realised that it was time to give back to the community. After all, my code built upon the efforts of others who freely shared their work. I’m therefore releasing our Drupal to WordPress Migration Tool for public use under the MIT License.
Where to download the Drupal to WordPress Migration Tool
I’ve set up a dedicated page for downloading our Drupal to WordPress Migration Tool which will have the most up-to-date version. More feature-rich versions can be found there as feedback comes in and bugs are reported. The first version should definitely be considered a ‘beta’ and only used by people who know what they’re doing when it comes to setting up and configuring content management systems. Currently it supports only Drupal 6 and WordPress 3.5. Eventually I aim for it to be a ‘wizard’ style utility that can be used by less technical people, like web creatives who’ve been tasked with migrating their clients’ sites from various releases of Drupal into the latest version of WordPress.
If you’re unsure of how to proceed or would simply like someone else to do the work, please drop us a note and we’ll be happy to provide a quotation.
Go to the download page. IMPORTANT: I’m offering this tool with no warranty or support implied.
Most computer users know about the importance of backups as it's all too common to have years worth of information and memories wiped out by a computer crash. Generally though, the average backup strategy involves copying files into some sort of device in your home or office. This may be of little use if the building is destroyed. Furthermore, fragile electronics may not survive an evacuation should you need to leave your home in an emergency.
The key to creating emergency-resilient electronic backups is to have both local and remote copies to create several layers of redundancy.
Local backups (or on-site backups) are kept in your primary location, such as at home or the office. These are for convenience since it's usually easier to make more frequent and larger backups. Should you need to restore, they are easily accessible.
Remote backups (or off-site backups) stored at another location are for redundancy in case your local ones are destroyed. These are likely to be the ones you'll fall back on after a large-scale emergency.
Local backups
Your local backup storage options include CDs, DVDs, external hard-drives or memory sticks. Since they can be connected directly to your computer, you can quickly backup and restore large files. Unfortunately, they're also the ones most likely to be lost in the event of a house fire, earthquake, flood or any number of emergencies.
To help protect them, these should ideally be stored in a strong, waterproof and fire resistant container. There are commercially available data safes that are rated to protect your backup media from fire, water and theft but these are expensive. A cheaper but still pricey alternative would be to use professional waterproof hard cases from Pelican or Wonderful. Those on a very tight budget can simply try putting the backups in a sealed plastic food container such as those from Lock & Lock or Tupperware.
However, the biggest drawback with these local backups is that you are faced with a dilemma:
You can keep your backup media stored in the protective case and schedule regular backups. They'll have some protection in the event of an emergency but it's easy to postpone or forget about making the backups. You also won't have a copy of any files that have been edited between the backups; or
You can make frequent backups to a constantly attached drive, usually automatically through backup software. This makes your backup process more reliable but leaves the media at risk since they're left out.
A make-shift office of laptops and backup drives laid out on the dining room table
Remote backups
The most convenient way to keep remote backups is to use an online service like the following:
By far the easiest to use are Dropbox and Carbonite but the most secure are rsync, SpiderOak and JungleDisk. (There are many more services available but these are the ones that I've personally tried.)
* Dropbox has a free option. If you want to try one of these, use my referrer code and we'll both get free extra space.
These types of services host their servers in business-grade data centers; your data will be housed in a secure facility with its own disaster recovery systems. In other words, they do the job of keeping your data safe for you. (Of course, you should never give full trust to a third-party so local backups are still important.)
Keep in mind that due to storage fees or internet speed limitations, some types of data, such as a large music or video library, may not be practical to store online. You should also expect your first backup to take a while since you'll need to copy everything onto the remote server. For example, the first time I used SpiderOak, it took several days to backup almost 30GB of data. Fortunately, subsequent backups are quicker as most services copy over only the changes.
A solution to keeping remote backups of large files is to create a 'backup-ring' with friends and relatives from out-of-town. The principle behind this is quite simple: make backups and then swap disks when you visit your out-of-town friends; you keep their drive and they keep yours.
Synchronization
File synchronization is a technology that's matured over the past few years and combines benefits of local and remote storage. It works by copying files from your computer onto online storage and when any changes are made, the older file is automatically updated with the new version.
Most services also allow you to synchronize several computers so if you have a laptop, desktop and office computer, they can all be updated with the latest changes. This feature alone can greatly simplify your backup and restore process; if one computer is unusable, you simply log in to another, synchronize then pick up where you left off. The same process applies when buying a new computer. Just install the synchronization software and your files will be copied over from the servers.
Some even offer web and mobile device access so in an emergency, you don't even need your own computer to get hold of your files.
Dropbox, JungleDisk and SpiderOak offer computer synchronization. Note that these services require an internet connection to synchronize. During a wide scale emergency, internet links may be down or unreliable so it's a good idea to ensure that your computers regularly go online to get updates.
Other tips
If your email provider supports it, try using the IMAP protocol for your emails. This essentially keeps your mail server synchronized with your computer's mailbox changes. Even if your computer is destroyed, you can still connect to the server and have your emails in the same state of your last access: new, read, saved and deleted emails will appear as they did on the destroyed computer. Those who use web-based email, like Gmail, Hotmail and Yahoo won't need to worry about this.
Remember that your backups may contain confidential files. For privacy, make sure that all backups are encrypted. Your backup software may have this feature built-in. If not, you might want to try TrueCrypt which is well-known and trusted encryption software.
Some files use special formats so make sure you also backup any software you need to access your files. As much as possible, try to save or export files into a widely used format. For example, PDF files can usually be opened in pretty much any operating system and most devices come with some sort of PDF reader.
Don't forget to run a test restore. You may be diligently making backups but they're useless if some error in your process means that you can't retrieve the files when needed.
It's all about continuity
While keeping your data safe might seem like a low priority in comparison to other preparedness tasks, the purpose behind creating emergency-resilient electronic file backups is continuity. Crises always pass and life eventually returns to normal. Since a large part of our assets, both business and personal, are now electronic, rescuing your data files will speed up your recovery.
More than anything, having data backups provide emotional security. Knowing that your digital archive of family photos or vital business documents are safe can help you focus on the immediate needs of getting through an emergency.
This blog will be about helping small companies use the internet more effectively.
As a small-business owner or entrepreneur, you know that reaching a wide audience is vital. You've heard that the internet can play a big part in a successful business but perhaps don't know where to start. You have enough on your plate just keeping things running smoothly. There's no time to become an expert and there's no budget to hire an expensive consultant. Besides, who can you trust? There are charlatans out there who'd happily run away with your hard-earned savings.
Sound familiar? If so, this blog is for you. It will help you apply technology to your business, spot new ideas that might otherwise go unnoticed, and find ways to make your life easier. As a result, you'll be able to use the internet to improve your business.
OK, this sounds good. But at this point, you're probably expecting the sales pitch for my book or seminar. Of course, there is always an ulterior motive and it would be foolish of me to think you're not intelligent or savvy enough to realise this. So here's what I'm trying to achieve with this blog:
I want to convert readers to customers by proving that we're useful to their business.
I want existing customers to get to know us a little more.
It's all about building trust. The more people know you, the more likely they are to spend money on you. And this leads me nicely to my first internet tip for small businesses.
Blogs are a proven way for companies to reach a wider marketplace. But some companies take the wrong approach, using it as a glorified press-release section, publishing nothing but "why we're so great" articles. In reality, people aren't that interested in a constant stream of spin and new project wins.
A personal blog is, by definition, about you and it's OK to be self-absorbed; your family and friends are a natural audience who want to know what you're up to. Company blogs are different. Readers need a reason to keep coming back so articles also have to be relevant to their lives. Two ways to do this are:
Including helpful information.
Generating discussion that engages them.
I will aim to do both.
Future articles
Future articles will explore this topic in more detail. Other themes will include explanations about technology without the geek-speak; tips to make your life online easier; and news in my industry that may affect you. Once in a while, I'll indulge myself by telling you about developments at Another Cup of Coffee, how we work, and the method behind our madness.
Since blogs are also about creating dialogue, please feel free to post your opinions and questions. No doubt, other readers will find your input valuable. If you'd like me to cover a specific topic, drop me a note and I'll try to fit it in.
About the author
If I'm to make claims about helping you, you'll probably need to know a little more about me. As a quick introduction, I'm Anthony Lopez-Vito, founder of Another Cup of Coffee. Rather than use up space writing about myself here, I'll direct you to my LinkedIn profile. Other members of the team may also contribute at some point. In the near future, I aim to present guest authors who are clients, partner companies, or suppliers.