Before using innodb_force_recovery ensure that you have a backup
copy of your database in case you need to start over. You should always begin by
setting innodb_force_recovery to a lower value. Incrementally
increase the setting as required. Only use an innodb_force_recovery setting of 3 or greater on a
production server instance after you have successfully tested the setting on
separate physical copy of your database.
innodb_force_recovery is 0 by default (normal startup
without forced recovery). The permissible nonzero values for innodb_force_recovery are 1 to 6. A larger value
includes the functionality of lesser values. For example, a value of 3 includes
all of the functionality 1 and 2. If you are able to dump your tables with an
option value of at most 3, then you are relatively safe that only some data on
corrupt individual pages is lost. A value of 6 is considered drastic because
database pages are left in an obsolete state, which in turn may introduce more
corruption into B-trees and other database structures.
-
1(SRV_FORCE_IGNORE_CORRUPT)Lets the server run even if it detects a corrupt page. Tries to make
SELECT * FROMjump over corrupt index records and pages, which helps in dumping tables.tbl_name -
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.
-
5(SRV_FORCE_NO_UNDO_LOG_SCAN)Does not look at undo logs when starting the database:
InnoDBtreats even incomplete transactions as committed. -
6(SRV_FORCE_NO_LOG_REDO)Does not do the redo log roll-forward in connection with recovery.
With this value, you might not be able to do queries other than a basic
SELECT * FROM t, with noWHERE,ORDER BY, or other clauses. More complex queries could encounter corrupted data structures and fail.If corruption within the table data prevents you from dumping the entire table contents, a query with an
ORDER BYclause might be able to dump the portion of the table after the corrupted part.primary_keyDESC
The database must not otherwise be used with any nonzero
value of innodb_force_recovery. As a safety
measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.
Q:
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.
A:
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:
-
Stop mysql
-
delete the ibdata and ib_logfile files.
ex :
rm -f /var/lib/mysql/ibdata1ex :
rm -f /var/lib/mysql/ib_logfile* -
Start MySQL (which recreates the innodb files)
-
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.
参考：
http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
http://dba.stackexchange.com/questions/23361/innodb-force-recovery-when-innodb-corruption