converting database from mysql to mongoDb

is there any easy way to change the database from mysql to mongoDB ?

Method #1: export from MySQL in a CSV format and then use the mongoimport tool. However, this does not always work well in terms of handling dates of binary data.

Method #2: script the transfer in your language of choice. Basically you write a program that reads everything from MySQL one element at a time and then inserts it into MongoDB.

Method #2 is better than #1, but it is still not adequate.

MongoDB uses collections instead of tables. MongoDB does not support joins. In every database I've seen, this means that your data structure in MongoDB is different from the structure in MySQL.

Because of this, there is no "universal tool" for porting SQL to MongoDB. Your data will need to be transformed before it reaches MongoDB.

If you're using Ruby, you can also try: Mongify

It's a super simple way to transform your data from a RDBS to MongoDB without losing anything.

Mongify will read your mysql database, build a translation file for you and all you have to do is map how you want your data transformed.

It supports:

• Auto updating IDs (to BSON ObjectID)
• Updating referencing IDs
• Type Casting values
• Embedding tables into other documents
• Before save filters (to allow changes to the data manually)
• and much much more...

Read more about it at: http://mongify.com/getting_started.html

There is also a short 5 min video on the homepage that shows you how easy it is.

Here's what I did it with Node.js for this purpose:

var mysql = require('mysql');
var MongoClient = require('mongodb').MongoClient;

function getMysqlTables(mysqlConnection, callback) {
    mysqlConnection.query("show full tables where Table_Type = 'BASE TABLE';", function(error, results, fields) {
        if (error) {
            callback(error);
        } else {
            var tables = [];
            results.forEach(function (row) {
                for (var key in row) {
                    if (row.hasOwnProperty(key)) {
                        if(key.startsWith('Tables_in')) {
                            tables.push(row[key]);
                        }
                    }
                }
            });
            callback(null, tables);
        }
    });

}

function tableToCollection(mysqlConnection, tableName, mongoCollection, callback) {
    var sql = 'SELECT * FROM ' + tableName + ';';
    mysqlConnection.query(sql, function (error, results, fields) {
        if (error) {
            callback(error);
        } else {
            if (results.length > 0) {
                mongoCollection.insertMany(results, {}, function (error) {
                    if (error) {
                        callback(error);
                    } else {
                        callback(null);
                    }
                });
            } else {
                callback(null);
            }
        }
    });
}

MongoClient.connect("mongodb://localhost:27017/importedDb", function (error, db) {
    if (error) throw error;

    var MysqlCon = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'root',
        port: 8889,
        database: 'dbToExport'
    });

    MysqlCon.connect();

    var jobs = 0;

    getMysqlTables(MysqlCon, function(error, tables) {
        tables.forEach(function(table) {
            var collection = db.collection(table);
            ++jobs;
            tableToCollection(MysqlCon, table, collection, function(error) {
                if (error) throw error;
                --jobs;
            });
        })
    });

    // Waiting for all jobs to complete before closing databases connections.
    var interval = setInterval(function() {
        if(jobs<=0) {
            clearInterval(interval);
            console.log('done!');
            db.close();
            MysqlCon.end();
        }
    }, 300);
});

MongoVUE's free version can do this automatically for you.

It can connect to both databases and perform the import

I think one of the easiest ways is to export the MySQL database to JSON and then use mongorestore to import it to a MongoDB database.

Step 1: Export the MySQL database to JSON

Load the mysql dump file into a MySQL database if necessary

Open MySQL Workbench and connect to the MySQL database

Go to the Schema viewer > Select database > Tables > right-click on the name of the table to export

Select 'Table Data Export Wizard'

Set the file format to .json and type in a filename such as tablename.json

Note: All tables will need to be exported individually

Step 2: Import the JSON files to a MongoDB using the mongorestore command

The mongorestore command should be run from the server command line (not the mongo shell)

Note that you may need to provide the authentication details as well as the --jsonArray option, see the mongorestore docs for more information

mongoimport -d dbname -u ${MONGO_USERNAME} -p ${MONGO_PASSWORD} --authenticationDatabase admin -c collectionname --jsonArray --file tablename.json

Note: This method will not work if the original MySQL database has BLOBs/binary data.