Friday, June 29, 2007

Exporting a MySQL DB to .csv

I thought the operation would be simple. Short, no casualties, 100% chance of success. No such luck.

I needed to export the data of a MySQL database into a .csv format file. I did not want it in a huge XML file, and I did not need the actual .sql statements. A common need, one would think. The command to use is:
mysqldump -u root --fields-terminated-by=, --tab=/tmp --tables DATABASE

Now, this might seem simple. But what I tried first was having the " > dump.sql" file at the end, and had the path after "--tab" as my home directory. Now the first choice was fine, as you can separate where you send the data files versus where you send the table syntax. But, you will get an error:
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.
a simple chmod should fix the problem

So that led me to make a dir and chown it to the mysql user, which worked. However, if you write it to /tmp, you do not need to chown. So this version writes all the files to one dir without permission woes:
mysqldump -u root --fields-terminated-by=, --tab=/tmp --tables DATABASE


Post a Comment

Subscribe to Post Comments [Atom]

<< Home