This post is a copy of the wonderful following post:
Here are some important exerts from it:
MySQL – Corrupted InnoDB tables recovery – Step by step guide
Posted in Databases By Alen Krmelj On March 19, 2013, 5-6 minutes
InnoDB tables don’t get corrupted easily, but when they do, it usually happens because of hardware issues, power outages or MySQL bug. It leaves you with corrupted pages in InnoDB tablespace and recovering from that might be problem. When your MySQL properly crashes and doesn’t want to come back, you may see looping of similar error:
InnoDB: Assertion failure in thread 1129654592 in file ibuf0ibuf.c line 4231 InnoDB: Failing assertion: page_get_n_recs(page) > 1 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. ... some backtrace ... The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. mysqld_safe Number of processes running now: 0 mysqld_safe mysqld restarted
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
So all together to bring back database, I had to add these 3 parameters in
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:
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, because 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.