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 'rn' ;
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
> mysql -e "SELECT * FROM foo ORDER BY bar" > '/path/to/file'
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 -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.
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