Linux, MySQL

Repairing MySQL InnoDB databases

| by admin

Introduction:
This post is a copy of the wonderful following post:
https://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/https://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/

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 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, 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.

Leave a Reply