Friday, August 12, 2016

Moving data from MongoDB to MySQL's JSON Document Store

I had an interesting phone call from someone wanting to move from MongoDB to MySQL's new JSON Document Store. The big question was 'How do I export my Mongo data into something I can read into MySQL?"

The good news is that the folks at Mongo have given us the tools for the hard part of a simple process. For this example I am going to use some test data create previously in a database creatively named dave. The numbers are made up and I am using my name and the names of the canines residing in my home. So a quick peek at the data:


$ mongo
MongoDB shell version: 3.2.8
connecting to: test
> use dave
switched to db dave
> db.dave.find()
{ "_id" : 123, "name" : "Dave" }
{ "_id" : 456, "name" : "Jack" }
{ "_id" : 789, "name" : "Dexter" }
{ "_id" : 787, "name" : "Boo" }
> 
bye

Dumping That Data

First use mongodump -d dave to write out the data much as you would with mysqldump. Under you current working directory of your shell (assuming you are on Unix/Linux) there will be created a directory named dump. And under dump is a directory named after the example database dave.

A dave.bson file was created with the data.

BSON to Text

Executing bsondump dave.bson > output.file will convert the MongoDB BSON formatted data into something much easier to work with.

$ cat output.file 
{"_id":123.0,"name":"Dave"}
{"_id":456.0,"name":"Jack"}
{"_id":789.0,"name":"Dexter"}
{"_id":787.0,"name":"Boo"}

The output.file can be processed in a number of ways to pull it into MySQL such as using your favorite text editor to wrap insert statements around the data or using a simple program that reads a line from the text file and then send data to the database.

1 comment:

  1. These all are notable facts… I stick with responsive website design for my business. With the mobile marketing is at its peak stage, having website that goes comfy on all devices will help your business. You can check my website for more details on the importance of responsive website design. PHP Training in Chennai | Best PHP training in Chennai | PHP Training Institute in Chennai

    ReplyDelete