我将my.cnf里面的tmpdir=/data/mysql/tmp ,但是最近发现数据库老是自动重启，下面是日志，基本都是和临时表相关的，大家看看什么错误？2:47:21 InnoDB: Error: table 'tmp/#sqlbe2_132ae_2'
InnoDB: in InnoDB data dictionary has tablespace id 1401648,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table `tmp`.`#sqlbe2_132ae_2`.
2:47:21 InnoDB: error: space object of table tmp/#sqlbe2_132c2_2,
InnoDB: space id 1401654 did not exist in memory. Retrying an open.
2:47:21 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2:47:21 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './tmp/#sqlbe2_132c2_2.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2:47:21 InnoDB: Error: table 'tmp/#sqlbe2_132c2_2'
InnoDB: in InnoDB data dictionary has tablespace id 1401654,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table `tmp`.`#sqlbe2_132c2_2`.
2:47:21 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=szgeodb1-relay-bin' to avoid this problem.
2:47:21 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/event' is ed as crashed and should be repaired
2:47:21 [Warning] Checking table: './mysql/event'
2:47:21 [ERROR] 1 client is using or hasn't closed the table properly
2:47:21 [Note] Event Scheduler: Loaded 3 events
2:47:21 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.1.37-log' socket: '/data/mysql/data/mysql.sock' port: 3306 Source distribution
2:47:21 [Note] Event Scheduler: scheduler thread started with id 1
InnoDB: in InnoDB data dictionary has tablespace id 1401648,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table `tmp`.`#sqlbe2_132ae_2`.
2:47:21 InnoDB: error: space object of table tmp/#sqlbe2_132c2_2,
InnoDB: space id 1401654 did not exist in memory. Retrying an open.
2:47:21 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
2:47:21 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './tmp/#sqlbe2_132c2_2.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2:47:21 InnoDB: Error: table 'tmp/#sqlbe2_132c2_2'
InnoDB: in InnoDB data dictionary has tablespace id 1401654,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table `tmp`.`#sqlbe2_132c2_2`.
2:47:21 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=szgeodb1-relay-bin' to avoid this problem.
2:47:21 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/event' is ed as crashed and should be repaired
2:47:21 [Warning] Checking table: './mysql/event'
2:47:21 [ERROR] 1 client is using or hasn't closed the table properly
2:47:21 [Note] Event Scheduler: Loaded 3 events
2:47:21 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.1.37-log' socket: '/data/mysql/data/mysql.sock' port: 3306 Source distribution
2:47:21 [Note] Event Scheduler: scheduler thread started with id 1
./mysql/event' is ed as crashed
InnoDB: in InnoDB data dictionary has tablespace id 1401648,
InnoDB: but tablespace with that id or name does not exist.
2:47:21 InnoDB: Operating system error number 2 in a file operation.综合上述信息看：
是目录 /data/mysql/tmp 不存在导致的，只有需要用到临时表或给予磁盘的临时表时才发生错误检查目录是否存在及有权限：
ls -lh /data/mysql/tmp
#****************************************#
MySQL技术及运维自动化网：www.mysqlops.com新浪微博账号：http://weibo.com/mysqlops
#****************************************#
[root@test ~]# ll /data/mysql/tmp
total 1008
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:29 #sql13f_565c_1e.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:29 #sql13f_565c_1e.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:25 #sql13f_591a_11.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:25 #sql13f_591a_11.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:29 #sql13f_5960_a.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:29 #sql13f_5960_a.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:26 #sql13f_5a7e_7.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:26 #sql13f_5a7e_7.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:20 #sql13f_5a83_2.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:21 #sql13f_5a83_2.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:26 #sql13f_5a85_5.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:26 #sql13f_5a85_5.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:25 #sql13f_5a86_5.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:25 #sql13f_5a86_5.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:29 #sql13f_5a8a_3.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:29 #sql13f_5a8a_3.ibd
-rw-rw---- 1 mysql mysql 8564 Nov 8 14:18 #sql13f_5a8b_1.frm
-rw-rw---- 1 mysql mysql 98304 Nov 8 14:18 #sql13f_5a8b_1.ibd
drwxrwxrwx 2 root root 4096 Nov 8 14:34 tmp
datadir=/data/mysql/data
tmpdir=/data/mysql/tmp