mysql 启动失败，数据恢复

时间：2021-03-19 10:39:40
mysql 启动失败，数据恢复
2017年02月13日 16:46:36
阅读数：621

Forcing InnoDB Recovery提供了6个等级的修复模式，需要注意的是值大于3的时候，会对数据文件造成永久的破坏，不可恢复。六个等级的介绍摘抄如下：

Forcing InnoDB Recovery



1 (SRV_FORCE_IGNORE_CORRUPT)


 


Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.


 


2 (SRV_FORCE_NO_BACKGROUND)


 


Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.


 


3 (SRV_FORCE_NO_TRX_UNDO)


 


Does not run transaction rollbacks after crash recovery.


 


4 (SRV_FORCE_NO_IBUF_MERGE)


 


Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.


 


5 (SRV_FORCE_NO_UNDO_LOG_SCAN)


 


Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.


 


6 (SRV_FORCE_NO_LOG_REDO)


 


Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.







使用方法如下，在mysql配置文件中，添加或修改以下配置的值


my.cnf









[mysqld]


innodb_force_recovery = 1







根据查到的博文提到的方法，我的修复步骤如下：


    

  • 因为我无法启动mysql，所以首先要想办法启动mysql，然后dump数据。从innodb_force_recovery的值1开始尝试，看mysql能否在该修复模式下启动，不到万不得已，不要尝试值为4及以上。
    • 

  • 在我这里，mysql在值为2时可以启动，这是stop掉数据库，然后备份数据
    • 










sudo service mysql stop


mysqldump -u root -p --all-databases > all-databases.sql







  • 删除掉出错的数据文件









mv ib_logfile0 ib_logfile0.bak


mv ib_logfile1 ib_logfile1.bak


mv ibdata1 ibdata1.bak







  • 启动mysql，然后从备份文件恢复数据









sudo service mysql start


mysql -u root -p < all-databases.sql







  • 因为在修复模式下，在插入数据时报错，也就是说此时是不能写入数据的。所以就关闭掉了修复模式









[mysqld]


innodb_force_recovery = 0







restart mysql后，再次恢复数据









sudo service mysql restart


mysql -u root -p --default-character-set=utf8 < all-databases.sql







  • 再次重启下mysql，现在mysql可以正常启动了，并且数据也回复成功。






相关文章