Easily Migrate Blogger / Blogspot comments to Disqus

It used to be a really simple process; click a button on Disqus admin, log into blogger, and then comments are migrated. But Disqus was left with a broken import link when Google deprecated some of their APIs. Given that it's been broken for a while, I didn't see that being fixed anytime soon.

So, I finally got some time to roll my own fix based off a bit of code I previously wrote. Now that it's been tested for a few weeks, I've decided to make it public.

This is a multi-step process but doesn't take more than a few minutes each to set up.

Exporting your existing comments

• Go to your Blogger admin and then find the settings item on the left menu.
• Under that, look for "Other settings"
• Click "Export blog" at the top.
• Save the backup file somewhere.

I don't blame you for having trouble finding it. Below is a screenshot of where to click.

Converting your comments to WXR

What you have now are comments in some God-awful XML format which you'll need to convert to Wordpress WXR format, one that Disqus can import.

You can either use my online "Blogger to Disqus comment converter" tool which uses django-disqus, or (if you don't trust me) check out the source and run it yourself.

Setting up Disqus

This part is fairly straight forward.

• Create a Disqus forum for your site.
• Go to the admin and then the Discussions tab
• Click Import
• Select the Generic tab
• Upload the converted WXR file
• While you wait, continue with the setup below.

Setting up Disqus on Blogger

Now that the import process has started, there's just one more thing left to do on your blog. While you wait for the import to complete, set up the Disqus plugin on your blogger so the old comments don't show anymore.

There is a page on Disqus that lets you quickly add the plugin to the site of your choice.

Click on this link and then:

• Click the "Add <disqus forum name> to my Blogger site" button (Step 1. You can ignore step 2 because we already did that)
• Select a blog you wish to use Disqus
• Click "Add widget"

Supporting Internet Explorer users

For those who wish to support Internet Explorer users, there is an extra step involved.

Check the source of your blog and search for "X-UA-Compatible" and check that it's using "EDGE". If it's not there or it's not showing "EDGE", Disqus has a decent tutorial written up here on how to add it into your Blogger template.

And that's the end of that! The Disqus widget on Blogger will handle the rest.

You're finally done!

Sources

• Disqus | Install instructions for Blogger
• How do I import my old comments? | DISQUS
• Updating a Blogger template to support all versions of Internet Explorer | DISQUS
• Manually adding a Disqus gadget to Blogger | DISQUS

MariaDB / MySQL: How to vacuum your deleted database (shrink size of ibdata1 file)

After checking out the data from a well known leak, I realised that MySQL poorly handles the deletion of databases and that the default configuration doesn't make it very easy to vacuum the obsolete data.

(5.6 changes the default so it's better, but still an issue)

Have no fear, twig (and the internet's collective knowledge) to the rescue!

• Get a list of all databases on your server

SHOW DATABASES;

• Make a backup of all the data. You're going to need it. You can skip internal databases information_schema, mysql, and performance_schema.

mysqldump -u root -p database_name > database_name.sql

• If you're lazy you can just use this instead:

mysqldump -u root -p --all-databases > everything.sql

• Now drop every single table EXCEPT for the internal databases; databases information_schema, mysql, and performance_schema.

DROP DATABASE database_name;

• Now terminate the server process.
• Open up my.cfg or my.ini (Windows)
• Under "mysqld", add in "innodb_file_per_table". This changes the setting so the information for each database goes into a separate file.
• From your MySQL data directory (MySQL\5.5\data or /var/lib/mysql/), delete the ibdata1 and ib_logfile* files.
• Restart the MySQL service.
• Log back into the SQL shell and create each database again.

CREATE DATABASE database_name;

• And finally, reimport the data.

mysql -u root -p database_name < database_name.sql

Once again, thank you technology for keeping me up way past my bedtime. Hopefully this has helped you avoid the situation I'm in right now... Looking for suitable gif animations can be hard, but I've found a photo that's surmised how I feel about the way MySQL has been built.

Sources

• database - How to shrink/purge ibdata1 file in MySQL - Stack Overflow
• innodb - What is the best way to reduce the size of ibdata in mysql? - Database Administrators Stack Exchange
• database - How to shrink/purge ibdata1 file in MySQL - Stack Overflow
• MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
• mysqldump - Export and Import all MySQL databases at one time - Stack Overflow