04 May

Steps to Move Copy Rename MySQL Database

Moving, copying or renaming database is a very basic activity. I have just noted a few commands for reference to quickly follow the required operation.

1. Rename database on Linux Machine:

A. Use RENAME DATABASE Command [MySQL 5.1].

RENAME DATABASE db_name TO new_db_name;

B.

mysql -uroot -pPASSWORD -e “drop database if exists NEW-DB-NAME; create database NEW-DB-NAME” && mysqldump -uroot -pPASSWORD SOURCE-DB-NAME | mysql -uroot -pPASSWORD NEW-DB-NAME && mysql -uroot -pPASSWORD -e “drop database SOURCE-DB-NAME”

2. Create Duplicate of a database:

mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword DB_TO_BE_CREATED

3. Copy MySQL Database to Remote MySQL Server:

[ad#ad-2-300×250]

3A. By using -h (hostname) option.

Create MySQL Dump:

mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Load MySQL Dump:

mysql -uroot -ppassword -hHOSTNAME < DB_TO_BE_COPIED.sql OR Using Single Command: mysql -uroot -ppassword -e “drop database if exists DB_TO_BE_CREATED;create database DB_TO_BE_CREATED;” | mysqldump -uroot -ppassword DB_TO_BE_COPIED | mysql -uroot -ppassword -hHOSTNAME DB_TO_BE_CREATED 3B. By moving dump file to remote server. Create MySQL Dump: mysqldump -uroot -ppassword –databases DB_TO_BE_COPIED > DB_TO_BE_COPIED.sql

Copy SQL file to remote MySQL Server:

scp DB_TO_BE_COPIED.sql username@remote-machin:/path/to/copy

Login to remote-machin.

Load MySQL Dump:

mysql -uroot -ppassword DB_TO_BE_CREATED < /path/to/copy/DB_TO_BE_COPIED.sql

Here, I haven’t considered copying and moving data-files for MyISAM databases as these are just the commands-way.

27 Apr

Google:Earth View on Google maps

When have you lastly visited Google Maps? Google have now Introduced Google Earth on Google Maps. Now using Browser you can  see 3D maps online; they named it “Earth View”.

Till now we were just looking at flat terrains but after installing the Earth View Plugin, you will be able to get actual 3D view. You don’t need to install the Google Earth Software, this is simple browser plugin that will do the trick.

You can see the places like Pyramids of Egypt, the Taj Mahal, Eiffel, the Great Wall of China, the Eiffel Tower and many other favourites in 3D mode.

3D Earth View of Effiel Tower

[ad#ad-2-250x250img]

3D Taj Mahal from Earth View

3D Taj Mahal from Earth View

You just need to download the Google Earth View plugin: http://earth.google.com/plugin/

Enjoy.

08 Apr

MySQL Load Data Infile Syntax Generator Tool Download

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

I have already written the basic how to follow that considering additional cases.

A lot of questions I have seen for loading separated data to MySQL, so here I’ve created a very beginner level excel tool that will allow you to generate the LOAD DATA syntax as per your choices.
Presently sample table name and field/line separators are specified, which you may alter as per your own file.

Load-data-syntax-v1.0

Load-data-syntax-v1.0

Download the sheet here: Load Data

Load Data Syntax:

Options column specifies options that are required for syntaxes generation.
Under Selection column you will choose your option while under Description column, for each Option description is provided.

Using basic knowledge of load data you can quite easily generate the syntax by specifying option, choosing file to be loaded and finally clicking the Syntax button.

Please refer the syntax on MySQL documentation as well.

This is a “early release”, please check for the updates.
If you have any suggestions or bug report please comment / mail.

Thanks.

05 Apr

In the world of NoSQL-Hands on Mongodb-1

I’m hearing a lot of “NoSQL” these days. To really understand how (and) does it works, I decided to give a try on MongoDB.

MongoDB (hu*mongo*us) is an open source, scalable, high-performance, schema-free, document-oriented database written in the C++ programming language.

MongoDB is not a Relational Database Management System. The database manages collections of JSON (JavaScript Object Notation)-like documents which are stored in a binary format referred to as BSON (Binary JSON).

This post will surely get you started with the MongoDB-NoSQL.

Installing MongoDB:

  • Download Mongodb from www.mongodb.org
  • Extract binaries
  • Create data directory – default for windows C:\data\db
  • Install service: Full\path\to\mongod.exe –install

If you want to change default data directory location:
Full\path\to\mongod.exe –dbpath=”\”full\path\to\”” –install

Troubleshooting Mongodb Installtion Error:
I faced while installing, if you get error like:
dbexit:
shutdown: going to close listening sockets.
shutdown: going to flush oplog…
shutdown: going to close sockets…
shutdown: waiting for fs preallocator…
shutdown: closing all files…
closeAllFiles() finished
dbexit: really exiting now

Go to: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/
Check for MongoDB
Verify the value of ImagePath is proper

This seems to be caused by not giving full-path while installing.

Add Environment variable Path, add path/upto/mongo-bin-dir;

Okay, now we’re all set to checkout the MongoDB-NoSQL world.

MongoDB GUI Administration Tool:

There is a GUI Admin tool available phpmoadmin to simplify the MongoDB Operations.
Signle php script: phpmoadmin
Download PHP extension from php.net/manual/en/mongo.installation.php
Extract in ext directory of WAMP & Restart.

In case of error you may install Microsoft Visual C++ 2008 SP1 Redistributable Package (x86) [www.microsoft.com/downloads/details.aspx?familyid=A5C84275-3B97-4AB7-A40D-3802B2AF5FC2&displaylang=en]

MySQL to MongoDB Database Migration:

Create CSV from MySQL:
mysql> select * into outfile “c:/TABLENAME.csv” FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ from TABLENAME;
Query OK, 293911 rows affected (1 min 8.17 sec)


Import CSV to mongodb using mongoimport:
C:\>mongoimport -d DATABASENAME -c TABLENAME –file “c:\TABLENAME.csv” –type csv -f FIELD1,FIELD2…

Using MongoDB on Windows:

On Command Prompt:
C:\MongoDB\mongodb-win32-i386-1.4.0\bin>mongo.exe
MongoDB shell version: 1.4.0
url: test
connecting to: test
type “exit” to exit
type “help” for help

By default it’s connecting to test database.
Default mongodb port is 9999.

Basic Commands

  • show dbs : show database names
  • show collections : show collections in current database
  • show users : show users in current database
  • show profile : show most recent system.profile entries with time >= 1ms
  • use : set curent database to
  • db.help() : help on DB methods
  • db.foo.help() : help on collection methods
  • db.foo.find() : list objects in collection foo
  • db.foo.find( { a : 1 } ) : list objects in foo where a == 1 it result of the last line evaluated; use to further iterate

Simple Queries with MongoDB:

Set Current Database:
>use DATABASENAME


See Present Database:
> db
DATABASENAME


View collections (tables) under present db:
> db.getCollectionNames()
[ “TABLENAME”, “system.indexes” ]


SQL>Select count(*) from TABLENAME:
NoSQL> db.TABLENAME.count()
238735


SQL>select * from TABLENAME where field1 regexp ‘enrique':
NoSQL>db.TABLENAME.find( { field1 : /enriqu/i} );


For simple find:
NoSQL>db.TABLENAME.find( { field1 : “value” } );


SQL>Select field1 from table where field2=”%search%”:
NoSQL>db.TABLENAME.find( { field2 : /search/i} , {field1 : 1 } );


SQL>Select All but one (field1) column:
NoSQL>db.TABLENAME.find( { field2 : /search/i} , {field1 : 0 } );


SQL>Select with sort order ascending and descending (1,-1):
NoSQL>db.TABLENAME.find( { ar_name : /enrique/i }, {field1 : 1 } ).sort({ field1 : -1})

MongoDB now seems easy to follow, but I’m still having hard time in taking on the Map/Reduce.
More in the next post; ofcourse documentation home is a good place, else you may export the pdf / html / xml documentation here.

03 Apr

Place to visit Mumbai: Alibaug 1-2 day tour weekend picnic

Think about one day tour and a lot of fun in Mumbai? Alibaug is a good choice.

It a normal budget, full day, full fun and great place.

Reach Gateway Of India. [20 Mins walk / 5 Mins (Rs. 15) Taxi from Chatrapati Shivaji Terminus / Victoria Terminus]

Gateway of India

Gateway of India

Book a PNP / Ajanta Jetty from booking stalls opposite to Gateway of India. [AC – 120, non-AC – 65]. [First ferry @ 8.30 AM – depends on tide conditions too]

Non-AC ferry, top seat is best way to travel as you can feel the breezing wind and cool waves. The birds following in the mid-sea to the ship is a different fun to capture them.

Sea Birds following Jetty

Sea Birds following Jetty

[ad#ad-2-300×250]

Next you will land at Mandwa port, Quick walk to the bus services provided by PNP / Ajanta to Alibaug. [It takes 1 Hr from Gateway of India to Mandwa and ~30 Mins from Mandwa to Alibaug.]

If you’re over cautious, book tickets for return journey and confirm day’s last jetty. PNP / Ajanta Jetty service offices are near by only.

The closest beach is Alibaug beach, walk-able distance from Alibaug bus stand.

At Alibaug beach you may have water bike / wave ride, or normal sea-routines.

If you’re a fort’oholic, you may choose to visit the Colaba Fort @ 100 Rs / person through a 10 minute boat ride. 1 hr. is sufficient to capture all major corners of the fort.

Colaba Fort, Alibaug

Colaba Fort, Alibaug

Kolaba Fort, Alibaug

Kolaba Fort, Alibaug

I don’t much recommend Alibaug to waste your precious time on.

Akshi and Negaon beaches are on the same route. Auto will charge 120 Rs for Negaon beach ~30 mins from Alibaug Station / beach. [Take mobile number from auto driver in case of no return transport availability.]

Negaon beach

and is really a recommended one. It has nice long beach with all those coconut trees greeting the waves to the shore.

If you’re a bunch of crazy guys looking for a place to freak-out, Negaon beach is for you. Football, cricket, Hourse riding or any beaching activity is absolute fun here. I don’t like lazy bums in the gang but they can rest under the shadows on misty sand. Gliding and water rides are of-course available.

Negaon Beach, Alibaug

Negaon Beach, Alibaug

Negaon Beach, Alibaug

Negaon Beach, Alibaug

Hotels / rooms are cheap; food is okay; though these shouldn’t impact as these are not major fun part.

You cannot cover everything in one day; nor have I visited all of the attractions there.

For 1 day tour, pack a bag and directly reach to Negaon beach.

Approx expenditure: <500 Rs / person.
Large

Other Routes:

By Road: One can reach Alibag via Pen (30 km away), which is on the Mumbai (78 km away) – Goa road.

From Mumbai, one can reach Alibag by traveling on the Mumbai-Goa highway (NH-17) till Wadkhal (Vadkhal) and taking the right fork from Wadkal – the left fork will lead you to Goa. The distance is approximately 108 km from Mumbai.

By Railways: The nearest rail railway station are Pen, India. Through Pen, India, it is connected to Khopoli.

Other beaches at Alibaug:

Varsoli Beach:Located about a mile from the main beach, home to a very large naval base.

Kihim-Navgaon Beach: Kihim is a secluded place at a distance of 12 km from Alibag. The Kihim beach is famous for dense cover of Coconut trees. It is also famous for woods which are home to rare butterflies, birds and flowers.

Awas: It is situated about 16 km away from Alibag.

Saswane: It is situated about 18 km away from Alibag.

Rewas: It is situated about 24 km away from Alibag.

Chaul Revdanda: It is situated about 17 km away from Alibag.

kaneshwar Mandir: It is situated about 17 km away from Alibag to karlekhind-chondi road.

Mandawa: It is situated about 20 km north of Alibag. The catamaran/ferry services are available from Mumbai to the Mandawa jetty. Many Bollywood celebrities own bungalows here.

Kashid Beach: It is 36 km away from Alibag, on the Alibag-Murud highway, also this beach is possibly one of the cleanest and most beautiful beaches in the region with almost ‘white’ sand.

-- Kedar Vaijanapurkar --