innodb_force_recovery when InnoDB corruption

Question

When I start mysqld (in /etc/init.d), it failed with

InnoDB: corruption in the InnoDB tablespace.

What's the best innodb_force_recovery value to force mysqld to start? I have tried 4 and 6, but corruption error message is the same. I already have a full dump.

Answer 1

According to MySQL's documentation, if you use innodb_force_recovery=1, the server will start even if it detects a corrupt page.

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

Still, there are a couple of things you may want to check.

Are there any relevant errors in mysql's error log? cat /var/log/mysqld.log

Is the file system OK, can you create files with touch in the same partition ? ex. touch /var/somefile.txt

If you have a full backup of your databases, as you mention in your question, you can delete the ibdata and ib_logfile(s) and recreate them, then restore your databases. This will resolve any corruption issues you may have in the innodb files.

The steps are:

1. Stop mysql

2. delete the ibdata and ib_logfile files.

   ex : rm -f /var/lib/mysql/ibdata1

   ex : rm -f /var/lib/mysql/ib_logfile*

3. Start MySQL (which recreates the innodb files)

4. Restore the dump mysql -u root -p < mydump.sql

As long as you only delete the innodb files(ibdata and ib_logfiles), your users and their access rights to the databases will still be in tact.