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 < all-databases.sql
再次重启下 mysql, 现在 mysql 可以正常启动了, 并且数据也恢复成功
来源: http://www.linuxidc.com/Linux/2018-02/151049.htm