Move Data From MySQL to MongoDB via phpMyAdmin and Mongoimport

I decided to migrate from a classic LAMP stack to a MEAN stack and I need to migrate my blog from MySQL to MongoDB. MongoDB has a tool called mongoimport which accepts a few types of data. Now all I have to do is export my data to the right format. I recalled phpMyAdmin having an export to JSON option and since MongoDB deals with JSON documents I thought this might be a good place to start. A simple SELECT statement is all I really needed to get the data ready for MongoDB.

Rememebr MongoDB is not relational so this will not help you with relationships. However, a few creative joins would probably allow you to pull that relational data in as part of the document.

SELECT
    title,
    slug,
    keywords,
    description,
    body,
    status,
    created as published,
    created as created
FROM
    posts
ORDER BY
    created DESC

Run the query then export the result set.

mongo_export

Be sure to set the export format to JSON.

mongo_import

Your data will look something like the following.

\*
 Export to JSON plugin for PHPMyAdmin
 @version 0.1
*/

// Database 'jasonsnider'

// jasonsnider.posts
[{
    "title": "Fixing Chrome Apt Errors on Debian\\/Ubuntu...",
    "slug": "fixing-chrome-apt-errors-on-debian-ubuntu...",
    "keywords": "Google Chrome, Chrome, Linux, Ubuntu, Ubuntu...",
    "description": "Fixing apt-get update errors involving Chrome in...",
    "body": "

While running a typical update on Ubuntu 14.04 ...",
    "status": "published",
    "published": "2016-03-09 08:23:26",
    "created": "2016-03-09 08:23:26"
}, {
    "title": "phpMyAdmin Showing Dropped Tables",
    "slug": "phpmyadmin-show-dropped-tables",
    "keywords": "MySQL, phpMyAdmin",
    "description": "I had deleted several tables...",
    "body": "

I had deleted several tables from.... ",
    "status": "published",
    "published": "2015-12-09 07:42:50",
    "created": "2015-12-09 07:42:50"
}]

mongoimport will not play nice with the comments generated by phpMyAdmin. You will want to open the file and remove the comments.

remove comments

Then user mongoimport to import your newly crafted JSON data. Be warned the command as written is a destrcutive operation. See the mongoimport documentation for more options.

mongoimport --db jasonsnider --collection posts --drop --file ~/Downloads/posts.json --jsonArray
  1. Write a query to shape the data export.
  2. Export as JSON.
  3. Remove comments from the JSON file.
  4. Use mongoimport to import the JSON file..