Fix MySQL or MariaDB Corruption: Recovery Steps

Before You Start Database corruption can be daunting, but understanding the recovery process is crucial. Always ensure you have recent backups before attempting any recovery operation. This guide will walk you through using 'innodb_force_recovery' to recover from database corruption and how to restore from backups. Technical Background MySQL and MariaDB are popular relational database management systems (RDBMS) that use InnoDB as their default storage engine. InnoDB is designed for high reliability and high performance, employing a transactional model that supports ACID (Atomicity, Consistency, Isolation, Durability) properties. Despite its robustness, databases can become corrupted due to hardware failures, software bugs, or improper shutdowns. When corruption occurs, InnoDB may prevent the database from starting to protect data integrity. The 'innodb_force_recovery' parameter can be used to force the database to start by allowing InnoDB to ignore certain types of data corruption. This parameter has levels ranging from 1 to 6, each progressively more aggressive in bypassing checks. The goal is to start the database minimally to extract data and then restore from a clean backup. The recovery process should always conclude with restoring from a backup, as relying on a corrupted database long-term is risky. Step By Step 1. **Verify Backups**: Ensure you have recent and complete backups of your database. 2. **Modify Configuration**: Edit the MySQL or MariaDB configuration file (my.cnf or my.ini) to include 'innodb_force_recovery'. 3. **Set Recovery Level**: Start with level 1 and increase incrementally. For example, 'innodb_force_recovery=1'. 4. **Restart Database**: Restart the database server to apply changes. Use bash or PowerShell, depending on your system. 5. **Extract Data**: Once started, dump the data using mysqldump or similar tools. 6. **Remove Recovery Mode**: After extracting, remove 'innodb_force_recovery' from the configuration. 7. **Restore Backup**: Restore the database from the most recent clean backup. 8. **Verify Integrity**: Check the integrity of the restored database. Worked Example Example: Setting innodb_force_recovery on Linux Command: sudo nano /etc/mysql/my.cnf Add: [mysqld] innodb_force_recovery=1 Command: sudo systemctl restart mysql Configuration Or Command Reference - innodb_force_recovery= — Set recovery level (1-6) to bypass checks. - mysqldump — Utility to export database data. - systemctl restart mysql — Restart MySQL servic…