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.
Follow these steps to import and export bulk data:
$ mysqldump -u admin -p mytestdb > db_backup.sql
$ mysqldump -u admin -p mytestdb table1 table2 > table_backup.sql
gzip
:$ mysqldump -u admin -p mytestdb | gzip > db_backup.sql.gz
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 ‘ ’;
$ nano query.sql select * from articles;
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.
$ mysqladmin -u admin -p create mytestdb2
$ mysql -u admin -p mytestdb2 < db_backup.sql
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 IGNORE 1 ROWS;