Tuesday, 23 October 2012

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 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 ( 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…. SmileSmileSmileMug

Friday, 5 October 2012

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/
# 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 /var/www/redmine>
RailsBaseURI /redmine
PassengerResolveSymlinksInDocumentRoot on
<Directory /var/www>
RailsBaseURI /redmine
PassengerResolveSymlinksInDocumentRoot on
Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
allow from all
ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
<Directory "/usr/lib/cgi-bin">
AllowOverride None
Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
Order allow,deny
Allow from all
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 ::1/128
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
    delivery_method: :smtp
      address: ""
      port: 25
      domain: ""
      authentication: :plain
      user_name: "xxxxxx"
      password: "xxxxxx"

    delivery_method: :smtp
      address: ""
      port: 25
      domain: ""
      authentication: :plain
      user_name: "xxxxxx"
      password: "xxxxxx"

Restart redmine to enable the email settings.