Server crashes could leave databases in inconsistent states. While there are many tools available to repair MYISAM tables in MySQL, InnoDB repair is not always so straightforward. MySQL could refuse to start up, leaving you with in-determinate downtime. If your MySQL error logs show an InnoDB corruption, it’s time to tread carefully.

Initial response

The first priority is to isolate the database, and to get a backup. For this, stop the web server (which will deny any more new database transactions), and block external access to port 3306 (if you have allowed remote access). Using the “mysqladmin” utility, ensure that there are no more transactions running.

Then, take a backup of the entire MySQL data folder, or if the space doesn’t permit it, take a backup of all the InnoDB files (those starting with *.ib).

Once this is done, try starting the MySQL server, and if it comes online, take an SQL dump using the mysqldump utility.

If MySQL keeps crashing, use InnoDB recovery to bring the server back online. For this, change the value of the “innodb_force_recovery” in /etc/my.cnf. Start with value “1”, and go on till value “6” to get the MySQL server online again. Once the server is back online, take an SQL dump.

If you are unable to get a dump, your next best option would be to use the daily backups that you have of the affected databases.

Restoring the databases

Once you have a reliable database dump, drop the corrupt databases, and remove the recovery settings from /etc/my.cnf.

Start the MySQL service again, and make sure there are no more errors displayed in the error log.

Now, you can restore the databases using the SQL dump.

# mysql < /path/to/backup/recovered_db.sql

How to be prepared

Server crashes can happen due to a variety of reasons like hardware failure, high server load etc. So, it is possible that InnoDB can get corrupted at any such instance. As explained above, we can use recovery mode to get back the databases, but it might not always be successful.

So, the next best option should always be available, which is a reliable daily backup. Configure your server for daily database backups, and schedule periodic audits to verify the backup integrity.

Another important step is to enable error monitoring of InnoDB databases. Monitoring systems like Nagios and Zabbix can include third party or custom developed plugins to detect InnoDB errors via error logs or utilities such as “mysqlcheck”.

Timely detection of an error gives you a higher probability of recovering the whole database. If you want us to retrieve your valuable data for you from your crashed databases, feel free to contact us.