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:

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

StackExchange Database Administrators Q#23279, answer score: 7