6.5.8 How to recover MySQL with corrupted InnoDB

MySQL Restore Banner EN

Sometimes MySQL may stop working due to corruption of the InnoDB data structure. In such cases, standard tools such as REPAIR TABLE are usually useless. If the server does not start or the databases become inaccessible, the only solution is to force recovery and recreate the original data structure.

The general algorithm of actions in this case is as follows:

  1. Put MySQL into emergency mode to gain access to the data.
  2. Create a backup copy of all databases, including technical ones.
  3. Completely delete/comment out the corrupted data.
  4. Reinitialize MySQL with a clean directory.
  5. Restore data from a backup copy.

This method allows you to return the MySQL server to a working state, preserving as much data as possible.

Step-by-step guide

When such a recovery is needed, MySQL usually does not start without recovery mode. First of all, we need to start it. For this, we will need a mode called innodb_force_recovery.

Important: innodb_force_recovery is not intended for stable operation of MySQL. Use it only for emergency startup in order to restore stable operation of MySQL without this mode.

1. Put MySQL into emergency mode. To do this, you first need to find the MySQL configuration file on your system:

For Linux 
sudo nano /etc/mysql/my.cnf

or

sudo nano /etc/my.cnf

In Ubuntu+ISPManager4 images, the MySQL configuration file is most often located at /etc/mysql/mysql.conf.d/mysqld.cnf.

For Windows

Open C:\ProgramData\MySQL\MySQL Server X.Y\my.ini in any text editor.

Add the parameter innodb_force_recovery=6 to the end of the [mysqld] section:

[mysqld]
innodb_force_recovery=6

Additionally: recovery levels can vary from 1 to 6. Level 6 is the most aggressive and can lead to data loss, since the database already operates in read_only mode.

Save the file and close the editor.

2. Restart MySQL for the changes to take effect.

For Linux (SysVinit or Upstart) 
sudo service mysql restart
For Linux (systemd) 
sudo systemctl restart mysql
For Windows

Start MySQL via services.msc or run in the command line:

net start mysql

3. Now create a full dump of all databases:

mysqldump -u root -p --all-databases --single-transaction --routines --triggers > backup.sql

Parameter explanations:

  • --single-transaction allows dumping without locking tables.
  • --routines --triggers preserves stored procedures and triggers.
If dump is not possible due to errors

Try --force:

mysqldump -u root -p --all-databases --single-transaction --routines --triggers --force > backup.sql

4. After successful dump creation, stop MySQL server:

sudo service mysql stop

5. Reopen my.cnf configuration file and disable recovery mode by removing the line:

innodb_force_recovery=6

Save changes.

6. Rename the old data directory to keep a copy of it:

sudo mv /var/lib/mysql /var/lib/mysql_old

Create a new directory and set the necessary permissions:

sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql

Advice: in some cases MySQL may run on non-standard users, have non-standard access rights, etc. Just copy all these parameters from the old renamed folder /var/lib/mysql_old.

7. Initialize a new data directory:

Для MySQL 8 and higher 
sudo mysqld --initialize
For MySQL 5.7 
sudo mysqld --initialize
For MySQL 5.6 and lower 
sudo mysql_install_db

Warning: when initializing a new MySQL environment, the system will provide you with a temporary MySQL root password, which must be written down:

MySQL Recovery, temporary password

And after that – it is advisable to immediately change it to a new one. You can do it like this:

  • Login to MySQL with a temporary password:
mysql -u root -p
  • Run the command, where instead of NewStrongPassword specify the password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword';
EXIT;

8. Now start MySQL again:

sudo service mysql start

If MySQL does not start, check the logs:

sudo journalctl -u mysql --no-pager | tail -30

or

sudo cat /var/log/mysql/error.log | tail -n 30

9. Load the data back using dump import:

mysql -u root -p < backup.sql

10. Check if the data was restored correctly using a similar query to one of your databases:

SHOW DATABASES;
USE your_db;
SHOW TABLES;

Then also check the site operation, especially with an emphasis on reading and writing the database.

If after testing and for a couple of days everything is fine – you have successfully restored the MySQL data structure. Now you can delete the old files (/var/lib/mysql_old) to free up space on the disk if desired.