In my band Cool Cavemen , before using a private mailing-list to discuss internal stuff, we were using a private bbPress forum. This forum is no longer used but it still contains tons of old, but useful content.

I’ve just finished to migrate this forum from bbPress to a mailbox archive. Here is how I did it.

First, I simply opened a MySQL terminal on a local copy of our bbPress site:

1$ mysql -u root
2mysql> USE bbpress;

We have several forums there, we’re only interested in the private one. Let’s get its ID:

mysql> SELECT ID, post_title FROM wp_posts WHERE post_type = "forum";
+-------+----------------+
| ID    | post_title     |
+-------+----------------+
| 13884 | Private forum  |
|  2891 | Public forum   |
+-------+----------------+

For safety, check the number of topics and replies in our forum, to make sure we’re on the right track:

mysql> SELECT COUNT(ID) FROM wp_posts WHERE post_type = "topic" AND post_parent = 13884;
+-----------+
| COUNT(ID) |
+-----------+
|       776 |
+-----------+
mysql> SELECT COUNT(ID) FROM wp_posts WHERE post_type = "reply" AND post_parent IN (SELECT ID FROM wp_posts WHERE post_type = "topic" AND post_parent = 13884);
+-----------+
| COUNT(ID) |
+-----------+
|     18104 |
+-----------+

We can now export the content of topics to a CSV file ( /tmp/forum-topic-export.csv ) directly from a MySQL query:

SELECT p.ID AS msg_id, p.ID AS topic_id, u.display_name AS realname, u.user_email AS from, p.post_date_gmt AS date, p.post_title AS subject, p.post_content AS body
    INTO OUTFILE '/tmp/forum-topic-export.csv'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '\\'
    LINES TERMINATED BY '\r\n'
FROM wp_posts AS p
LEFT JOIN wp_users AS u ON p.post_author = u.ID
WHERE p.post_type = "topic" AND p.post_parent = 13884;

Next, it is for replies to be exported (to /tmp/forum-reply-export.csv ):

SELECT p.ID AS msg_id, p.post_parent AS topic_id, u.display_name AS realname, u.user_email AS from, p.post_date_gmt AS date, p.post_title AS subject, p.post_content AS body
    INTO OUTFILE '/tmp/forum-reply-export.csv'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '\\'
    LINES TERMINATED BY '\r\n'
FROM wp_posts AS p
LEFT JOIN wp_users AS u ON p.post_author = u.ID
WHERE p.post_type = "reply" AND p.post_parent IN (
    SELECT ID
    FROM wp_posts
    WHERE post_type = "topic" AND post_parent = 13884
);

I then wrote a tiny Python script to parse those CSV files and transform each forum post to an email. All these email are then consolidated in a single mailbox file.

Once this is done, it’s easy to transfer these mails to any mail account using, for example, the ImportExportTools plugin for Thunderbird  .

Finally, once your confident enough (read: have lots of MySQL backups) that all your forum’s threads were safely converted to mails, you’re free to purge your bbPress databases of the content you just migrated:

DELETE
FROM wp_posts
WHERE post_parent IN (
    SELECT ID
    FROM wp_posts
    WHERE post_parent = 13884
);
DELETE
FROM wp_posts
WHERE post_parent = 13884;