Exploring the location of temporary MySQL database files

MySQL uses the value of the environmental variable TMPDIR as the path name of the directory for saving the temporary file. If TMPDIR is not set, MySQL uses the default value of the system, usually/tmp,/var/tmp, or/usr/tmp. If the file system that contains the temporary file directory is too small, you can use the "-tmpdir" option for mysqld to specify a directory in the file system with sufficient space.

In MySQL 5.1, the "-tmpdir" option can be set to a list of several paths and used cyclically. On Unix platforms, the path is separated by the colon (:). On Windows, NetWare, and OS/2 platforms, the path is separated by the semicolon. Note: To effectively distribute loads, these paths should be located on different physical disks, rather than in different partitions of the same disk.

If the MySQL server is being used as a replication slave server, you should not set "-- tmpdir" to the directory of the memory-based file system, or the directory that will be cleared when the server host restarts. For replication from the server, some temporary files need to be retained when the machine restarts, so that you can copy the temporary table or perform the load data infile operation. If files in the temporary file directory are lost when the server is restarted, the replication will fail.

MySQL creates all temporary files implicitly. This ensures that all temporary files are deleted when mysqld is aborted. The disadvantage of using an implicit file is that you cannot see a large temporary file that occupies the file system in the location of the temporary file directory.

When sorting (order by or group by), MySQL usually uses one or more temporary files. The maximum disk space required is determined by the following expression:

 
 

  
  
(LengthOfWhatIsSorted + sizeof (row pointer ))
  
  
 
  
  
* NumberOfMatchedRows 
  
  
 
  
  
* 2
  
  
 
  
  
The size of "row pointer" (row pointer) is usually 4 bytes.
 
 

However, in the future, this value may increase for large tables.

For some SELECT queries, MySQL also creates temporary SQL tables. They are not implicit tables and have names in the SQL _ * format.

Alter table creates a temporary TABLE in the same directory as the original TABLE directory.

1. Implementation of Like indexes in MySQL full-text search
2. MySQL Query Optimization Using scheduling and locking
3. Analysis of basic MySQL Scheduling Policies
4. Adding "light" Drizzle + PHP to MySQL
5. Learn to set five categories of MySQL Parameters