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.
Best Answer
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
Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)?
You need three files for recovery
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 ofdatadir
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
Once you select the value you want, start up MySQL with it. Then, perform a mysqldump of all the data. Keep that mysqldump somewhere.
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 !!!