Import Mysql data (or a CSV file) into MongoDB

If you have Open source MySQL database. And want to move either all, or some of the data into MongoDB (noSQL Database). There may be any other direct/indirect way to do it,  but you can also achieve this thing from below method (Add on) :) 

Step 1. 

Export all of your MySQL data as a CSV file.

select columns INTO OUTFILE '/path/to/csv' 

   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

   LINES TERMINATED BY '\n' from table [where clause]

Step 2.

Import the CSV into MongoDB.

mongoimport -d dbname -c collname --type csv -f fields-sep-by-coma  --drop /path/to/csv 

if your csv file has a header row add --headerline option.

Suppose you have a big table having more than 100 columns then how can you display/show those columns as a comma separated. Here is the way- 

mysql>select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")) 

from INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME = 'your_table_name' AND 

TABLE_SCHEMA = 'your_database_name';

Secure authentication in Mysql (For DBA Administration purpose)

Dear Folks,

For secure authentication in mysql database server, "mysql_config_editor" tool ( available from mysql 5.6.6 version) enables you to store authentication information in encrypted format like below-

[devpcs-readonly]

user = dba

password = *****

host = 10.0.7.1

[devcpdb-readonly]

user = dba

password = *****

host = 10.0.2.1

[sitpcs-repl]

user = dba

password = *****

host = 172.31.2.14

[sitcpdb-repl]

user = dba

password = *****

host = 172.31.2.1

Above details can be seen by the below command -

mysql_config_editor print --all

Now we will see a demo to save a new authentication connection-

shell> mysql_config_editor set --login-path=<name_to_which_you_want_to_connect> —host=<IP_of_remote/local_server> —user=username —password

Enter Password:xxxxxxxxx

E.g
shell>mysql_config_editor --login-path='uat-pcs-readonly' --host='10.22.0.1' --user='readonly' --password
Enter Password: xxxxxx

Trying to login now-

shell>mysql --login-path=uat-pcs-readonly 
mysql>

You can also use --prompt option for avoiding any discrepancy among the multiple mysql console.

Example-

shell>mysql --login-path=uat-pcs-readonly --prompt="\u@\h [\d@\p] mysql> " 
readonly@172.22.0.1 [(none)@3306] mysql>


In this way, we do not need to write or provide database password again and again.

Obj-
Do not save mysql password in plain text flat file (csv/txt/.sh/etc)

Pros-
For DBA Administration related automate scripts, this feature can be useful in many cases.

Cons-
Anybody can login into mysql database by issuing --login-path option. So do not provide "root" credentials anywhere. Restrict user privileges.

Reference-
http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html