How to Rebuild a MySQL Instance with a Corrupted InnoDB Tablespace

Posted Jun 3, 2024

By Dzubayyan Ahmad 5 min read

MySQL databases are essential for many applications, and corruption in the InnoDB tablespace can lead to significant downtime and data loss. This guide provides a detailed step-by-step process to rebuild a MySQL instance with a corrupted InnoDB tablespace.

Introduction

MySQL databases are vital for many applications, and corruption in the InnoDB tablespace can cause significant downtime and data loss. This guide provides a step-by-step process to rebuild a MySQL instance with a corrupted InnoDB tablespace.

Causes of InnoDB Corruption

InnoDB corruption can result from various issues, including:

Sudden Power Loss/Server Reset: Unexpected shutdowns can interrupt database operations, leading to corruption.
Disk Space Issues: When the server’s disk reaches full capacity, it can cause write operations to fail and corrupt the database files.
Hardware Failures: Faulty RAM, CPU, or storage drives can lead to data corruption.
Software Bugs: Bugs in MySQL or the underlying operating system can cause data corruption.
Improper Shutdowns: Forcefully killing the MySQL process or improper shutdowns can lead to incomplete transactions and corrupted data.
Filesystem Corruption: Issues with the filesystem, such as bad sectors or corruption in the storage medium, can affect MySQL data integrity.
Incompatible MySQL Versions: Upgrading or downgrading MySQL versions improperly can lead to corruption.
Malware or Security Breaches: Malicious software or unauthorized access can corrupt database files.

Symptoms of Corruption

Typical error messages indicating InnoDB corruption include:

2024-06-01 16:24:51 0 [ERROR] InnoDB: Corrupted page identifier at 3156638258; set innodb_force_recovery=1 to ignore the record.
2024-06-01 16:24:51 0 [Note] InnoDB: End of log at LSN=3156638258
2024-06-01 16:24:51 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-06-01 16:24:51 0 [Note] InnoDB: Starting shutdown...
2024-06-01 16:24:51 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

Repair Process

Step 1: Ensure Sufficient Disk Space

Before starting the repair process, ensure that the server has sufficient free disk space, at least as much as the size of the /var/lib/mysql directory. Insufficient disk space can cause further issues during the repair.

Step 2: Modify MySQL Configuration

To enable MySQL to start with corrupted tables, you need to set the innodb_force_recovery parameter. This forces InnoDB to skip certain checks and recovery steps that can prevent the server from starting.

Edit the MySQL configuration file:
1 sudo nano /etc/my.cnf
Add the innodb_force_recovery option under the [mysqld] section:
1 2 [mysqld] innodb_force_recovery=1

Step 3: Start MySQL Service

Attempt to start the MySQL service:

sudo systemctl start mysqld

If the service fails to start, increment the innodb_force_recovery value by 1 (up to a maximum of 6) and try again:

sudo nano /etc/my.cnf

  
[mysqld]
innodb_force_recovery=2

sudo systemctl start mysqld

Step 4: Dump All Databases

Once the service starts, create a backup of all databases. This step is crucial to ensure that you have a copy of your data before proceeding with more destructive recovery steps.

Create a backup directory:

mkdir ~/mysql_backups
cd ~/mysql_backups

Dump all databases:

  
for db in $(mysql -B -s -e 'show databases;'); do
  echo $db
  mysqldump --extended-insert=true -B $db > $db.sql
done

Note: Some databases may fail to dump. Record these errors, but you can ignore errors related to performance_schema and information_schema.

Restoring Data

Step 5: Stop the MySQL Service

Before proceeding with the restoration process, stop the MySQL service:

sudo systemctl stop mysqld

Step 6: Delete the MySQL Data Directory

To ensure a clean slate, delete the contents of the MySQL data directory:

  
sudo rm -rf /var/lib/mysql/*

Step 7: Reinitialize the MySQL Data Directory

Reinitialize the MySQL data directory to create a fresh InnoDB tablespace:

sudo mysql_install_db --force

Step 8: Adjust Permissions

Ensure that the MySQL data directory has the correct ownership and permissions:

sudo chown -R mysql:mysql /var/lib/mysql

Step 9: Modify MySQL Configuration

Edit the my.cnf file to remove the innodb_force_recovery line and add skip_grant_tables:

sudo nano /etc/my.cnf

Update the [mysqld] section:

  
[mysqld]
# Remove or comment out the following line
# innodb_force_recovery=1

# Add this line
skip_grant_tables

Step 10: Start the MySQL Service

Start the MySQL service again:

sudo systemctl start mysqld

Step 11: Import Databases

Import the previously dumped databases to the new, clean MySQL instance:

Navigate to the backup directory:
1 cd ~/mysql_backups

Import each database:

  
for db_file in *.sql; do
  echo $db_file
  mysql -f < $db_file
done

Step 12: Finalize Configuration

Stop the MySQL service once more:

sudo systemctl stop mysqld

Edit the my.cnf file to remove the skip_grant_tables line:

sudo nano /etc/my.cnf