Convert a 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. I will focus on installation of MongoDB on Centos and conversion of a MySQL database using PHP.

Now lets get started by connecting to our server via SSH

  1. sudo yum install gcc php php-pear -y
  2.  
  3. sudo pecl install mongo
  4.  
  5. sudo echo ?extension=mongo.so? >> /etc/php.ini
  6.  
  7. sudo /etc/init.d/httpd restart
  8.  
  9. created blank file called 10gen.repo in /etc/yum.repos.d/
  10.  
  11. vi /etc/yum.repos.d/10gen.repo
  12.  
  13. past the content below
  14.  
  15. For CentOS on x86_64(64-bit)
  16. #------ copy after this line --------
  17. [10gen]
  18. name=10gen Repository
  19. baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64
  20. gpgcheck=0
  21. #------ end copy ---------
  22.  
  23. For CentOS 5.4 and Fedora on x86(32-bit)
  24. #------ copy after this line ---------
  25. [10gen]
  26. name=10gen Repository
  27. baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/i686
  28. gpgcheck=0
  29. #------ end copy ---------
  30.  
  31. save in vi by using :wq
  32.  
  33. sudo yum install mongo-10gen mongo-10gen-server -y
  34.  
  35. sudo /etc/init.d/mongod start
  36.  
  37. sudo chkconfig mongod o

Lets make sure that the database is working

  1. now type "mongo" this will connect you to the database shell
  2.  
  3. If all goes well you should see something like:
  4. MongoDB shell version: 1.8.2
  5. connecting to: test

Now we can use our script to convert our MySQL database to MongoDB

create a new file called convertMySqlToMongo.php

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

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

That's it for now! Please leave a comment if you have any questions.

Newsletter Sign Up

Sign Up
SafeSubscribe with Constant Contact
cosmiceggstudioCosmic Egg Studios
@cosmiceggstudio:
10 Things You Probably Didn’t Know About The iPhone! http://t.co/oWZoxPZw3U
46 months ago
cosmiceggstudioCosmic Egg Studios
@cosmiceggstudio:
Check out these promotional bottles we created for Dr. Bronner's to support GMO labeling in Washington! http://t.co/PJ7khKBO24
48 months ago
cosmiceggstudioCosmic Egg Studios
@cosmiceggstudio:
Have a Safe and Happy 4th of July!
49 months ago
cosmiceggstudioCosmic Egg Studios
@cosmiceggstudio:
Here's another recent site launch for a great client and they happen to be the leaders in the aerospace... http://t.co/wON4oC6dNx
50 months ago
cosmiceggstudioCosmic Egg Studios
@cosmiceggstudio:
Dr. Bronner's customized tent and promotional materials! http://t.co/FN6Iyi4o8u
50 months ago
cosmiceggstudioCosmic Egg Studios
@cosmiceggstudio:
Dr. Bronner's customized flags and signage for their shower! http://t.co/ZNSh6GrLqk
50 months ago