MySQL 5.0: Recovering Crashed/Corrupted InnoDB Database

If you, by any chance, forgot to create backup, you should do more work as the following:

1. Edit my.cnf (usually located in /etc/my.cnf) and add this line into [mysqld] section

   innodb_force_recovery=4

2. Restart mysql
   (e.g: /etc/init.d/mysqld restart)
3. Your MySQL log should tell you which table is corrupt. You can dump the table using “SELECT … INTO OUTFILE …” from mysql console or using tool like mysqldump
   • Example using MySQL query (you also need to dump the schema later)

     mysql> SELECT * FROM corrupted_table INTO OUTFILE ‘/path/to/file’

   • Example using mysqldump
     

     root# mysqldump –opt -u uname -p dbname corrupted_tablename > /path/to/out.sql

4. Go back to my.cnf and comment the line you wrote from step #1
5. Go intoMySQL console and drop the corrupted table
   

   mysql> DROP TABLE corrupted_tablename;

6. Import data dump, for sql dump you can use:
   

   root# mysql -u uname -p < /path/to/out.sql

7. Go back into mysql console, check and optimize all tables
8. Done