Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.3Mb
PDF (A4) - 38.4Mb
PDF (RPM) - 37.7Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 9.0Mb
Man Pages (TGZ) - 197.4Kb
Man Pages (Zip) - 305.9Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

B.5.3.5 Where MySQL Stores Temporary Files

On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.

On Windows, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp\.

If the file system containing your temporary file directory is too small, you can use the mysqld --tmpdir option to specify a directory in a file system where you have enough space. On replication slaves, you can use --slave-load-tmpdir to specify a separate directory for holding temporary files when replicating LOAD DATA INFILE statements.

The --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (:) on Unix and semicolon characters (;) on Windows.


To spread the load effectively, these paths should be located on different physical disks, not different partitions of the same disk.

If the MySQL server is acting as a replication slave, you should be sure to set --slave-load-tmpdir not to point to a directory that is on a memory-based file system or to a directory that is cleared when the server host restarts. A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the slave temporary file directory are lost when the server restarts, replication fails.

MySQL arranges that temporary files are removed if mysqld is terminated. On platforms that support it (such as Unix), this is done by unlinking the file after opening it. The disadvantage of this is that the name does not appear in directory listings and you do not see a big temporary file that fills up the file system in which the temporary file directory is located. (In such cases, lsof +L1 may be helpful in identifying large files associated with mysqld.)

When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk space required is determined by the following expression:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

The row pointer size is usually four bytes, but may grow in the future for really big tables.

For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form SQL_*.

In most cases, ALTER TABLE creates a temporary copy of the original table in the same directory as the original table.

Where InnoDB Stores Temporary Files

As of MySQL 5.7.1, non-compressed InnoDB temporary tables are stored in a temporary tablespace file named ibtmp1, which is located in the MySQL data directory (datadir) by default. The innodb_temp_data_file_path option can be used at startup to specify a different file name and location. Compressed InnoDB temporary tables are stored in their own independent tablespace files (.ibd files) in the path specified by the TMPDIR environment variable.

If an ALTER TABLE operation on an InnoDB table uses the ALGORITHM=COPY technique, InnoDB creates a temporary copy of the table in the same directory as the original table. Temporary table file names begin with an #sql- prefix and only appear briefly during the ALTER TABLE operation.

If an ALTER TABLE operation rebuilds an InnoDB table using the ALGORITHM=INPLACE technique (online DDL), InnoDB creates an intermediate copy of the table the same directory as the original table. Intermediate table file names begin with an #sql-ib prefix and only appear briefly during the ALTER TABLE operation.

ALTER TABLE operations that rebuild an InnoDB table using the ALGORITHM=INPLACE technique (online DDL) also create temporary sort files in the MySQL temporary directory ($TMPDIR on Unix, %TEMP% on Windows, or the directory specified by the --tmpdir configuration option). If the temporary directory is not large enough to hold such files, you may need to reconfigure tmpdir. Alternatively, you can define a separate temporary directory for InnoDB online ALTER TABLE operations using the innodb_tmpdir configuration option. This option was introduced in MySQL 5.7.11 to help avoid temporary directory overflows that could occur as a result of large temporary sort files created during online ALTER TABLE operations. innodb_tmpdir can be configured dynamically using a SET GLOBAL or SET SESSION statement.

The innodb_tmpdir option is not applicable to intermediate table files, which are always created in the same directory as the original table.

In replication environments, only consider replicating an innodb_tmpdir setting if all servers have the same operating system environment. Otherwise, replicating an innodb_tmpdir setting could result in a replication failure when running online ALTER TABLE operations. If server operating environments differ, it is recommended that you configure innodb_tmpdir on each server individually.

For more information about online DDL, Section 14.13, “InnoDB and Online DDL”.

User Comments
Sign Up Login You must be logged in to post a comment.