Recovering from corrupted InnoDB tables

Step 1 – Bring up your database in recovery mode

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 from 1 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

Step 2 – Check which tables are corrupted and make a list

Now you have your database back up and running, but in recovery mode. You can not change your databases / tables. If you try it, you will get error:

Got error -1 from storage engine

We need to find out which tables got corrupted. In order to do that, we execute: mysqlcheck --all-databases

Check for lines where it says table is Corrupted. Write down all tables / databases that got you an error. You will need to mysqldump them in recovery mode and reimport them after you boot back into normal MySQL mode. Let me also remind you that innochecksum command did not help me with finding out which tables are corrupted, so don’t bother with it.

Step 3 – Backup and drop your corrupted tables

Once you got the list of corrupted tables, you should mysqldump them to their own .sql files, that way you will have backup for reimport. In case you wondered how to dump only one table in database:

mysqldump my_database table > database.table.sql

After you have the backup, drop your corrupted tables by executing: drop table database.table; from your MySQL shell. You have now cleaned up your MySQL database so it’s time to boot it up back without recovery mode.

Step 4 – Restart MySQL in normal mode

When we don’t have any corrupted tables left in our database, we should remove the my.cnf settings that we added in Step 1. Don’t remove the port setting yet, becouse your database is still missing tables you backed up and need to be reimported. Restart your MySQL.

Step 5 – Import backup .sql

Import each dumped .sql table to their respected database. To do that from CLI:

mysql database < database.table.sql

Step 6 – Change port and grab a beer

Once you finished importing your tables, you are free to change port setting in your my.cnf. Of course reboot MySQL afterwards. It should come back and start working just as before the crash. Grab a beer and click on the top of this post, to let me know this article helped you solve your problem.

Reference: http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/