MongoDB Recipes: Importing a MySQL table into MongoDB

Importing an existing MySQL table into MongoDB collection is a very trivial task. I will first export the MySQL table into comma separated values (csv) file and then import the csv file into a MongoDB collection.
Step 1: Exporting MySQL table into csv file:
This can be done in many ways. I will show you simple one using the SQLYog Community edition (from http://www.webyog.com/). Connect the MySQL server using SQLYog and select the table to export. Right click on it and go to Backup/Export –> Export Table Data as CVS, SQL, Excel etc….


Select CSV format, and the Fields of the table to export. Enter the output file name and click on Export button


Once the table is exported successfully, we will see the success message.


We can see 4845 rows are exported to the csv file.
Now copy the csv file (in my case mongotest.csv) into the mongodb server.

Step 2: Importing the csv file into MongoDB:
Run the mongoimport tool to import the csv file.
root@redmine:~# mongoimport --db test --collection talukamaster --type csv -fields TalukaID,DistrictID,TalukaName,Enabled  --file mongotest.csv
--db or –d : MongoDB database to use
--collection or –c : Collection to use
--type: Type of file to import
--fields or –f : Comma separated list of field names
--file: File to import from

From the output, we can see that 4845 objects are imported by the mongoimport tool, which is same as the number of rows exported. That means all the MySQL table rows are imported successfully.

I will use one MongoDB GUI MongoVUE (http://blog.mongovue.com/) to view the just imported collection in MongoDB.
Connect to MongoDB server, if you do not have any saved connections, click on the add new connection button (green + button)


Enter the connection details, to check the connection settings, click on Test button.


If settings and OK and DB connection is successful, we can see the success message.


Now connect to the MongoDB server using the new connection that we just created.


Navigate to the collection that we imported using the mongoimport tool.


Right click on the collection name and click on View to view the documents stored in this collection.




As we are used to with viewing data in tabular format, so click on Table View tab. We can have our familiar RDBMS table like view of the collection….

Installing Redmine in Ubuntu 12.04

Here I am running all the commands from the root user command prompt. If you login as normal user, add sudo before the commands shown here.

Update the local package index in the Ubuntu system with the latest entries in repositories
# apt-get update
 
Upgrade the system with latest updated version
# apt-get upgrade
 
Install Apache webserver
# apt-get install apache2
# apt-get install apache2-threaded-dev
 
Install build-essential
# apt-get install build-essential
 
Install packages required for Redmine
# apt-get install libapache-dbi-perl
# apt-get install libapache2-mod-perl2
# apt-get install libapache2-svn
# apt-get install libgemplugin-ruby
# apt-get install libgemplugin-ruby1.8
# apt-get install mongrel
# apt-get install mysql-server
# apt-get install rails
# apt-get install rake
# apt-get install ruby
# apt-get install rubygems
# apt-get install rubygems1.8
# apt-get install ruby1.8-dev
# apt-get install subversion
# sudo apt-get install libcurl4-openssl-dev
 
Install Redmine application
# apt-get install redmine redmine-mysql
 
Create a symbolic link of redmine’s web folder into Apache’s document root directory
# ln -s /usr/share/redmine/public /var/www/redmine
 
Change the owner of redmine directory
# chown -R www-data:www-data ./redmine
 
Install Passenger
# gem install passenger
# cd /var/lib/gems/1.8/gems/passenger-3.0.17/
# ./bin/passenger-install-apache2-module
 
Create passenger.load and passenger.conf files in /etc/apache2/mods-available directory
# cd /etc/apache2/mods-available
 
# pico passenger.load
Paste following line in passenger.load file
LoadModule passenger_module /var/lib/gems/1.8/gems/passenger-3.0.17/ext/apache2/mod_passenger.so
 
# pico passenger.conf
Paste following lines in passenger.conf file
PassengerRoot /var/lib/gems/1.8/gems/passenger-3.0.17
PassengerRuby /usr/bin/ruby1.8
Enable the passenger module in Apache
# a2enmod passenger
 
I will use Redmine as the default site in Apache. So I am going to change the default site configuration file of Apache.
# cd /etc/apache2/sites-available
# pico default
<VirtualHost *:80>
ServerAdmin webmaster@localhost
DocumentRoot /var/www/redmine
<Directory />
Options FollowSymLinks
AllowOverride None
</Directory>
<Directory /var/www/redmine>
RailsBaseURI /redmine
PassengerResolveSymlinksInDocumentRoot on
</Directory>
<Directory /var/www>
RailsBaseURI /redmine
PassengerResolveSymlinksInDocumentRoot on
Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
allow from all
</Directory>
ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
<Directory "/usr/lib/cgi-bin">
AllowOverride None
Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
Order allow,deny
Allow from all
</Directory>
ErrorLog ${APACHE_LOG_DIR}/error.log
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
LogLevel warn
CustomLog ${APACHE_LOG_DIR}/access.log combined
Alias /doc/ "/usr/share/doc/"
<Directory "/usr/share/doc/">
Options Indexes MultiViews FollowSymLinks
AllowOverride None
Order deny,allow
Deny from all
Allow from 127.0.0.0/255.0.0.0 ::1/128
</Directory>
</VirtualHost>
 
Restart Apache and we are ready with Redmine.
# service apache2 restart
 
Optional Configuration
Now if we want to configure Email Notification for Redmine, login as admin user and go to Settings –> Email notifications. Now we have to add configuration.yml file in /etc/redmine/<instance> folder. In my case I am using the default redmine instance, so the file will be /etc/redmine/default/configuration.yml  
 

In this example I am using sendgrid SMTP server to send emails, so my configuration.yml file will look like:
# Outgoing email settings
production:
  email_delivery:
    delivery_method: :smtp
    smtp_settings:
      address: "smtp.sendgrid.net"
      port: 25
      domain: "smtp.sendgrid.net"
      authentication: :plain
      user_name: "xxxxxx"
      password: "xxxxxx"
development:
  email_delivery:
    delivery_method: :smtp
    smtp_settings:
      address: "smtp.sendgrid.net"
      port: 25
      domain: "smtp.sendgrid.net"
      authentication: :plain
      user_name: "xxxxxx"
      password: "xxxxxx"

Restart redmine to enable the email settings.