mysql 8 临时表空间

mysql> optimize table iuap_apdoc_coredoc.productapplyrange;
+--------------------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------------------------+----------+----------+-------------------------------------------------------------------+
| iuap_apdoc_coredoc.productapplyrange | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| iuap_apdoc_coredoc.productapplyrange | optimize | error | Temporary file write failure. |
| iuap_apdoc_coredoc.productapplyrange | optimize | status | Operation failed |
+--------------------------------------+----------+----------+-------------------------------------------------------------------+
3 rows in set (2112.27 sec)

优化表空间时临时表空间不足

查询临时表空间

root@mysqldb 14:34: [(none)]> show global variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global VARIABLES like 'innodb_tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| innodb_tmpdir | |
+---------------+-------+
1 row in set (0.05 sec)

tmpdir 不能动态修改需要重启mysql

如果表时innodb引擎，使用innodb_tmpdir，动态修改

mkdir -p /data/mysql/tmp

chown -R mysql:mysql  /data/mysql/tmp

set global innodb_tmpdir='/data/mysql/tmp';

set @@global.innodb_tmpdir='/data/mysql/tmp';

set innodb_tmpdir='/data/mysql/tmp';

root@mysqldb 14:32: [(none)]> show global VARIABLES like 'innodb_tmpdir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| innodb_tmpdir | /data/mysql/tmp |
+---------------+-----------------+
1 row in set (0.01 sec)