Importing and exporting bulk data_Linux：Powerful Server Administration-QQ阅读男生都市网

上QQ阅读APP看书，第一时间看更新

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:

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:
```
$ mysqladmin -u admin -p create mytestdb2
```
Once the database is created, import data with the following command:
```
$ mysql -u admin -p mytestdb2 < db_backup.sql
```
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;
```

Importing and exporting bulk data

How to do it…

See also