InnoDB corruption can crash your MySQL service, lock your data, and leave your application hanging. If you're seeing corruption errors in the MySQL logs or facing InnoDB-related crashes, don’t panic. This step-by-step guide walks you through safely backing up, recovering, and restoring a corrupted InnoDB database.
Important: Always back up your MySQL data before attempting any recovery operation. These steps involve the deletion of files and forced recovery modes, which can result in permanent data loss if not handled properly.
Step 1: First, take a full backup of the existing MySQL data directory as a precaution:
# cd /var/lib/ # tar -czvf mysql.tar.gz mysql
Step 2: Stop the MySQL Service
# /etc/init.d/mysql stop
Step 3: Enable Forced Recovery Mode. Edit /etc/my.cnf and add the following line under the [mysqld] section:
# innodb_force_recovery = 4
Step 4: Restart MySQL in Read-Only Mode. MySQL will now start in read-only mode, allowing you to safely dump data.
# service mysql restart or # systemctl start mysql
Step 5: Backup All Databases Using mysqldump
# mysqldump -u root -p -A > alltable.sql
Step 6: Drop Corrupted Databases. You can delete corrupted databases either by removing their folders:
# rm -rf /var/lib/mysql/db_name
Step 7: We can check the available database using this command
# show databases;
Step 8: Stop the MySQL Service Again
# service mysql stop
Step 9: Remove InnoDB Log and Data Files. Delete the InnoDB system files to force a clean rebuild:
# rm -rf /var/lib/mysql/ib*
Step 10: Disable Forced Recovery. Remove or comment out the innodb_force_recovery line from /etc/my.cnf.
Step 11: Restart MySQL in Normal Mode
# service mysql restart or # systemctl start mysql
Step 12: Restore the Databases from Backup
# mysql -u root -p dbname < alltable.sql
Step 13: Restart the MySQL service again to confirm everything is working:
# service mysql restart or # systemctl start mysql
Conclusion:
If MySQL starts successfully after following the above steps, the InnoDB corruption issue has been resolved. If not, you may need to restore from an older, known-good backup. To avoid such issues in the future, ensure regular backups, disk health monitoring, and graceful shutdowns of the MySQL service.
