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

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'

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.

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