Repair or Recover InnoDB Tables in MySQL: A Complete Guide

Repair or Recover InnoDB Tables in MySQL: A Complete Guide

Server crashing in the middle of a session will lead to inconsistent states. As opposed to repairing MYISAM tables in MySQL, repairing InnoDB isn’t that straightforward. There aren’t many dedicated tools available for repairing InnoDB. In this article, learn how to repair or repair InnoDB tables the correct way.

What to do First?

The first thing you should do on priority is to isolate the database. That’s important to contain the problem before it gets out of hand. Then take a backup to ensure you have a copy of the same.

To isolate the database, you need to stop the web server. It will prevent any ongoing and future database transactions. Use the ‘mysqladmin’ utility to check the transactions and thaw them.

Furthermore, you should block any access to port 3306 if you’ve allowed remote access.

Lastly, take a backup of the database. In case you’re out of memory, at least take the backup of all InnoDB files (such files have .ib extension).

After that, you need to start MySQL server. If and when it comes online, take an SQL dump. For this purpose, use the ‘mysqldump’ utility.

But if you notice that the MySQL is crashing continually, use InnoDB recovery and bring the sever back online. For this, open the /etc/my.cnf file and look for the ‘innodb_force_recovery’ option. Then change the value from 1 all the way up to 6 to get MySQL server back online. After that, take an SQL dump.

Some users report that they aren’t able to dump SQL. In such cases, you need to use the daily backups of the affected databases.

Restore the Databases

After you’ve dumped the database, now is the time to restore it.

First, drop the corrupt database and remove the settings in the configuration file. Then restart the MySQL service. Check the error log and ensure there are no errors listed.

Finally, execute the following command to restore databases using MySQL dump:

Code:
# mysql < /path/to/backup/recovered_db.sql
Take Daily Backups

You can use the recovery mode to restore the databases and bring them back online. But it may not always work as intended. This would mean losing the databases forever.

The best alternative is to configure the server to take daily backups reliably without manual intervention. Also, schedule periodic tables which would help in verifying the backup integrity.

Another thing you should do is set up error monitoring of InnoDB databases. You can use third-party tools like Nagios or Zabbix for detecting errors in InnoDB. ‘mysqlcheck’ utility can also be used.

By taking these measures, you’ll be able to find out the database problems at the earliest and recover the whole database in time.
Author
kumkumsharma
Views
633
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from kumkumsharma

Top