Migrate Mysql database to Mongodb

I recently had a chance to try out one of the new NoSQL databases called MongoDB http://www.mongodb.org. This new class of databases seems to solve many of the bottlenecks in MySql and other relational databases. It will give you shear performance, self replication and scalability at not cost because it open source. MongoDB has plenty of drivers for other scripting and high-level languages I use PHP so I download the PHP driver. You can see the supported list here: http://www.mongodb.org/display/DOCS/Drivers. In this blog I convert  a MySQL database using PHP to MongoDB.

First you install MongoDB, you can do it by checking my previous blog.

Then we run the script to convert a Mysql DB to Mongodb.

create a new file called MySqltoMongodb.php, In that file please copy paste the below contants (please give your Mysql DB details as well as your Mongodb details)

  1. <?php
  2. // mysql settings
  3. $mydb = “database”;
  4. $myconn = mysql_connect(‘localhost’,’user’,’password’);
  5. $setmydb = mysql_select_db( $mydb );
  6. $mytables = getMyTables( $mydb );
  7.  //mongo db settings
  8. $modb = “database”;
  9. $moConnect=”mongodb://user:password@localhost”;
  10.  function getMyTables( $dbname ) {
  11. $tables = array();
  12. $sql = mysql_query(“SHOW TABLES FROM $dbname “) or die(“Error getting tables from $dbname”);
  13.  if( mysql_num_rows( $sql ) > 0 ) {
  14. while( $table = mysql_fetch_array( $sql ) ) {
  15. $explain = explainMyTable( $table[0] );
  16. $tables[$table[0]] = $explain;
  17. }
  18. }
  19. return $tables;
  20. }
  21.  function explainMyTable( $tbname ) {
  22. $explain = array();
  23. $sql = mysql_query(“EXPLAIN $tbname”) or die(“Error getting table structure”);
  24. $i = 0;
  25.  while( $get = mysql_fetch_array( $sql ) ) {
  26. array_push( $explain, $get[0] );
  27. $i++;
  28. }
  29. return $explain;
  30. }
  31.  function checkEncode($string) {
  32. if( !mb_check_encoding($string,’UTF-8′)) {
  33. return mb_convert_encoding($string,’UTF-8′,’ISO-8859-1′);
  34. } else {
  35. return $string;
  36. }
  37.  }
  38. try {
  39. $moconn = new Mongo($moConnect);
  40. $modb = $moconn->selectDB( $modb );
  41. } catch(MongoConnectionException $e) {
  42. die($e.”Problem during mongodb initialization. Please start mongodb server.”);
  43. }
  44.  foreach( $mytables as $table => $struct ) {
  45. $sql = mysql_query(“SELECT * FROM $table LIMIT 0 , 500000”) or die( mysql_error() );
  46. $count = mysql_num_rows( $sql );
  47.  // Starts new collection on mongodb
  48. $collection = $modb->$table;
  49.  // If it has content insert all content
  50. if( $count > 0 ) {
  51. while( $info = mysql_fetch_array( $sql, MYSQL_NUM )) {
  52. $infosize = count( $info );
  53. $mosql = array();
  54.  for( $i=0; $i < $infosize; $i++ ) {
  55. if(!empty($struct[$i]))
  56. $mosql[$struct[$i]] = checkEncode($info[$i]);
  57. }
  58.  $collection->insert($mosql);
  59. }
  60. // Only create a new entry empty
  61. } else {
  62.  for( $i=0; $i < $infosize; $i++ ) {
  63. if(!empty($struct[$i]))
  64. $mosql[$struct[$i]] = ”;
  65.  }
  66. $collection->insert($mosql);
  67. }
  68. }
  69.  echo “Done! Please, check your MongoDB collection!”;
  70. ?>

Now fire up your browser and launch the page. If all all goes well you should see
“Done! Please, check your MongoDB collection!”

After running this script check your Mongo db collection, in that you can see your Mysql Table.



Why MongoDB?

  • Document-oriented
    • Documents (objects) map nicely to programming language data types
    • Embedded documents and arrays reduce need for joins
    • Dynamically-typed (schemaless) for easy schema evolution
    • No joins and no multi-document transactions for high performance and easy scalability
  • High performance
    • No joins and embedding makes reads and writes fast
    • Indexes including indexing of keys from embedded documents and arrays
    • Optional streaming writes (no acknowledgements)
  • High availability
    • Replicated servers with automatic master failover
  • Easy scalability
    • Automatic sharding (auto-partitioning of data across servers)
      • Reads and writes are distributed over shards
      • No joins or multi-document transactions make distributed queries easy and fast
    • Eventually-consistent reads can be distributed over replicated servers
  • Rich query language

Mongo data model

  • A Mongo system (see deployment above) holds a set of databases
  • A database holds a set of collections
  • A collection holds a set of documents
  • A document is a set of fields
  • A field is a key-value pair
  • A key is a name (string)
  • A value is a
    • basic type like string, integer, float, timestamp, binary, etc.,
    • a document, or
    • an array of value


    • Mongo query language


  • To retrieve certain documents from a db collection, you supply a query document containing the fields the desired documents should match. For example, {name: {first: 'John', last: 'Doe'}} will match all documents in the collection with name of John Doe. Likewise, {name.last: 'Doe'} will match all documents with last name of Doe. Also, {name.last: /^D/} will match all documents with last name starting with ‘D’ (regular expression match).
  • Queries will also match inside embedded arrays. For example, {keywords: 'storage'} will match all documents with ‘storage’ in its keywords array. Likewise, {keywords: {$in: ['storage', 'DBMS']}} will match all documents with ‘storage’ or ‘DBMS’ in its keywords array.
  • If you have lots of documents in a collection and you want to make a query fast then build an index for that query. For example, ensureIndex({name.last: 1}) or ensureIndex({keywords: 1}). Note, indexes occupy space and slow down updates a bit, so use them only when the tradeoff is worth it.

Install MongoDB on Ubuntu 10.04

Configure Package Management System (APT)

The Ubuntu package management tool (i.e. dpkg and apt) ensure package consistency and authenticity by requiring that distributors sign packages with GPG keys. Issue the following command to import the 10gen public GPG Key:

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv 7F0CEB10

Create a /etc/apt/sources.list.d/10gen.list file and include the following line for the 10gen repository.

deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen

Now issue the following command to reload your repository:

sudo apt-get update

Install Packages

Issue the following command to install the latest stable version of MongoDB:

sudo apt-get install mongodb-10gen

When this command completes, you have successfully installed MongoDB! Continue for configuration and start-up suggestions.

Configure MongoDB

These packages configure MongoDB using the /etc/mongodb.conf file in conjunction with the control script. You will find the control script is at /etc/init.d/mongodb.

This MongoDB instance will store its data files in the /var/lib/mongodb and its log files in /var/log/mongodb, and run using the mongodb user account.


If you change the user that runs the MongoDB process, you will need to modify the access control rights to the /var/lib/mongodb and /var/log/mongodb directories.

Controlling MongoDB

Starting MongoDB

You can start the mongod process by issuing the following command:

sudo service mongodb start

You can verify that mongod has started successfully by checking the contents of the log file at /var/log/mongodb/mongodb.log.

Stopping MongoDB

As needed, you may stop the mongod process by issuing the following command:

sudo service mongodb stop

Restarting MongoDB

You may restart the mongod process by issuing the following command:

sudo service mongodb restart

Controlling mongos

As of the current release, there are no control scripts for mongos. mongos is only used in sharding deployments and typically do not run on the same systems where mongod runs. You can use the mongodb script referenced above to derive your own mongos control script.

Using MongoDB

Among the tools included with the MongoDB package, is the mongo shell. You can connect to your MongoDB instance by issuing the following command at the system prompt:

> show dbs (); ---> to show your databases 
> use <databasename> ----> To switch database 
> db.createCollection("collectionname") ---> to create collection 
> db.collectionname.find(); ---> To see the contents in the collection
> db.addUser("theadmin", "anadminpassword") ---> To create user and password