{"id":1889,"date":"2022-06-29T11:49:53","date_gmt":"2022-06-29T11:49:53","guid":{"rendered":"https:\/\/www.checkmateq.com\/blog\/?p=1889"},"modified":"2023-08-06T15:13:46","modified_gmt":"2023-08-06T15:13:46","slug":"mysql-tables-to-mongodb","status":"publish","type":"post","link":"https:\/\/www.checkmateq.com\/blog\/mysql-tables-to-mongodb","title":{"rendered":"How to Export MySQL Tables to MongoDB?"},"content":{"rendered":"

MySQL<\/strong><\/a> is one of the most used relational database managements (RDMS) based on SQL, it stores data in rows and columns each row will have unique data for the defined columns, and every RDMS follows a predefined schema to store data, use a relational database management system when you want your data to be stored in a particular predefined schema.<\/p>\n

When to use MongoDB?<\/h3>\n

MongoDB<\/a> is a non-relational document database used to store unstructured data, it has flexible data model as it allows you to store data in Json format, it uses collections and documents unlike tables and rows in relational database, documents consist of key-value pairs which are mongo dB’s basic unit of data, fields in a MongoDB document can be indexed with primary and secondary index’s.<\/p>\n

while MongoDB is more flexible and ensures data availability ,MySQL ensures greater reliability of transactions ,so if you have a application where there is a need to perform complex queries and you do not intend to add more features\u00a0 it’s better to stay on MySQL, on the other side ,if you are constantly adding new features and functionalities to your application it’s better to migrate to MongoDB.<\/p>\n

In this blog we will see a very simple way of migration where you export tables from mysql-workbench and mysql command line to csv files, and importing a csv file to mongodb.<\/p>\n

MySQL to MongoDB terminology mapping chart<\/strong><\/p>\n

\"\"<\/p>\n

Exporting tables using MySQL workbench to\u00a0 a CSV file<\/strong><\/h3>\n

On your MySQL workbench click on “Schemas” under navigator.<\/p>\n

\"\"<\/p>\n

Select the database from which you want to export table and right click on the table name then click on “Table Data Export Wizard” as shown in below snap shot, I am exporting table people from database “abc” .<\/p>\n

\"\"<\/p>\n

data of table “people” from abc database.<\/p>\n

\"\"<\/p>\n

Now select the columns that you want to export and click on next.<\/p>\n

\"\"<\/p>\n

give file path where you want to store data and select the file type then click on “Next>”, as you can see ,\u00a0 I am saving my file in downloads.<\/p>\n

\"\"<\/p>\n

click on Next on “Export Data” tab and click on ‘Finish’ on Export Results tab.<\/p>\n

\"\"<\/p>\n

Now go check your csv file in file path you gave, below is the snapshot of the file generated.<\/p>\n

\"\"<\/p>\n

Exporting table from MySQL Command line (Server)<\/h3>\n

Below is the snapshot of a table data “Customers” from a database named dbone<\/strong><\/p>\n

\"\"<\/p>\n

To export your table from command line you need to add below line\u00a0 to mysql-server\u00a0 configuration file under [mysqld] and save it.<\/p>\n

secure-file-priv = \"\"<\/pre>\n
This will enable users to export or import data from database server, restart your mysql server after saving the file.<\/p>\n
\"\"<\/p>\n
Command to export\u00a0 a table to a csv file<\/strong><\/h3>\n
Login to your mysql shell and use below command to export your table.<\/p>\n
TABLE Customers<\/strong> INTO OUTFILE 'Customers.txt<\/strong>' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\\n';<\/pre>\n
\"\"<\/p>\n
here Customers<\/strong> is the table name that I want to export and Customers.txt<\/strong> if the file name to which I am exporting the table file will be created at \u00a0\/var\/lib\/mysql\/database_name (may vary based on OS), you can also give a specific path instead of file name , below is snapshot of\u00a0 \u00a0CSV file(exported table data) column names are not exported here.<\/p>\n
\"\"<\/p>\n
Importing a CSV file to MongoDB database using mongoimport\u00a0 command<\/h3>\n
you can use mongoimport command to import CSV files it’s fast and multi threaded<\/p>\n
Example-1: Importing a CSV file which does not has field names<\/strong><\/p>\n
Now lets see how to use mongoimport command to import a CSV file which does not have header (column\/field names), below is the snapshot of data in a csv file named Customers.<\/p>\n
\"\"<\/p>\n
As you can see in the below image database “databaseOne” only has one table.<\/p>\n
\"\"<\/p>\n
execute below command on your terminal to import data from a csv file (not on mongo shell).<\/p>\n
mongoimport --db databaseOne<\/strong> --collection=Customers<\/strong> --file=Customers.csv<\/strong> --type=csv --fields=\"ID\",\"Name\",\"Occupation\",\"Age\"<\/pre>\n
Here databaseOne<\/strong> is database that I am importing data to ,Customers\u00a0<\/strong>is the new collection created to store imported data, Customests.csv<\/strong> is the CSV file name that the data is imported from and use option fields<\/strong> to give field names.<\/p>\n
\"\"<\/p>\n
Example-2: Importing a CSV file which has field names<\/strong><\/p>\n
Observe the below given snapshot of\u00a0 abc.csv<\/strong> file it has field names, use option headerline\u00a0<\/strong>along with mongoimport command, option headerline notifies mongoimport that not to import first line as document as it contains field names.<\/p>\n
\"\"<\/p>\n
Command to import data into mongodb from csv file with header\u00a0<\/strong><\/p>\n
mongoimport --db databaseOne<\/strong> --collection=Persons<\/strong> --file=abc.csv<\/strong> --type=csv --headerline<\/pre>\n
databaseOne<\/strong> is database that I am importing data to ,Persons\u00a0<\/strong>is the new collection created to store imported data, abc.csv <\/strong>is the CSV file name that the data is imported from.<\/p>\n
\"\"<\/p>\n
Snapshot of collection created from imported file<\/p>\n
\"\"<\/p>\n
 <\/p>\n
Please contact<\/a> with our technical consultants if you want to develop any cloud migration<\/a> strategy. Our cloud engineers can provide you appropriate infrastructure migration plan. You can hire Checkmate Global Technologies cloud engineer today.<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL is one of the most used relational database managements (RDMS) based on SQL, it stores data in rows and columns each row will have unique data for the defined columns, and every RDMS follows a predefined schema to store data, use a relational database management system when you want your data to be stored … Continue reading “How to Export MySQL Tables to MongoDB?”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":2067,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[3,2,11,63,23,16,24,8],"_links":{"self":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/1889"}],"collection":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/comments?post=1889"}],"version-history":[{"count":27,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/1889\/revisions"}],"predecessor-version":[{"id":4286,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/1889\/revisions\/4286"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media\/2067"}],"wp:attachment":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media?parent=1889"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/categories?post=1889"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/tags?post=1889"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}