MySQL to MongoDB Migration

29 / Jan / 2015 by Deepak Kumar Mittal 4 comments

MySQL and MongoDB are a very popular open-source databases these days. Through this blog, I wanted to share a use case of migrating MySQL database to MongoDB database and perhaps want to share few lessons during the migration process which would be helpful.

There are three steps to the migration process:

Suppose you have a user table with the following structure, which needs to be migrated to MongoDB collection named “user

[code]

+————+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————+————–+——+—–+———+—————-+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| address | varchar(255) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| first_name | varchar(255) | NO | | NULL | |
| last_name | varchar(255) | NO | | NULL | |
+————+————–+——+—–+———+—————-+

[/code]

Step 1 : Generate csv of the data for existing table :

[code]

select first_name, last_name, address, age, version into outfile ‘user.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ from user;

[/code]

This query will generate a file named user.csv into directory where MySQL is installed followed by your database name. Suppose your database name is Test, then file will be generated at /var/lib/mysql/test directory.

Step 2 : Now move the file to appropriate directory, or else you will get file permissions error. Incase, you have all permissions, you may skip this step.

[code] sudo mv /var/lib/mysql/test/user.csv Desktop/ [/code]

Step 3 : Now import generated csv into MongoDB

[code]

mongoimport –host 127.0.0.1 –port 27017–username user –password pass –db test –collection user –type csv -f first_name,last_name,address,age,version –file Desktop/user.csv

[/code]

This will import data into “user” collection  under test database.

I hope you found this blog useful with the steps for migrating from MySQL database to MongoDB.

Keep performing!!!

FOUND THIS USEFUL? SHARE IT

comments (4)

    1. Shahid

      Hi Ashok,
      Thanks for this useful info.
      Just want to ask you about the relational tables and views. I am trying to move my present site from MySql to MongoDB. And the challenge is that entire DB is in Mysql with multiple relations and around 10 views. Is there anything you can help guiding on this?

      Thanks
      Shahid

      Reply

Leave a Reply to Shahid Cancel reply

Your email address will not be published. Required fields are marked *