MongoDB is a NoSQL database that stores it’s data in BSON format, as such, it’s not always obvious on how to migrate this data into relational databases like MySQL.

The main issue is that MongoDB doesn’t have a fixed schema, and the data stored can have any shape, whereas MySQL does have a schema, and you cannot just store anything.

This makes migration a bit complicated, because you have to somehow transform the MongoDB data in a format that makes sense for relational databases.

Step 1. Export JSON data from MongoDB

The first thing you want to do is take the data out of MongoDB, luckily there’s a handy CLI tool called mongoexport that can do this for us.

Let’s export our data as JSON into a file called data.json:

$ mongoexport \
   --db ${YOUR_DATABASE} \
   --collection ${YOUR_COLLECTION} \
   > data.json

If your database is not on localhost you might want to specify a connection string and a username and password with the following options:

$ mongoexport \
   --db ${YOUR_DATABASE} \
   --collection ${YOUR_COLLECTION} \
   --username ${YOUR_USER} \
   --password ${YOUR_PASSWORD} \
   "mongodb://${YOUR_HOST}:27017" \
   > data.json

The resulting JSON export will look something like this:

{"_id":{"$oid":"520e6431b7fa4ea22d6b1872"},"first_name":"Kelly","last_name":"Freddi","email":"kfreddi0@wikipedia.org","gender":"Male","ip_address":"79.111.233.14"}
{"_id":{"$oid":"5cd0de910dbce4346295ae28"},"first_name":"Orazio","last_name":"Playhill","email":"oplayhill1@bbb.org","gender":"Male","ip_address":"94.111.107.131"}
{"_id":{"$oid":"5ad0de920dbce4346295ae29"},"first_name":"Sabine","last_name":"McVey","email":"smcvey2@bbc.co.uk","gender":"Female","ip_address":"101.237.80.136"}
...

Step 2. Convert JSON to SQL

Once you have your exported JSON data as a file, it’s time to convert it to SQL. Use our free JSON to MySQL converter to convert your data to a SQL file.

The converter will take care of flattening any nested objects you might have in your data and create a valid schema automatically.

Once you’ve converted you should have an .sql file, it will looks something like this:

CREATE TABLE `mytable` (
`id` INT,
`first_name` VARCHAR(1024),
`last_name` VARCHAR(1024),
`email` VARCHAR(1024),
`gender` VARCHAR(1024),
`ip_address` VARCHAR(1024)
);

INSERT INTO mytable VALUES
(1,'Kelly','Freddi','kfreddi0@wikipedia.org','Male','79.111.233.14'),
(2,'Orazio','Playhill','oplayhill1@bbb.org','Male','94.111.107.131'),
(3,'Sabine','McVey','smcvey2@bbc.co.uk','Female','101.237.80.136')

...

As you can see the SQL file will create a table with a matching schema and insert all the data for you.

Step 3. Importing into MySQL

Let’s import it into your MySQL:

$ mysql -u username -p database < data.sql

This should now create the table and insert the data for you.

> SELECT * FROM mytable;
id	first_name	last_name	email	gender	ip_address
1	Kelly	Freddi	kfreddi0@wikipedia.org	Male	79.111.233.14
2	Orazio	Playhill	oplayhill1@bbb.org	Male	94.111.107.131
3	Sabine	McVey	smcvey2@bbc.co.uk	Female	101.237.80.136
...

Happy migrating!