今天一个研发数据库突然断电后，数据库无法启动，原因是其中一个表文件损坏导致，下面是问题处理过程

(日志文件如下 我本地电脑的错误日志文件名 D:\xampp\mysql\data\PC-201710161042.err 正常日志好像是D:\xampp\mysql\data\ib_logfile0 D:\xampp\mysql\data\ib_logfile1 等等 :)

2018-06-02  8:40:52 4748 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace brocms2/bro_message uses space ID: 2432 at filepath: .\brocms2\bro_message.ibd. Cannot open tablespace laravelweixintest/slides which uses space ID: 2432 at filepath: .\laravelweixintest\slides.ibd

InnoDB: Error: could not open single-table tablespace file .\laravelweixintest\slides.ibd  这一个数据库这个表有问题

2015-01-26 14:18:05 4122 [Note] InnoDB: Database was not shutdown normally!
2015-01-26 14:18:05 4122 [Note] InnoDB: Starting crash recovery.
2015-01-26 14:18:05 4122 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-01-26 14:18:05 4122 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace dev/tb_test uses spac
e ID: 1 at filepath: ./dev/tb_test.ibd. Cannot open tablespace mysql/innodb_table_stats which uses space ID: 1 at filepath: ./mysql/
innodb_table_stats.ibd
2015-01-26 14:18:05 2ad861898590  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./mysql/innodb_table_stats.ibd
InnoDB: We do not continue the crash recovery, because the table may becomeInnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
150126 14:18:06 mysqld_safe mysqld from pid file /home/mysql/mysql_app/dbdata/host01.pid ended

大意是一个表空间id号(1)抢占了另一个表空间的id号(1),导致不能打开另一个表空间，从而数据库不能打开

1.在my.cnf中添加如下参数
在[mysqld]组中加入：
innodb_force_recovery=6   (好像是 1-6的数字 只要大于0就行,最好为6吧)

innodb_purge_threads = 0   (默认是0吧)

innodb_force_recovery参数解释：
innodb_force_recovery影响整个InnoDB存储引擎的恢复状况，默认值为0，表示当需要恢复时执行所有的恢复操作。
当不能进行有效的恢复操作时，mysql有可能无法启动，并记录下错误日志。

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。
当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash
 3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

2.启动数据库

3.备份数据库
$mysqldump -h 192.168.56.111 -uroot -p dev > /home/mysql/dev.sql

4.删除数据库 如里删不掉的话 就停止数据库 然后删除数据库的文件夹 (在data 目录下对应的数据库的文件夹)
$mysql -h 192.168.56.111 -uroot -p
mysql> drop database dev;
ERROR 1051 (42S02): Unknown table 'dev.tb_test'

物理删除tb_test对应的frm和ibd文件

mysql> drop database dev;
Query OK, 0 rows affected (0.00 sec)

4-2) 可能还需要删除 ibdata1 (好像删了之后 启动就会报错) ib_logfile*  等文件 (如果下面的 5 6 7 操作不行的话看左边)

4-3) 导数据库可能无法导入, 提示 table 表格已存在里面多了 .pid文件 是应该是数据库服务器启动状态下 删掉.ibd还是关闭状态下删.ibd文件

呢? (如果下面的 5 6 7 操作不行的话看左边)

5.去掉参数innodb_force_recovery 和 innodb_purge_threads 
将之前设置的参数去掉后，重新启动数据库
#innodb_force_recovery=6

#innodb_purge_threads = 0

6.(启动 mysql )创建数据库
mysql> create database dev;
Query OK, 1 row affected (0.03 sec)

7.导入数据  (这一步有大用)
[mysql@host01 dev]$ mysql -h 192.168.56.111 -uroot -pmysql dev Warning: Using a password on the command line interface can be insecure.
ERROR 1050 (42S01) at line 25: Table '`dev`.`tb_test`' already exists

提示表已经存在，这是因为将innodb_force_recovery参数去掉后，数据库会进行回滚操作，会生成相应的tb_test.ibd文件，所以需要进data目录将该文件 tb_test.ibd 删除掉. 删除后重新导入(如果无法删除呢) 怎么办?

第一种方法:重新删除数据库 就是提示报错 有 tb_test.ibd 文件,进data 目录手动删除,好像就生成了一个新的空的数据库 dev (当然是latin1_general_ci的数据库,不含回滚操作的空的数据库),再删除它,再重新建 数据库dev,然而再导入就可以了

第二种方法:重启一下数据库,应该就可以删除tb_test.ibd了,再删除数据库dev,新建数据库dev(此时就不会回滚tb_test.ibd),再导入sql文件就可以了.

(可能是在数据库停止状态下删除吧,好像未必,好像必须在数据库运行状态下删除)删除后还是无法导入,按上面的 7) 步聚重新再做一遍

[mysql@host01 dev]$ mysql -h 192.168.56.111 -uroot -pmysql dev 
-- The End --

来自 http://blog.chinaunix.net/uid-77311-id-4800176.html

我在虚拟机上模拟mysql服务器突然断电的场景，在mysql正常运行的情况下直接重启服务器，结果mysql数据库起不来了，详细情况如下：

环境：

操作系统           系统内核版本                                  mysql 版本

CentOS 5.4       Linux  2.6.18-164.el5  x86_64        5.6.14

1. 重启服务器后（模拟断电），启动mysql报错

[root@langkeziju2 bin]# ./mysqld_safe --user=mysql &
131104 10:02:26 mysqld_safe Logging to '/data0/mysql-3307/data/langkeziju2.err'.
131104 10:02:26 mysqld_safe Starting mysqld daemon with databases from /data0/mysql-3307/data
131104 10:02:28 mysqld_safe mysqld from pid file /data0/mysql-3307/data/langkeziju2.pid ended

2.在错误日志中查看究竟

[root@langkeziju2 bin]# vi  /data0/mysql-3307/data/langkeziju2.err

2013-11-04 10:02:27 5816 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-04 10:02:27 5816 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-11-04 10:02:27 5816 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-04 10:02:27 5816 [Note] InnoDB: Using Linux native AIO
2013-11-04 10:02:27 5816 [Note] InnoDB: Using CPU crc32 instructions
2013-11-04 10:02:27 5816 [Note] InnoDB: Initializing buffer pool, size = 100.0M
2013-11-04 10:02:27 5816 [Note] InnoDB: Completed initialization of buffer pool
2013-11-04 10:02:28 5816 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-04 10:02:28 5816 [Note] InnoDB: The log sequence numbers 1600657 and 1600657 in ibdata files do not match the log sequence number 1676012 in the ib_logfiles!
2013-11-04 10:02:28 5816 [Note] InnoDB: Database was not shutdown normally!
2013-11-04 10:02:28 5816 [Note] InnoDB: Starting crash recovery.
2013-11-04 10:02:28 5816 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-11-04 10:02:28 5816 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace hr/employee uses space ID: 1 at filepath: ./hr/employee.ibd. Cannot open tablespace mysql/innodb_table_stats which uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd
2013-11-04 10:02:28 2aaf5d032520  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./mysql/innodb_table_stats.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
131104 10:02:28 mysqld_safe mysqld from pid file /data0/mysql-3307/data/langkeziju2.pid ended

hr/employee 表空间id号(1)抢占了mysql/innodb_table_stats表空间的id号(1),导致不能打开mysql/innodb_table_stats表空间，从而数据库不能打开

3. 根据错误日志中的提示将innodb_force_recovery参数设置成>0的数值加入配置文件my.cnf中

[root@langkeziju2 bin]# vi /data0/mysql-3307/my.cnf

在[mysqld]组中加入：

innodb_force_recovery=6

*************************************

innodb_force_recovery参数解释：

innodb_force_recovery影响整个InnoDB存储引擎的恢复状况，默认值为0，表示当需要恢复时执行所有的恢复操作。
当不能进行有效的恢复操作时，mysql有可能无法启动，并记录下错误日志。

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。
当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

*********************************

4.mysql能正常启动了，我对hr库做了备份，然后将其删掉

[root@langkeziju2 bin]# ./mysqld_safe --user=mysql &
[1] 6440
[root@langkeziju2 bin]# 131104 10:04:06 mysqld_safe Logging to '/data0/mysql-3307/data/langkeziju2.err'.
131104 10:04:06 mysqld_safe Starting mysqld daemon with databases from /data0/mysql-3307/data

[root@langkeziju2 data_3307]# mysqldump -uroot -p hr > /bak/hr.sql
Enter password:

[root@langkeziju2 data_3307]# ls
hr.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hr                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database hr;
Query OK, 3 rows affected (0.65 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

5. 还原hr库，报：ERROR 1030 (HY000) at line 41: Got error -1 from storage engine

[root@langkeziju2 data_3307]# mysql -uroot -p hr < hr.sql 
Enter password: 
ERROR 1049 (42000): Unknown database 'hr.sql'

还原hr库，hr库必须存在，因我已经将hr库删除，现在创建一个空hr库

mysql> create database hr;
Query OK, 1 row affected (0.00 sec)

再次尝试还原hr库

[root@langkeziju2 data_3307]# mysql -uroot -p hr < hr.sql
Enter password: 
ERROR 1030 (HY000) at line 41: Got error -1 from storage engine

报这个错误是因为上面在my.cnf中添加了innodb_force_recovery=6的缘故

6. 将my.cnf中的innodb_force_recovery参数设置成0或者将其删除

[root@langkeziju2 ~]# vi /data0/mysql-3307/my.cnf

[mysqld]

innodb_force_recovery=0

7.重启mysql

[root@langkeziju2 ~]# mysqladmin -uroot -p shutdown

[root@langkeziju2 ~]# mysqld_safe --user=mysql &

8. 成功还原hr库

[root@langkeziju2 data_3307]# mysql3307 hr < hr.sql
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hr                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.12 sec)

来自   https://blog.csdn.net/langkeziju/article/details/14126399 

Mysql无法启动 InnoDB: Attempted to open a previously opened tablespace

win2008，Mysql5.6，mysql服务无法启动

查看事件日志，报错InnoDB: Attempted to open a previously opened tablespace

最终解决方法，修改my.ini

把innodb_force_recovery = 0 修改 innodb_force_recovery = 1

================================================

参数Innodb_force_recovery影响了整个InnoDB存储引擎的恢复状况。默认0

因为日志已经损坏，这里采用非常规手段,首先修改innodb_force_recovery参数，使mysqld跳过恢复步骤，将mysqld 启动,将数据导出来然后重建数据库。

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。

1. (SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
  2. (SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
  3. (SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
  4. (SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
  5. (SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
  6. (SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。

注意

a 当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

立即对数据库做逻辑导出 ，完成之后将innodb_force_recovery设置为0 ，innodb_purge_thread=1 ,然后重建数据库 。

来自 https://www.cnblogs.com/7dwww/p/6841601.html

2018-03-12 14:30:04 9728 [Note] InnoDB: Using mutexes to ref count buffer pool pages

2018-03-12 14:30:04 9728 [Note] InnoDB: The InnoDB memory heap is disabled

2018-03-12 14:30:04 9728 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

2018-03-12 14:30:04 9728 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier

2018-03-12 14:30:04 9728 [Note] InnoDB: Compressed tables use zlib 1.2.3

2018-03-12 14:30:04 9728 [Note] InnoDB: Using generic crc32 instructions

2018-03-12 14:30:04 9728 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2018-03-12 14:30:04 9728 [Note] InnoDB: Completed initialization of buffer pool

2018-03-12 14:30:04 9728 [Note] InnoDB: Highest supported file format is Barracuda.

2018-03-12 14:30:04 9728 [Note] InnoDB: The log sequence numbers 60088286 and 60088286 in ibdata files do not match the log sequence number 60088296 in the ib_logfiles!

2018-03-12 14:30:04 9728 [Note] InnoDB: Database was not shutdown normally!

2018-03-12 14:30:04 9728 [Note] InnoDB: Starting crash recovery.

2018-03-12 14:30:04 9728 [Note] InnoDB: Reading tablespace information from the .ibd files...

2018-03-12 14:30:08 9728 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace drupalredhendemo/field_revision_redhen_relation_roles uses space ID: 1793 at filepath: .\drupalredhendemo\field_revision_redhen_relation_roles.ibd. Cannot open tablespace piwik/piwik_archive_blob_2017_11 which uses space ID: 1793 at filepath: .\piwik\piwik_archive_blob_2017_11.ibd

InnoDB: Error: could not open single-table tablespace file .\piwik\piwik_archive_blob_2017_11.ibd

InnoDB: We do not continue the crash recovery, because the table may become

InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.

InnoDB: To fix the problem and start mysqld:

InnoDB: 1) If there is a permission problem in the file and mysqld cannot

InnoDB: open the file, you should modify the permissions.

InnoDB: 2) If the table is not needed, or you can restore it from a backup,

InnoDB: then you can remove the .ibd file, and InnoDB will do a normal

InnoDB: crash recovery and ignore that table.

InnoDB: 3) If the file system or the disk is broken, and you cannot remove

InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf

InnoDB: and force InnoDB to continue crash recovery here.