Importing and exporting bulk data

In this recipe, we will learn how to import and export bulk data with MySQL. Many times it happens that we receive data in CSV or XML format and we need to add this data to the database server for further processing. You can always use tools such as MySQL workbench and phpMyAdmin, but MySQL provides command-line tools for the bulk processing of data that are more efficient and flexible.

How to do it…

Follow these steps to import and export bulk data:

1. To export a database from the MySQL server, use the following command:

   $ mysqldump -u admin -p mytestdb > db_backup.sql
   

2. To export specific tables from a database, use the following command:

   $ mysqldump -u admin -p mytestdb table1 table2 > table_backup.sql
   

3. To compress exported data, use gzip:

   $ mysqldump -u admin -p mytestdb | gzip > db_backup.sql.gz
   

4. To export selective data to the CSV format, use the following query. Note that this will create articles.csv on the same server as MySQL and not your local server:

   SELECT id, title, contents FROM articles
   INTO OUTFILE ‘/tmp/articles.csv’
   FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’
   LINES TERMINATED BY ‘\n’;

5. To fetch data on your local system, you can use the MySQL client as follows:
   • Write your query in a file:

     $ nano query.sql
     select * from articles;

   • Now pass this query to the mysql client and collect the output in CSV:

     $ mysql -h 192.168.2.100 -u admin -p myblog < query.sql > output.csv
     

   The resulting file will contain tab separated values.

6. To import an SQL file to a MySQL database, we need to first create a database:

   $ mysqladmin -u admin -p create mytestdb2
   

7. Once the database is created, import data with the following command:

   $ mysql -u admin -p mytestdb2 < db_backup.sql
   

8. To import a CSV file in a MySQL table, you can use the Load Data query. The following is the sample CSV file:
   

   Now use the following query from the MySQL console to import data from CSV:

   LOAD DATA INFILE ‘c:/tmp/articles.csv’
   INTO TABLE articles
   FIELDS TERMINATED BY ‘,’  ENCLOSED BY ‘”’
   LINES TERMINATED BY \n IGNORE 1 ROWS;

See also

• MySQL select-into syntax at https://dev.mysql.com/doc/refman/5.6/en/select-into.html
• MySQL load data infile syntax at https://dev.mysql.com/doc/refman/5.6/en/load-data.html
• Importing from and exporting to XML files at https://dev.mysql.com/doc/refman/5.6/en/load-xml.html