MySQL to MongoDB Migration

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

| 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 | |

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

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

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.

sudo mv /var/lib/mysql/test/user.csv Desktop/

Step 3 : Now import generated csv into MongoDB

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

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!!!

MySQL to MongoDB Migration

Neeraj Gupta

A Solution Architect who has helped customers in cloud adoption and build successful products on the AWS platform. In the past few years, I got the opportunity to work with multiple customers in their AWS Cost Optimisation initiatives and have successfully brought their AWS costs down by 30%-40%. In the available time, I like to work on the latest tools & technologies.

Post navigation

Leave a Reply

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