Converting your MySQL tables to mongodb collections

I needed to convert my mySQL database to mongodb collections. So I’ve written a PHP script to make this dirty work. It’s very simple to convert and if you need something like that, just copy the script below and run it. Pay attention if you have memory to get all entries in MySQL, if you don’t have just implement a new code to paginate the query to get these entries. Im my case I just used 500.000 entries and stopped here.

<?php
function getMyTables( $dbname ) {
 $tables = array();
 $sql = mysql_query("SHOW TABLES FROM $dbname ") or die("Error getting tables from $dbname");

 if( mysql_num_rows( $sql ) > 0 ) {
   while( $table = mysql_fetch_array( $sql ) ) {
     $explain = explainMyTable( $table[0] ); 
     $tables[$table[0]] = $explain;
    }
 }
 return $tables;
}

function explainMyTable( $tbname ) {
  $explain = array();
  $sql = mysql_query("EXPLAIN $tbname") or die("Error getting table structure");
  $i = 0;

  while( $get = mysql_fetch_array( $sql ) ) {
     array_push( $explain, $get[0] ); 
     $i++;
  }
  return $explain;
}

function checkEncode($string) {
  if( !mb_check_encoding($string,'UTF-8')) {
    return mb_convert_encoding($string,'UTF-8','ISO-8859-1');
  } else {
    return $string;
  }
}

$mydb   = "YOUR_MYSQL_DB_NAME";
$myconn = mysql_connect('MYSQL_HOST','MYSQL_USER','MYSQL_PASS');
$setmydb   = mysql_select_db( $mydb );
$mytables = getMyTables( $mydb );

$modb   = "YOUR_MONGO_DB_NAME";

try {
  $moconn = new Mongo();
  $modb = $moconn->selectDB( $modb );
} catch(MongoConnectionException $e) {
  die("Problem during mongodb initialization. Please start mongodb server.");
}

foreach( $mytables as $table => $struct ) {
  $sql = mysql_query("SELECT * FROM $table LIMIT 0 , 500000") or die( mysql_error() );
  $count = mysql_num_rows( $sql );

  // Starts new collection on mongodb
  $collection = $modb->$table;

  // If it has content insert all content
  if( $count > 0 ) {
    while( $info = mysql_fetch_array( $sql, MYSQL_NUM )) {
      $infosize = count( $info );
      $mosql = array();

      for( $i=0; $i < $infosize; $i++ ) {
        $mosql[$struct[$i]] = checkEncode($info[$i]);
  }

  $collection->insert($mosql);
  }
// Only create a new entry empty
} else {
  for( $i=0; $i < $infosize; $i++ ) {
    $mosql[$struct[$i]] = '';
  }

  $collection->insert($mosql);
  }
}

echo "Done! Please, check your mongodb collection and have fun!";
?>
Advertisements

6 thoughts on “Converting your MySQL tables to mongodb collections

  1. Good afternoon everyone!
    I created a database with two tables, but okay occurring errors.
    Can anyone tell me why this code:https://github.com/suissa/mongodb-exemplos/blob/master/conversor_mysql_mongodb/converte_mysql_mongo-2_new.php when run only creates the database, the name of the tables usually in mongodb. But only puts the data from the first table normally. In the second table, it repeats data from the first. Data from the second table are not converted. Does anybody know why this occurs and how to solve?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s