mysql无法启动

234rew

原因：mysql数据库无法启动

分析：查看mysql_error.log日志，
   
2015-07-24 15:06:39 18610 [Note] Plugin 'FEDERATED' is disabled.<br>2015-07-24 15:06:39 18610 [Note] InnoDB: The InnoDB memory heap is disabled<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Compressed tables use zlib 1.2.3<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Using CPU crc32 instructions<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Initializing buffer pool, size = 128.0M<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Completed initialization of buffer pool<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Highest supported file format is Barracuda.<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Log scan progressed past the checkpoint lsn 15415856992<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Database was not shutdown normally!<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Starting crash recovery.<br>2015-07-24 15:06:39 18610 [Note] InnoDB: Reading tablespace information from the .ibd files...<br>2015-07-24 15:06:40 18610 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace finance_TEST0724/f_commission_activity uses space ID: 6537 at filepath: ./finance_TEST0724/f_commission_activity.ibd. Cannot open tablespace finance_TESTBAK/f_commission_activity which uses space ID: 6537 at filepath: ./finance_TESTBAK/f_commission_activity.ibd<br>2015-07-24 15:06:40 7f02b451d720  InnoDB: Operating system error number 2 in a file operation.<br>InnoDB: The error means the system cannot find the path specified.<br>InnoDB: If you are installing InnoDB, remember that you must create<br>InnoDB: directories yourself, InnoDB does not create them.<br>InnoDB: Error: could not open single-table tablespace file ./finance_TESTBAK/f_commission_activity.ibd<br>InnoDB: We do not continue the crash recovery, because the table may become

可以推测是数据库意外关闭导致的文件损坏

解决过程：

一：

    在my.cnf中添加如下参数

    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):不执行前滚的操作。

二：备份数据库

    启动数据库

    通过sqldump或者使用navicat客户端备份库数据

    mysqldump  -uroot -p finance > /home/mysql/dev.sql

三：删除数据库

    mysql>drop database finance；

ps：可以物理删除文件，如果是innodb引擎，必须删除mysql.innodb_index_stats和mysql.innodb_table_stats表里相关表的数据

四：去掉参数innodb_force_recovery

五：启动数据库

六：导入数据

mysql -u root -p finance < dev.sql