Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I got following message from them-

I have attempted every repair on the InnoDB database and we are still getting the InnoDB log sequence number is in the future. At this point to get the ibdata and the iblogfile to match up once again we will need to restore the MySQL directory (which includes databases) from the backup we have on the server. The process should not take to long but there will be some downtime associated with a restore like this. If this is not the best time to restore the MySQL directory I can schedule this for a different time. Please let me know how you would like to proceed with this.

Can someone tell me what'd be the best way to address this issue. I really don't want to lose out on any data and want the dB to be repaired.

PS: If you need more information, please let me know and I'll get it from our web hosts.

Would really appreciate your help.

  • 2
    If I were in your position, I would ask them to shutdown the MySQL server process (it's still running?), make a tarball of the entire mysql data directory, and make it available to you for so it could be examined by an expert on another machine, before things get worse and before allowing them to restore from a backup. I would also be troubled by the fact that the system got into this state without it being detected and that they are insisting on restoring from a backup in order to fix it. Jun 19, 2013 at 20:20
  • Hope this helps mysqlperformanceblog.com/2013/09/11/…
    – netneladno
    Sep 18, 2013 at 6:43

1 Answer 1


What was suggested to you thus far is what can be done to bring the database to a consistent state.

Here is what you need to know about InnoDB.

First of all here is the InnoDB Architecture in Pictorial Form

InnoDB Picture

Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)?

  • The Double Write Buffer has the cache of changed blocks to be used for recovery.
  • The Insert Buffer handles updates to nonunique indexes
  • The InnoDB transaction logs (ib_logfile0,ib_logfile1) contain Redo Playback Info also used in recovery.
  • There are undo logs (1023 of them, the maximum number of concurrent transactions)

You need three files for recovery

  • ibdata1
  • ib_logfile0
  • ib_logfile1

The whole datadir folder (/var/lib/mysql) needs to be restored from the same moment in time it was being backed up. If there are no physical copies of datadir from the same moment in time, then log sequence numbers for future transactions can never be referenced correctly.

If you do not trust your host in this matter, perhaps you can get MySQL started with innodb_force_recovery set to an appropriate value.

Here are the values from the MySQL Documentation


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.


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


Does not run transaction rollbacks after crash recovery.


Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics.


Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.


Does not do the redo log roll-forward in connection with recovery.

Once you select the value you want, start up MySQL with it. Then, perform a mysqldump of all the data. Keep that mysqldump somewhere.

  • I would set innodb_force_recovery to 6. Restart mysql. Do the mysqldump (MyData6.sql)
  • Change it to 5. Restart mysql. Do the mysqldump (MyData5.sql)
  • Change it to 4. Restart mysql. Do the mysqldump (MyData4.sql)
  • Change it to 3. Restart mysql. Do the mysqldump (MyData3.sql)
  • Change it to 2. Restart mysql. Do the mysqldump (MyData2.sql)
  • Change it to 1. Restart mysql. Do the mysqldump (MyData1.sql)

You now have 6 snapshots of the data based on how much could be recovered. You would then have to load each MySQLDump into a separate instance of MySQL. You would have to then peruse the data and determine if enough of the data has been recovered. Percona has a Data Recovery Toolkit that would do all this way more efficiently than I am saying it.

My answer is simply a poor man's approach to this.

I hope this helps !!!

  • 7
    Definitely don't start with the number 6 for innodb_force_recovery! Values greater than 4 may permanently corrupt data files, according the manual. It's suggested to start with the lowest number (i.e. 1) and only try the higher number in case the server wouldn't start with the current one. There's no need to try all values. Just use the lowest possible, create a dump, delete the broken database or table and re-create it using the dump. Oct 29, 2019 at 18:25
  • It's been awhile and the link to the data recovery toolkit is broken now; any idea if they still offer anything similar?
    – Kevin
    Oct 4, 2022 at 5:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.