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”

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