InnoDB: 2 of name './banggood201401/ana_tmp_sku.ibd' already exists in the tablespace

春节期间某台服务器断电重启后，数据库无法启动！

看mysql错误日志

140207 08:11:51 mysqld_safe Starting mysqld daemon with databases from /data/mysql_data
140207  8:11:52 [Warning] Using unique option prefix thread_cache instead of thread_cache_size is deprecated and will be removed in a future release. Please use the full name instead.
140207  8:11:53 [Note] Plugin ‘FEDERATED‘ is disabled.
140207  8:11:53 InnoDB: The InnoDB memory heap is disabled
140207  8:11:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140207  8:11:53 InnoDB: Compressed tables use zlib 1.2.3
140207  8:11:53 InnoDB: Using Linux native AIO
140207  8:11:53 InnoDB: Initializing buffer pool, size = 512.0M
140207  8:11:53 InnoDB: Completed initialization of buffer pool
140207  8:11:53 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140207  8:11:53  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: trying to add tablespace 2 of name ‘./banggood/ana_tmp_sku.ibd‘
InnoDB: to the tablespace memory cache, but tablespace
InnoDB: 2 of name ‘./banggood201401/ana_tmp_sku.ibd‘ already exists in the tablespace
InnoDB: memory cache!
140207 08:12:35 mysqld_safe mysqld from pid file /data/mysql_data/test1.banggood.com.pid ended

尝试将ana_tmp_sku相关文件移动

mv ana_tmp_sku.* /soft/tmp/

再次尝试重新启动,启动成功，再次查看错误日志，发现仍有如下信息

140207  8:39:28 [ERROR] Cannot find or open table banggood/ana_tmp_sku from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn‘t support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

重新尝试将ana_tmp_sku.frm拷贝回来至数据目录，只是暂时将innodb独享表空间的数据文件丢失！重新启动，没有发现错误！

但是查看ana_tmp_sku表跑错如下：

Table ‘banggood201401.ana_tmp_sku‘ doesn‘t exist

这是因为在mysql的innodb共享表空间中已经将ana_tmp_sku的信息移除，无法在表空间中读取表结构！

需要从备份中恢复表结构和数据！

因为这个表并不是十分重要，所以从备份恢复该表，并导入数据库中！

总结：innodb启动不起来，如果是单表损坏导致，最好是只移除该表的独显表空间数据文件！其实从这里还可以看出一个问题，尽量用独享表空间！如果损坏单表，马上移除改单表，恢复大部分数据应用，尽量将down机时间缩短！

本文出自 “原下” 博客，请务必保留此出处http://qdjalone.blog.51cto.com/1222376/1357001