how to use a different tmp dir for INTO OUTFILE select with MySQL? I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough). 

The problem is, I am getting an error code 28. I am pretty sure it is because it is running out of disk space on the default /tmp location. I have enough space on the disk elsewhere, if I could just specify at runtime what location to use for tmp. 

I found out I can change the tmpdir by modifying my.cnf, but I do not have sufficient server privileges to modify it or restart mysqld. 

What is the solution here?  

EDIT: a few answers have suggested using mysql -e. I may have to go this route and post-process my file. The reason I wanted to use SELECT ... INTO OUTFILE is because I want to 

fields   terminated by '\t'
         escaped by '\\'
lines    terminated by '\r\n' ;
Assuming you have the FILE privilege, you should be able to specify a location other than the /tmp location. For example:

SELECT .. INTO OUTFILE '/home/foo/myfile.csv'


The other option, if you have access to the command line, is to use the -e switch:

&gt; mysql -e "SELECT * FROM foo ORDER BY bar" &gt; '/path/to/file'