Importing and exporting bulk data in Ubuntu server

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:

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

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

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

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

To compress exported data, use gzip:

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

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’;

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.

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