how to use a different tmp dir for INTO OUTFILE select with MySQL?

Question

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

• Jack Douglas: "I have enough space on the disk elsewhere" do you mean you have enough space on another disk?
• Derek Downey: Or a different partition, I'm assuming.
• pocketfullofcheese: yes, on a different partition

Answer 1

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'

• pocketfullofcheese: I am specifying another location, but mysql seems to do the sorting or otherwise using a tmp file before writing it to where I specify
• pocketfullofcheese: the second option might get around that. I will give it a try!
• pocketfullofcheese: wait, the second option does not allow me to format the output. I wanted to use terminated by, escaped by, and lines terminated by

Answer 2

The problem is that the servers pocket is full of cheese, but there's no way of specifying an alternative pocket to put the cheese in due to the cheese creator (the MySQL server process) not having permission to place cheese (query output) in any other pocket (directory location) due to privs.

If your host can't give you an alternative mount point to dump the file to (with appropriate privs for the MySQL server to write to), a workaround is to connect to the server from a MySQL prompt (making sure you're on the same subnet or the same server, as you don't want to transfer 40Gb of data over the internet) and use mysql -e:

mysql -h your.host.name.com -u youruser --password=yourpass -e 'select stuff from mytable order by stuff' > /wherever/you/want/mylocaloutput.txt

That'll create a local file for you in the location of your choosing.

• swasheck: that's deep. mind = blown.
• pocketfullofcheese: haha. upvoted for creativity. However, my problem is that SELECT ... INTO OUTFILE writes to /tmp regardless of where you specify the output. mysql -e does not let me format the output the way INTO OUTFILE does (specify delimiter, escape char, line ending, etc)
• Philᵀᴹ: Yeah, it does. You can use string concatenation (CONCAT and CONCAT_WS()) in your SELECT clause to achieve the same thing. Not sure how to escape selected column values though
• RolandoMySQLDBA: Please make sure there are no rats around (bad queries) during the process. Call the exterminator (DBA) beforehand. +1 !!!

Answer 3

Actually, you can use mysqldump to export the data using the same semantics as SELECT ... INTO OUTFILE

# mysqldump --help | grep terminated
 --fields-terminated-by=name
 Fields in the output file are terminated by the given
 string.
 --fields-enclosed-by=name
 Fields in the output file are enclosed by the given
 character.
 --fields-optionally-enclosed-by=name
 Fields in the output file are optionally enclosed by the
 given character.
 --fields-escaped-by=name
 Fields in the output file are escaped by the given
 character.
 --lines-terminated-by=name
 Lines in the output file are terminated by the given
 string.

For example, to dump a table mydb.mytable to a CSV file:

mysqldump --fields-terminated-by="\\t" --fields-enclosed-by="'" --lines-terminated-by="\\r\\n" mydb mytable > mydb_mytable.csv

• Derek Downey: I don't think the formatting is what the OP's concern is (he wants it sorted a certain way).
• RolandoMySQLDBA: @DTest I was just mentioning mysqldump having the same capacity to do SELECT INTO OUTFILE. That was all.
• Derek Downey: I'm +1'ing this, because it might end up being the only way to eventually get the file sorted (by doing the sort after the dump, instead of having mysql handle it)
• pocketfullofcheese: Can you explain how the query below is related to the mysqldump part? I see only a SELECT ... INTO OUTFILE statement below and the --tab part of the mysqldump command above; where is the connection?