Is there a way to covert or export an entire MySQL database to valid CSV files?

Question

I am working with a large database 1.5 gig in size and hundreds of tables / fields. I need to convert all tables into CSV files. PhpMyAdmin does not do this easily / times out.

I would rather use a shell / mysql command or a script to get the data out and into CSV.

Note:

I am looking to export ALL tables of the database - in 1 shot. I can not produce an export command for every single table individually.

Answer 1

You can use mysqldump:

The mysqldump command can also generate output in CSV, other delimited text, or XML format.

In particular, look at the following arguments:

• --tab=path
• --fields-[optionally-]enclosed-by
• --fields-escaped-by
• --fields-terminated-by
• --lines-terminated-by
• --no-create-info

Answer 2

You will need to do this table by table, see below.

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products

Note that the directory must be writable by the MySQL database server. If it's not, you'll get an error message like this:

#1 - Can't create/write to file '/tmp/products.csv' (Errcode: 13)

Also note that it will not overwrite the file if it already exists, instead showing this error message:

#1086 - File '/tmp/products.csv' already exists

Source: http://www.electrictoolbox.com/mysql-export-data-csv/

Answer 3

Information about the software : sql2csv

Download link exe : http://www.convert-in.com/demos/sql2csv.exe

This is best option I found around for windows. With the software we can connect to local and remote DB server and select schema. In one shot we can extract all tables data into Valid CSV files.

Features :