CODEWITHSUNDEEP
mysqlcsv
Andrés Hazard
Andrés Hazard

Reputation: 43

How to export a MySQL database to CSV

What is the best way to export a MySQL database to a CSV file without including indexes, table structures etc? I just need to get all the data, I have a lot tables so I don't want to do it one by one. I'm using 0xdbe and Workbench running on Linux. Thanks!

Upvotes: 2

Views: 12459

Answers (3)

the
the

Reputation: 21939

This worked well for me:

 mysqldump DBNAME TABLENAME  --fields-terminated-by ',' \
 --fields-enclosed-by '"' --fields-escaped-by '\' \
 --no-create-info --tab /var/lib/mysql-files/

I'm dumping to /var/lib/mysql-files/ to avoid this error:

mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

Upvotes: 1

miken32
miken32

Reputation: 42743

mysqldump has a mode to dump tab-separated files, one per table.

mysqldump -u <username> -p<password> -T <output_directory> --no-create-info <database_name>

With a bit of tweaking this can be make to look like a CSV file.

mysqldump -u <username> -p<password> -T <output_directory> --fields-terminated-by ',' --fields-enclosed-by '"' --fields-escaped-by '\' --no-create-info <database_name>

Note that the file is written by the database, so whatever user your database is running as needs to have write access to the output directory!

Upvotes: 7

Bajji
Bajji

Reputation: 2393

mysqldump might be useful in this case. Even though it's not the csv output, it has all the indices and table structures including the data.

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Upvotes: -4

Related Questions