# 執(zhí)行 optimize  命令時(shí)會(huì)發(fā)現(xiàn)報(bào)錯(cuò)
mysql>  optimize local table t_word;
+-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| abc.t_word | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| abc.t_word | optimize | error    | Temporary file write failure.                                     |
| abc.t_word | optimize | status   | Operation failed                                                  |
+-------------------------+----------+----------+-------------------------------------------------------------------+
3 rows in set, 1 warning (54 min 27.89 sec)

# 進(jìn)入MySQL
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

# 回到Linux ，可見(jiàn) /tmp 目錄實(shí)際上是在系統(tǒng)盤(vda1)上的。（這個(gè)是默認(rèn)位置）
> df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G  6.0G   13G  33% /

#1.查看 tmpdir
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

#2.創(chuàng)建 tmpdir目錄

mkdir -p /data/tmpdir
chown -R mysql:mysql /data/tmpdir
chmod a+w /data/tmpdir

#3. 修改MySQL配置，設(shè)置 tmpdir 
vim /etc/my.cnf
把tmpdir設(shè)置到 /data/tmpdir
tmpdir=/data/tmpdir

# 4. 修改完成后，重啟mysql服務(wù)
service mysqld restart

	#注意： 執(zhí)行前必須確保 /mnt/mysql-innodb-temp 目錄已經(jīng)創(chuàng)建了，并且給這個(gè)目錄設(shè)置權(quán)限，確保MySQL能夠讀寫改目錄
 	mysql> set global innodb_tmpdir= '/mnt/mysql-innodb-temp'
 	```
	
下面是操作（踩坑）示例： 
	
```sh
# 注意： 必須先在系統(tǒng)上創(chuàng)建臨時(shí)文件目錄，并且確保mysql用戶有權(quán)限訪問(wèn)這個(gè)目錄
mysql> show VARIABLES like 'innodb_tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| innodb_tmpdir |       |
+---------------+-------+
1 row in set (0.09 sec)

# 1. 設(shè)置   innodb_tmpdir
mysql> set global innodb_tmpdir= '/mnt/mysql-innodb-temp';
#  或者執(zhí)行
mysql> set @@global.innodb_tmpdir=/mnt/mysql-innodb-temp

# 如果報(bào)錯(cuò) ERROR 1231 (42000): Variable 'innodb_tmpdir' can't be set to the value of '/mnt/mysql-innodb-temp' 則說(shuō)明這個(gè)目錄跟 data-dir 重復(fù)了,如果沒(méi)重復(fù)則執(zhí)行這個(gè)已經(jīng)查看詳情
# 顯示最近一次警告信息
# 語(yǔ)法： SHOW WARNINGS [LIMIT [offset,] row_count]
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Path doesn't exist.                                                               |
| Error   | 1231 | Variable 'innodb_tmpdir' can't be set to the value of '/mnt/mysql-innodb-temp' |
+---------+------+-------------------------------------------------------------------------------------------+

# Path doesn't exist. 說(shuō)明該目錄不存在, 創(chuàng)建目錄后再執(zhí)行命令，如果發(fā)現(xiàn)依然報(bào)錯(cuò)，那么也可能是因?yàn)闆](méi)有文件權(quán)限
mysql>  SHOW WARNINGS limit 10;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Server doesn't have permission in the given location.                   |
| Error   | 1231 | Variable 'innodb_tmpdir' can't be set to the value of '/mnt/mysql-innodb-temp' |
+---------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

# 那么設(shè)置該目錄的文件權(quán)限即可。 
> chmod 777 /mnt/mysql-innodb-temp

# 再回到 mysql 發(fā)現(xiàn)執(zhí)行成功了!
mysql> set @@global.innodb_tmpdir='/mnt/mysql-innodb-temp';
Query OK, 0 rows affected (0.00 sec)

# 檢查下效果
mysql>  show VARIABLES like 'innodb_tmpdir';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| innodb_tmpdir |/mnt/mysql-innodb-temp |
+---------------+-----------------------------------+
1 row in set (0.01 sec)

mysql> optimize local table t_word;
-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| abc.t_word| optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| abc.t_word| optimize | status   | OK                                                                |
+-------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 hours 29 min 54.20 sec)