Mysql optimize table 时报错:Temporary file write fail的解决

# 执行 optimize 命令时会发现报错
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)

# 进入MySQL
mysql> SHOW VARIABLES LIKE ‘tmpdir’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| tmpdir | /tmp |
+—————+——-+
1 row in set (0.00 sec)

# 回到Linux ，可见 /tmp 目录实际上是在系统盘(vda1)上的。（这个是默认位置）
> 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.创建 tmpdir目录

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

#3. 修改MySQL配置，设置 tmpdir
vim /etc/my.cnf
把tmpdir设置到 /data/tmpdir
tmpdir=/data/tmpdir

# 4. 修改完成后，重启mysql服务
service mysqld restart

#注意： 执行前必须确保 /mnt/mysql-innodb-temp 目录已经创建了，并且给这个目录设置权限，确保MySQL能够读写改目录
mysql> set global innodb_tmpdir= ‘/mnt/mysql-innodb-temp’
“`

下面是操作（踩坑）示例：

“`sh
# 注意： 必须先在系统上创建临时文件目录，并且确保mysql用户有权限访问这个目录
mysql> show VARIABLES like ‘innodb_tmpdir’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| innodb_tmpdir | |
+—————+——-+
1 row in set (0.09 sec)

# 1. 设置 innodb_tmpdir
mysql> set global innodb_tmpdir= ‘/mnt/mysql-innodb-temp’;
# 或者执行
mysql> set @@global.innodb_tmpdir=/mnt/mysql-innodb-temp

# 如果报错 ERROR 1231 (42000): Variable ‘innodb_tmpdir’ can’t be set to the value of ‘/mnt/mysql-innodb-temp’ 则说明这个目录跟 data-dir 重复了,如果没重复则执行这个已经查看详情
# 显示最近一次警告信息
# 语法： 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. 说明该目录不存在, 创建目录后再执行命令，如果发现依然报错，那么也可能是因为没有文件权限
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)

# 那么设置该目录的文件权限即可。
> chmod 777 /mnt/mysql-innodb-temp

# 再回到 mysql 发现执行成功了!
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)