converting database from mysql to mongoDb

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);
});

If anyone's still looking for a solution, i found that the easiest way is to write a PHP script to connect to your SQL DB, retrieve the information you want using the usual Select statement, transform the information into JSON using the PHP JSON Encode functions and simply output your results to file or directly to MongoDB. It's actually pretty simple and straight forward, the only thing to do is to double check your output against a Json validator, you may have to use functions such as explode to replace certain characters and symbols to make it valid. I have done this before however i currently do not have the script at hand but from what i can remember it was literally half a page of code.

Oh also remember Mongo is a document store so some data mapping is required to get it to be acceptable with mongo.

For those coming to this with the same problem, you can check out this [Github project][1]. This is an ongoing development that will help you migrate data from MySQL database to MongoDB by simply running a simple command.

It will generate MongoDB Schemas in TypeScript so you can use them later in your project. Each MySQL table will be a MongoDB collection, and datatypes will be efficiently converted to their MongoDB compatibles.

The documentation for the same can be found in the project's [README.md][1]. Feel free to come in and contribute. Would like to help if need be.


[1]:

[To see links please register here]

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][1] for more information

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


[1]:

[To see links please register here]

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