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: 13160511 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: 132016-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: InnoDB2016-05-11 15:44:04 2432 [ERROR] Aborting

4. 解決方案

  修改tmpdir 目錄許可權為777，重啟mysql執行個體，問題解決。