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' ;