Moving a MySQL Database to a MongoDB DBaaS
A DBaaS is accessed through a set of common APIs, and provides the full functionality of a traditional database. The primary advantages of a DBaaS include automatic scaling, fast failure recovery and increased accessibility. The challenges of migrating to a DBaaS include privacy, security and the inability to access data during a disaster. The MongoDB open-source database has been available from Rackspace's data center in Chicago since early March, and this database will also be integrated into Rackspace's entire cloud portfolio in the near future. This article will show how to migrate a MySQL database to a MongoDB database.
MongoDB
MongoDB is a NoSQL database as opposed to a traditional RDBMS that requires SQL to conduct queries. A NoSQL database stores data in documents using a format such as JavaScript Object Notation. MongoDB uses a query language that is based on this document format, which allows queries to be easily translated from an RDMS. This RDMS also resolves many of the performance bottlenecks common to an RDMS such as MySQL. This allows MongoDB to provide the performance, scalability and self replication you expect in a DBaaS. MongoDB supports many scripting languages which you can review at http://www.mongodb.org/display/DOCS/Drivers.
MongoDB Installation
Install MongoDB using Ubuntu to ensure the authenticity and consistency of the package. Ubuntu accomplishes this by requiring you to provide the public GNU Privacy Guard Key for MongoDB from 10gen as follows:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv 7F0CEB10
You can view the GPG key for MongoDB at http://docs.mongodb.org/10gen-gpg-key.asc.
Create a file named /etc/apt/sources.list.d/10gen.list file with the following line so that the installation can access the 10gen repository:
deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen
Execute this command to reload the 10gen repository:
sudo apt-get update
Install the current stable version of MongoDB with this command:
sudo apt-get install mongodb-10gen
Configure your MongoDB installation with the MongoDB control script and its configuration file. The control script is /etc/init.d/mongodb and the configuration file /etc/mongodb.conf. An instance of MongoDB stores data files in the directory /var/lib/mongodb.
You will typically start MongoDB under the mongodb user account. However, you can also start it under another account by modifying the /var/log/mongodb and /var/lib/mongodb directories to be accessible by that user account. Start MongoDB with the following command:
sudo service mongodb start
Read the log file /var/log/mongodb/mongodb.log to ensure MongoDB has started successfully.
The MongoDB package contains a variety of tools such as a command shell. Connect to the active instance of MongoDB with the following command:
mongo
This command will return ">", which is the prompt for the MongoDB command shell. Common commands in this command shell include the following:
//display the current databases
>show dbs ()
//switch to database_name
>use
//create a collection
>db.createCollection("collection_name")
//display the contents of a collection
>db.collection_name.find()
//create user with password
>db.addUser("user_name", "password")
Install PHP Drivers for MongoDB
Ensure the MongoDB server is running before you attempt to install the PHP drivers for MongoDB. The name of this server is mongod, whereas the name of the MongoDB client is mongo. Download the correct driver for your platform from http://github.com/mongodb/mongo-php-driver/downloads. Extract php_mongo.dll from the downloaded file and add it to the directory for your PHP extensions, typically the ext directory in your PHP directory.
Install the PHP drivers with the following command:
sudo pecl install mongo
Add the following line to your php.ini file so that PHP will recognize the extensions for MongoDB:
extension=mongo.so
extension=php_mongo.dll
You will typically add the above lines to the section that has the other extensions, although you can place them anywhere in the php.ini file. Restart your web server so the changes to php.ini will take effect.
Convert a MySQL Database to a MongoDB Database
Create a PHP script to convert your MySQL database to a MongoDB database. The following script will require you to provide information on both your MySQL database and your MongoDB database:
//start a php session
//MySQL settings including database, tables and connection
$database_name = "database";
$setdbname = mysql_select_db($database_name);
$tables = get_tables($database_name);
$myconnection = mysql_connect('localhost','user','password');
//MongoDB settings
$mongodb = "database";
$mongo_connect="mongodb://user:password@localhost";
// Retrieve MongoDB tables
function get_tables($mongodb_name) {
$tables = array();
$sql_results = mysql_query("SHOW TABLES FROM $mongodb_name") or die("Couldn't retrieve tables from $mongodb_name");
if(mysql_num_rows($sql_results) > 0) {
while($mytable = mysql_fetch_array($sql_results)) {
$reason = explain_tables($mytable[0]);
$tables[$mytable[0]] = $reason;
}
}
return $tables;
}
//Retrieve MongoDB table structure
function explain_tables($table_name) {
$reason = array();
$sql_results = mysql_query("EXPLAIN $table_name") or die("Couldn't retrieve table structure");
$i = 0;
while($get = mysql_fetch_array($sql_results)) {
array_push( $reason, $get[0] );
$i++;
}
return $reason;
}
function check_character_code($string) {
if( !mb_check_encoding($string,'UTF-8′)) {
return mb_convert_encoding($string,'UTF-8′,'ISO-8859-1′);
}
else {
return $string;
}
}
//Connect to the MongoDB database
try {
$my_mongo_connect = new Mongo($mongo_connect);
$mongodb = $my_mongo_connect->selectDB($mongodb);
}
catch(MongoConnectionException $e) {
die($e."error during MongoDB initialization. Please restart MongoDB server.");
}
foreach( $tables as $mytable => $struct ) {
$sql_results = mysql_query("SELECT * FROM $mytable LIMIT 0, 500000″) or die( mysql_error() );
$count = mysql_num_rows($sql_results);
//Create new collection on mongodb
$mycollection = $mongodb->$mytable;
// Insert content if the collection has content
if($count > 0) {
while($data = mysql_fetch_array($sql_results, MYSQL_NUM)) {
$datasize = count($data);
$mongo_sql = array();
for( $i=0; $i < $datasize; $i++ ) {
if(!empty($structure[$i]))
$mongo_sql[$structure[$i]] = check_character_code($data[$i]);
}
$mycollection->insert($mongo_sql);
}
}
//Create a new entry only if the table is empty
else {
for( $i=0; $i < $datasize; $i++ ) {
if(!empty($structure[$i]))
$mongo_sql[$structure[$i]] = ";
}
$collection->insert($mongo_sql);
}
}
echo "MongoDB database conversion completed.";
//Close PHP session
?>
Launch a browser session and load your MongoDB page. You will see the message "MongoDB database conversion completed." if the script executed correctly. Review the MongoDB collection to ensure the MySQL table is present.
Summary
This procedure covered the basics steps involved in migrating from MySQL to MongoDB, including installing MongoDB, installing the PHP drivers for MongoDB and converting MySQL tables to MondoDB tables. The conversion of a large database may use additional steps to optimize the performance of this process such as running conversion scripts in parallel. This conversion will likely involve greater error checking when you use it on a production database.
Sources
http://www.rackspace.com/blog/why-mongodb/
http://www.migrate2cloud.com/blog/
http://www.migrate2cloud.com/blog/installation-of-mongodb-and-its-performance-test
http://www.rackspace.com/blog/why-mongodb/
http://www.migrate2cloud.com/blog/migrate-mysql-database-to-mongodb
http://docs.mongodb.org/ecosystem/drivers/php/
http://blog.objectrocket.com/2012/10/03/connecting-to-objectrocket/
Copyright © 2018 McMahan Writing - All Rights Reserved.
Powered by GoDaddy