The CSV file format is extremely useful as well as common for dumping, parsing, and loading data. Being able to export the data of a MySQL database into a .csv format file is a handy ability to have. For one-off uses of exports in this format the MySQL Query Browser provides an option to export a resultset to CSV. But if you want to generate the dump programmatically (or have greater output flexibility) you’ll need to use command line calls (or an API, like mysqldb). In a recent case I did not need the actual SQL for my database tables, just their data. The command I tried first was:
1 2 | mysqldump -u USER -pPASSWORD --fields-terminated-by=, \ --fields-enclosed-by=\" --tab=DUMPDIR DATABASE |
This dumps the data and the create table statements for tables in DATABASE to DUMPDIR. The “–fieldsX” options here dump the data in CSV format. If you just want the schema you can add “–no-data”, but I haven’t found a way to just dump the data and not the schema as well. Anyway, the problem I ran into was that I first tried DUMPDIR as my home directory. This threw an error:
1 | mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' |
A post on the MySQL forums clued me in: It’s because the mysql user doesn’t have the right permissions to write to that dir (my home dir). One solution would be making a new dir to hold the dump, chowning it to mysql or allowing world write (chmod a+w DIR). Or you can write it to /tmp, which is always world-writable. I’d recommend creating a directory for the dump (in /tmp or elsewhere) in any case since the files all get dropped into the DB specified at the same level. So if you have lots of tables and already have files in /tmp or wherever you dump to it could get confusing. One other caution: Some distributions set a size limit on /tmp (since it’s world-writable a rogue process could otherwise fill your filesystem). So be careful if you are dumping a lot of data.
The final version:
1 2 3 4 5 6 7 | mkdir /tmp/db_dump # Free tip: Press alt + . to insert the final arg from the previous line! chmod 777 /tmp/db_dump mysqldump -u USER -pPASSWORD --fields-terminated-by=, \ --fields-enclosed-by=\" --tab=DUMPDIR DATABASE rm /tmp/db_dump/*.sql rename 's/\.txt$/\.csv/' /tmp/db_dump/*.txt |
This produces a .txt file per table in the specified DB containing all the data and a .sql file per table containing the drop/create table statements. It then drops the unneeded .sql files and corrects the extension on the data files.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
