MySQL InnoDB corruption

http://chepri.com/mysql-innodb-corruption-and-recovery/

A query for finding all of your InnoDB tables.

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';



Steps to get it back up.

1. Stop mysqld.
2. Backup /var/lib/mysql/ib*
3. Add the following line into /etc/my.cnf

innodb_force_recovery = 4

4. Restart mysqld.
5. Dump all tables:# mysqldump -A > dump.sql
6. Drop all databases which need recovery.
7. Stop mysqld.
8. Remove /var/lib/mysql/ib*
9. Comment out innodb_force_recovery in /etc/my.cnf
10. Restart mysqld. Look at mysql error log. By default it should be /var/lib/mysql/server/hostname.com.err to see how it creates new ib* files.
11. Restore databases from the dump:mysql < dump.sql