Monthly Archives: October 2011

MySQL import / export fun

Exporting Data

If you run the command:

SELECT * FROM TestTable INTO OUTFILE 'outfile.txt

and receive the rather cryptic error message ‘PERMISSION DENIED’..

Keep in mind, you need to specify an explicit path to export the data to, e.g. ‘/tmp/outfile.txt’

SELECT INTO OUTFILE also requires the permission ‘OUTFILE’ to be granted to the currently executing user.

Importing Data
Similarly, when importing data using LOAD DATA INFILE (even with an explicit path) you get the error message

‘ERROR 29 (HY000): File ‘/tmp/infile.txt’ not found (Errcode: 13)

MySQL server may be denied access to most of the filesystem depending on your OS setup (including /tmp), so try using

LOAD DATA LOCAL INFILE '/tmp/infile.txt' ...

which pipes the result through to the MySQL client which has all the permissions that the executing user has.

#mysqlfun