MySql Innodb corruption recovery

MySql Innodb corruption recovery

by hames lamar -
Number of replies: 1

Hi,

 

I have a MySql database, just containing Innodb tables, that seems to have corrupted itself.

 

It replicates to another server and it has corrupted that one too!

 

The symprom is that I can restart the server but as soon as I try to connect to it, it crashes.

 

The log file talks about a possible index page corruption to a specific table and that fits in terms of the table being accessed when the problem occurred.

 

Following the Innodb recovery guidelines in the MySql manual, I have set the innodb_force_recovery option to 6 which lets me restart the database and dump the tables.

 

Anything less than 6 and it will not stay up.

 

My questions are:

How do I know the extent of the innodb corruption?

Is the only safe option to recreate the innodb files from scratch (I assume via a re-install) or is there a smarter way to do this

Can I trust the table dumps or should I go back to the most recent backup (also a table dump) and accept the data loss

If it is an index page corruption, can I rebuild the primary index of the affected table and then restart the DB without the innodb_force_recovery option

 

Thanks


Average of ratings: -
In reply to hames lamar

Re: MySql Innodb corruption recovery

by trued moller -

Bring up your database in recovery mode is one of the methods for mysql tables, another variant is accessible and must be used if you can't find any other https://mysql.recoverytoolbox.com/ Recovery Toolbox for MySQL

 

You should bring down your database. Shut it down in case it’s still running and spamming these messages in your log. As last resort, you may also kill the process. In order to bring back your database you will need to start it in recovery mode, with innodb_force_recovery. You should know this recovery mode makes your databases read only. Users connecting to it can not update, insert or other way alter existing data. To prevent your MySQL getting hammered the second it comes back, I suggest you to also change port of MySQL server from 3306 to something random. Add innodb_force_recovery=1 to your my.cnf In case your server doesn’t want to come back, you may further increase this number from1 to 6, check MySQL manual to see what the differences are.

Be sure to check your MySQL logs, and if it loops with something like:

InnoDB: Waiting for the background threads to start

You should also add innodb_purge_threads=0 to your my.cnf.

So all together to bring back database, I had to add these 3 parameters in my.cnf:

port = 8881

innodb_force_recovery=3

innodb_purge_threads=0

Average of ratings: Useful (1)