Shulou(Shulou.com)05/31 Report--

This article mainly explains "where the temporary files in the MySQL database are stored". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "where the temporary files in the MySQL database are stored".

MySQL uses the value of the environment variable TMPDIR as the pathname of the directory where the temporary files are saved. If TMPDIR,MySQL is not set, the default value of the system is used, usually / tmp, / var/tmp, or / usr/tmp. If the file system that contains temporary file directories is too small, use the "- tmpdir" option for d to specify 1 directory within the file system that has enough space.

In MySQL 5.1, the "- tmpdir" option can be set to a list of several paths, which can be used in a circular manner. On Unix platforms, paths are separated by the colon character ":", and on Windows, NetWare, and OS/2 platforms, paths are separated by semicolon characters ";". Note that in order to effectively distribute the load, these paths should be on different physical disks, not on different partitions of the same disk.

If the MySQL server is being used as a replication slave server, the "--tmpdir" should not be set to point to the directory of the memory-based file system, or the directory that will be emptied when the server host restarts. For the replication slave server, you need to keep some temporary files when the machine is rebooted so that you can copy temporary tables or perform LOADDATAINFILE operations. If the files in the temporary files directory are lost during the server restart, 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 implicit files is that large temporary files that occupy the file system cannot be seen in the same location as 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:

(length of what is sorted + sizeof (rowpointer))

* number of matched rows

* 2

The size of the "row pointer" (row pointer) is usually 4 bytes

But later, for large tables, this value may increase.

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

ALTERTABLE creates temporary tables in the same directory as the original table directory.

Thank you for your reading, the above is the content of "where the temporary files of the MySQL database are stored". After the study of this article, I believe you have a deeper understanding of where the temporary files of the MySQL database are stored, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.