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

backupMySQLmysqldump

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

Best Answer

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'

Related Solutions

Mysql – Not enough space for MySQL optimize; do new rows overwrite existing data

There is a user comment on the OPTIMIZE TABLE documentation page that had the same situation as you. His solution was to copy the table somewhere else, optimize, then copy back. This required offline time.

Unfortunately, my solution would also require offline time as well, for 70GB worth of data. Something like this:

mysqldump table from another machine with enough space
DROP TABLE foo
Load table from the mysqldump file: mysql -hXXX.XX.XX.XXX -u... -p... < foo.sql

The downtime will be from the DROP TABLE step until the load table from a remote host of 70GB of data can be completed.

One potential bright spot is that new INSERTS will re-use the space created from DELETES:

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

But this is what causes fragmentation to begin with. So you will need to make sure your tablespace is optimized frequently to prevent this in the future.

I just want to throw out there that to run an optimize query, you're always going to need at least the database size + the size of the table you're optimizing available on disk.

As an example, this means for a database size of 30GB and the table your optimizing is 28GB, you will need at minimum a harddrive that can fit 58GB of data space. This is because MySQL rewrites a new temporary file and then replaces the old one.

I say at least, because there's always other things going on. You'll need space for other programs, MySQL might be creating on-disk temporary tables during the operation, etc.

MySQL – SELECT INTO OUTFILE with UNION

Give this a Try

SELECT * FROM
((SELECT email FROM table WHERE createdate >= date_sub(now(), interval 1 year))
UNION DISTINCT
(SELECT email FROM table WHERE createdate < date_sub(now(), interval 1 year) AND email not like '%@gmail.com')) A
INTO OUTFILE '/tmp/emailDump.txt' FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

Best Answer

Related Solutions

Mysql – Not enough space for MySQL optimize; do new rows overwrite existing data

MySQL – SELECT INTO OUTFILE with UNION

Related Question