今天一個同事反饋往一個MySQL資料庫導入數據時，報“ERROR 1 (HY000): Can't create/write to file '/tmp/MLjnvU95' (Errcode: 13 - Permission denied)”這樣的錯誤，如下所示： uery OK, 0 rows a... ...
今天一個同事反饋往一個MySQL資料庫導入數據時，報“ERROR 1 (HY000): Can't create/write to file '/tmp/MLjnvU95' (Errcode: 13 - Permission denied)”這樣的錯誤，如下所示：
uery OK, 0 rows affected (0.03 sec)
ERROR 1 (HY000): Can't create/write to file '/tmp/MLjnvU95' (Errcode: 13 - Permission denied)
ERROR 1 (HY000): Can't create/write to file '/tmp/MLPzmIbJ' (Errcode: 13 - Permission denied)
ERROR 1 (HY000): Can't create/write to file '/tmp/MLdpJwKm' (Errcode: 13 - Permission denied)
ERROR 1 (HY000): Can't create/write to file '/tmp/MLB7FTT0' (Errcode: 13 - Permission denied)
ERROR 1 (HY000): Can't create/write to file '/tmp/ML1wcUAF' (Errcode: 13 - Permission denied)
ERROR 1 (HY000): Can't create/write to file '/tmp/MLYjOzPk' (Errcode: 13 - Permission denied)
ERROR 1 (HY000): Can't create/write to file '/tmp/ML7gi9z0' (Errcode: 13 - Permission denied)
ERROR 1 (HY000): Can't create/write to file '/tmp/ML6nezQG' (Errcode: 13 - Permission denied)
個人使用source script.sql導入數據時，也是遇到這個錯誤，如上截圖所示。從這個錯誤提示，可以明顯看出是MySQL沒有許可權對/tmp進行操作，
查看系統變數tmpdir,如下所示，tmpdir變數指定路徑為/tmp
mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |+---------------+-------+
| tmpdir | /tmp |
+---------------+-------+
1 row in set (0.00 sec)
[root@mylx01 ~]# ls -lh /tmp
total 20M
-rw-rw-r--. 1 root root 20M Nov 7 12:04 cccc_user.sql-rw-r--r--. 1 root root 57 Nov 7 02:00 percona-version-check-rw-------. 1 root root 2.0K Nov 2 21:38 tmpP4BN3H
-rw-------. 1 root root 2.0K Oct 31 21:40 tmpusdOhs
-rw-------. 1 root root 2.0K Oct 30 21:36 tmp_WBZNO
-rw-------. 1 root root 2.1K Oct 29 21:38 tmpXcXptG
-rw-------. 1 root root 2.0K Nov 5 21:40 tmpyqSR0U
-rw-------. 1 root root 2.1K Oct 28 21:42 tmpZjNjtu
我們可以修改/tmp目錄的許可權； 或者修改資料庫的臨時目錄來解決這個問題：
1：在配置文件my.cnf中修改資料庫的臨時目錄，然後需要重啟MySQL資料庫
tmpdir = /var/lib/mysql
2：修改/tmp目錄許可權
chmod 1777 /tmp
或
chmod 777 /tmp
那麼MySQL使用tmpdir來做什麼呢？tmpdir是MySQL存臨時文件的目錄的路徑名。如果未設置變數tmpdir，MySQL將使用系統的預設值，通常為/tmp、/var/tmp或/usr/tmp。更多詳細信息，可以參考官方文檔Where MySQL Stores Temporary Files的詳細介紹：
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_*.
DDL operations that rebuild the table and are not performed online using the ALGORITHM=INPLACE technique create a temporary copy of the original table in the same directory as the original table.
Online DDL operations may use temporary log files for recording concurrent DML, temporary sort files when creating an index, and temporary intermediate tables files when rebuilding the table. For more information, see Section 15.12.3, “Online DDL Space Requirements”.
InnoDB user-created temporary tables and on-disk internal temporary tables are created in a temporary tablespace file named ibtmp1 in the MySQL data directory. For more information, see Section 15.6.3.5, “Temporary Tablespaces”.
MySQL會以隱含方式創建所有的臨時文件。這樣，就能確保中止mysqld時會刪除所有臨時文件。使用隱含文件的缺點在於，在臨時文件目錄所在的位置中，看不到占用了文件系統的大臨時文件。
進行排序時（ORDER BY或GROUP BY），MySQL通常會使用1個或多個臨時文件。所需的最大磁碟空間由下述表達式決定：
(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2
“row pointer”（行指針）的大小通常是4位元組，但在以後，對於大的表，該值可能會增加。
對於某些SELECT查詢，MySQL還會創建臨時SQL表。它們不是隱含表，並具有SQL_*形式的名稱。
ALTER TABLE會在與原始表目錄相同的目錄下創建臨時表。
如果記憶體臨時表超出了限制，MySQL就會自動地把它轉化為基於磁碟的MyISAM表，存儲在指定的tmpdir目錄下。
參考資料：
https://dev.mysql.com/doc/refman/8.0/en/temporary-files.html
