IT and Tech

Recovering from a MySQL InnoDB crash

On Feb 9th 2019, the datacenter where the Virtual Private Server that I use for this site(and many others) experienced a bit of a hardware failure which required some emergency maintenance.

This happened overnight and when I woke up, I found my inbox with 100+ emails alerts from the various sites that I manage indicating an error with connecting to the database.

Normally this isn’t a huge issue but this time things were different. The MySQL server not only wouldn’t start, it also didn’t provide any logs in either the MySQL error log or the system error log. I didn’t have any recent backups and neither did the datacenter, so this meant that had to do some serious guess work.

If that sounds similar to what you have to deal with, don’t worry. It’s fixable.

The Fix

Before you start screwing around with a live production database, unless you have daily or weekly backups that you would be ok with, the first thing you’re going to want to do is extract whatever data you can.

To do that, we’re going to boot the MySQL in forced recovery mode. There’s a line in your mysql my.cnf that says “innodb_forced_recovery = 0”

Change that 0 value to 1 or just add that line if you don’t have it.

This may or may not work depending on the issue. If it doesn’t work, try a value of 2 or 3. If recovery levels 1-3 won’t work for you, there’s still hope as there’s also levels 4-6, however, do understand that recovery levels 4-6 may cause data loss with level 6 resulting in guaranteed data loss. So work your way up from level 1 and pray to god that your database boots up in the earlier levels.

In my case, I had to use recovery level 4 which puts me in a risky position but at least it booted.

Once you find a level that boots, it’s time to backup and restore your databases/tables

Backup and restore MySQL

For this you have two options. Either export the databases using phpmyadmin or use mysqldump.

Both tools will fail once it reaches a table that’s corrupted so your mileage may vary.

To dump ALL databases run

mysqldump –all-databases –single-transaction –quick –lock-tables=false > someName-full-backup-$(date +%F).sql -u root -p

To dump one specific database run

mysqldump -u username -p databaseName –single-transaction –quick –lock-tables=false > databaseName-backup-$(date +%F).sql

To dump one specific table run

mysqldump -u username -p –single-transaction –quick –lock-tables=false databaseName tableName > databaseName-tableName-$(date +%F).sql

Once you have everything backed up, this is where it may be easy or not as it depends on what the issue is and what innodb recovery level you had to use.

Finding the faulty table

Finding the faulty table

Completely remove MySQL server and do a fresh re-install

This step

Remove MySQL completely (instructions for a Debian based system)

sudo apt-get remove –purge mysql\*
sudo apt-get clean
sudo updatedb

Reinstall MySQL (in my case it was 5.6 or 5.7)

Download the mysql repository configuration tool


(you can get whatever the latest version is at )

Install the tool using

dpkg -i mysql-apt-config_0.8.9-1_all.deb

You get the option of selecting versions 5.6 or 5.7. I went with 5.6 because that’s what was running before.

Update the apt repo list and install the damn server

apt-get update
apt-get install mysql-community-server

Re-install the mysqli connector (in my case I needed it for php5.6 and php7)

apt-get install php5-mysqlnd
apt-get install php-mysql

Note: you can get away with php5-mysql but for MySQL 5.6 or higher you’re going to want mysqlnd. (the ND stand for Native Driver).

While it php5-mysql and MySQL 5.6+ will most likely work, depending on the level of error reporting, you may get a LOT of notices about incorrect minor versions. It’s a problem I ran into before and it’s more annoying than anything.

To restore a backup:

For full backups

mysql -u root -p < full-backup.sql

For a specific database

mysql -u [username] -p databaseName < databaseName-backup.sql

For a specific table

mysql -u [username] -p databaseName < databaseName-tableName.sql

Understanding the InnoDB recovery levels

Leave a Reply

Your email address will not be published.