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...