MySQL 啟動失敗資料恢複案例

MySQL 啟動失敗資料恢複案例

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可以正常啟動了，並且資料也恢複成功。

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-02/151049.htm