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!
-
2How do you propose keeping things like indices and table structure in a CSV file though?miken32– miken322015-10-29 20:56:49 +00:00Commented Oct 29, 2015 at 20:56
-
That's the thing I don't want the indices and table structure. I just want the data. Does that make sense ?Andrés Hazard– Andrés Hazard2015-10-30 03:04:07 +00:00Commented Oct 30, 2015 at 3:04
-
Understood. "Without giving up" something means you want to keep it, this was what caused my confusion.miken32– miken322015-10-30 03:06:06 +00:00Commented Oct 30, 2015 at 3:06
-
Sorry for that. I changed itAndrés Hazard– Andrés Hazard2015-10-30 03:08:18 +00:00Commented Oct 30, 2015 at 3:08
Add a comment
|
3 Answers
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!
1 Comment
Andrés Hazard
It worked ok. I got the files on .txt and .sql but I can change them to .csv Thanks
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'
Comments
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
