InnoDB: Error: unable to create temporary file; errno: 13

1.版本

1)操作系统

 cat /etc/issue
cat /etc/issue
CentOS release 6.6 (Final)
Kernel \r on an \m

 cat /proc/version
cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014

2)mysql数据库版本

mysql --version

mysql  Ver 14.14 Distrib 5.1.73, for unknown-linux-gnu (x86_64) using readline 5.1

2. 问题描述

   今天一个朋友说他修改了mysql数据库的tmpdir参数，重启mysql实例后，对innodb表进行optimize操作报类似如下错误(修改之前是可以执行optimize操作的)：

optimize table test_1;
+-------------+----------+----------+-------------------------------+
| Table       | Op       | Msg_type | Msg_text                      |
+-------------+----------+----------+-------------------------------+
| test.test_1 | optimize | Error    | Unknown table engine 'InnoDB' |
| test.test_1 | optimize | error    | Corrupt                       |
+-------------+----------+----------+-------------------------------+
2 rows in set (0.00 sec)

##此时对innodb表的操作如select,dml等操作都会报Unknown table engine 'InnoDB'错

3. 问题分析

3.1 查看数据库目前支持的存储引擎

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

##我们看到此时数据库支持的存储引擎中已经没有了innodb(但是那位朋友确认这个库之前确实是支持innodb存储引擎的，库中多数是innodb表)

3.2 查看数据库errorlog

  既然之前数据库是innodb存储引擎是正常的，在修改了tmpdir参数重启实例后发现异常，那肯定是跟这个改动有关了。(多数是目录权限问题)，我们了查看一下数据库的errorlog，在errorlog中发现如下报错：

^G/usr/sbin/mysqld: Can't create/write to file '/home/tmp_test/ibJOkvIT' (Errcode: 13)
160511 16:46:58  InnoDB: Error: unable to create temporary file; errno: 13
160511 16:46:58 [ERROR] Plugin 'InnoDB' init function returned error.
160511 16:46:58 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

##从errorlog中我们可以清楚的看到因为mysqld进程没有写新指定的tmpdir目录(/home/tmp_test)的权限，innodb存储引擎初始化失败。所以你登录数据库对innodb表进行相关操作时报Unknown table engine 'InnoDB'错

NOTE: 如果是同样的情况发生在mysql 5.6上你mysql实例是无法启动的，errorlog中会报如下错误：

^G/usr/local/mysql/bin/mysqld: Can't create/write to file '/home/tmp_test/ibOw1Z7q' (Errcode: 13 - Permission denied)
2016-05-11 15:44:04 7f306b223720  InnoDB: Error: unable to create temporary file; errno: 13
2016-05-11 15:44:04 2432 [ERROR] Plugin 'InnoDB' init function returned error.
2016-05-11 15:44:04 2432 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-05-11 15:44:04 2432 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-05-11 15:44:04 2432 [ERROR] Aborting

4. 解决方案

  修改tmpdir 目录权限为777，重启mysql实例，问题解决。