MySQL InnoDB Table Corruption Troubleshooting: Forcing Recovery on CentOS Stream / Rocky Linux

Fix MySQL InnoDB table corruption on CentOS Stream/Rocky Linux requiring `innodb_force_recovery`. A step-by-step guide for data recovery and prevention.

A corrupt InnoDB table can bring your MySQL or MariaDB database to a grinding halt, resulting in inaccessible websites or applications and critical data loss. This guide will walk you through the process of recovering your database using innodb_force_recovery, a powerful but potentially destructive setting, on CentOS Stream and Rocky Linux environments. We'll focus on safely dumping your data and restoring it to a fresh database instance.

Symptom & Error Signature

When InnoDB table corruption occurs, your MySQL/MariaDB service might fail to start, or if it does start, queries to the affected tables will fail. You'll typically see errors in your database logs (/var/log/mysqld.log or journalctl -u mysqld) indicating issues with pages, tablespaces, or data dictionary inconsistencies.

Here are common error signatures you might encounter:

[ERROR] [MY-011011] [Server] Failed to find tablespace for table 'your_database/your_table' in the cache.
[ERROR] [MY-010928] [Server] A message was sent to a wrong thread type at ...
[ERROR] [MY-010931] [Server] InnoDB: Table 'your_database/your_table' not found.
[ERROR] [MY-010933] [Server] InnoDB: Trying to access page number 12345 in space 67890, but maximum page number in space is 9876.
[ERROR] [MY-010935] [Server] InnoDB: Assertion failure in file ... at line ...
[ERROR] [MY-010935] [Server] InnoDB: Corrupt page [page_number] of type [page_type] in space [space_id], page no [page_no].
[ERROR] [MY-010935] [Server] InnoDB: Database page corruption or a fatal error.
[ERROR] [MY-010935] [Server] InnoDB: The database was not shut down normally!

You might also find the database continually crashing after startup attempts.

Root Cause Analysis

InnoDB table corruption can stem from various underlying issues. Understanding the cause can help prevent future occurrences:

• Hardware Failure: Faulty disk drives (bad sectors), corrupted RAM, or unreliable power supplies can lead to data being written incorrectly or read incorrectly from disk.
• Unexpected Server Shutdown: Abrupt power loss, system crashes, or forcibly killing the mysqld process without a proper shutdown can leave InnoDB tables in an inconsistent state, especially if data was being written or transaction logs were not fully flushed.
• File System Issues: Corruption at the file system level (e.g., ext4, XFS) can directly impact the integrity of InnoDB data files (.ibd files), redo logs (ib_logfile*), and system tablespaces (ibdata*).
• Software Bugs: While rare in stable releases, bugs in MySQL/MariaDB itself or the operating system kernel can occasionally lead to data corruption.
• Disk Space Exhaustion: Running out of disk space during critical write operations (e.g., table alterations, large inserts) can prevent MySQL from completing transactions and flushing data, leading to corruption.
• Misconfiguration: Aggressive innodb_flush_log_at_trx_commit settings (e.g., 0 or 2) combined with system crashes increase the risk of corruption because data isn't immediately synced to disk.

Step-by-Step Resolution

This recovery process involves significant risk, including potential data loss. Always prioritize a full backup before attempting these steps.

This procedure involves potentially irreversible data loss. Ensure you have a full server backup (VM snapshot, file system backup) before proceeding. Data that cannot be recovered by innodb_force_recovery will be lost.

1. Stop the MySQL/MariaDB Service

Before making any changes or backups, ensure the database service is stopped cleanly.

sudo systemctl stop mysqld

Verify it's stopped:

sudo systemctl status mysqld

2. Backup Existing Data Directory

This is the most critical step. Create a full copy of your MySQL data directory. The default location is /var/lib/mysql.

# Create a timestamped backup directory
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
sudo mkdir -p /var/lib/mysql_backup_$TIMESTAMP

# Copy all contents of the data directory
sudo cp -a /var/lib/mysql/* /var/lib/mysql_backup_$TIMESTAMP/

echo "MySQL data directory backed up to /var/lib/mysql_backup_$TIMESTAMP"

Do not skip this backup. It is your only recourse if the recovery process further damages your data or fails.

3. Configure innodb_force_recovery

innodb_force_recovery tells InnoDB to start up even if it finds corruption. There are different levels, from 1 to 6, each bypassing more checks and potentially allowing more data loss. You should start with the lowest level (1) and increment if the database still fails to start.

Edit your MySQL configuration file, typically /etc/my.cnf or a file within /etc/my.cnf.d/ (e.g., /etc/my.cnf.d/mysql-server.cnf).

sudo vi /etc/my.cnf.d/mysql-server.cnf

Add or modify the [mysqld] section:

[mysqld]
innodb_force_recovery = 1

If innodb_force_recovery = 1 does not allow the server to start, try incrementing the value to 2, then 3, and so on, up to 6.

Higher innodb_force_recovery levels (4-6) are very dangerous and can permanently corrupt data that might otherwise be recoverable. They are usually a last resort when lower levels fail and you need to salvage any data. Levels 4-6 also prevent background operations like purge, which can prevent clean shutdowns. If using level 4-6, also add:

innodb_purge_threads=0
innodb_max_purge_lag=0

4. Start MySQL/MariaDB in Recovery Mode

Attempt to start the service with innodb_force_recovery enabled.

sudo systemctl start mysqld

Monitor the logs closely for any new errors or confirmation of successful startup:

sudo journalctl -u mysqld -f

If it fails, stop the service, increase innodb_force_recovery level in my.cnf, and try again. Repeat until the service starts successfully.

5. Export All Databases (Dump)

Once the database is running in recovery mode, immediately export all your data. This is the goal of the recovery process: to get a consistent dump of as much data as possible.

# For MySQL 8.0+
sudo mysqldump --all-databases --single-transaction --skip-lock-tables --flush-privileges > /root/all_databases_recovery_dump.sql

# For MariaDB (or older MySQL)
# sudo mariadb-dump --all-databases --single-transaction --skip-lock-tables --flush-privileges > /root/all_databases_recovery_dump.sql

• --single-transaction helps ensure data consistency without locking tables, crucial when tables might be unstable.
• --skip-lock-tables prevents LOCK TABLES statements, which can fail on corrupted tables.
• --flush-privileges ensures the mysql database (users and permissions) is also dumped correctly.
• The dump might take a long time depending on your database size. Do not interrupt it.
• If you encounter errors during the dump, it means some data is unrecoverable. Make note of the tables that cause issues. You might need to exclude them from the dump, or try dumping them individually if possible.

6. Stop MySQL/MariaDB and Remove Corrupted Data

After successfully dumping the databases, stop the MySQL service. We will now remove the corrupted data files to prepare for a fresh installation.

sudo systemctl stop mysqld

Then, remove the contents of the MySQL data directory. Ensure your backup from Step 2 is complete and verified before running this command.

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

7. Reinitialize MySQL/MariaDB Data Directory

Now, create a clean, fresh data directory. The command varies slightly between MySQL and MariaDB.

For MySQL 8.0+:

sudo mysqld --initialize --user=mysql --datadir=/var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql

For MariaDB 10.x:

sudo mariadb-install-db --user=mysql --datadir=/var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql

8. Restore MySQL Configuration

Edit your configuration file (/etc/my.cnf.d/mysql-server.cnf or /etc/my.cnf) and remove the innodb_force_recovery line. Also, remove innodb_purge_threads=0 and innodb_max_purge_lag=0 if you added them. Re-add any other custom configuration settings you had previously (e.g., character sets, buffer sizes).

sudo vi /etc/my.cnf.d/mysql-server.cnf

Ensure the [mysqld] section is clean, resembling something like this (without innodb_force_recovery):

[mysqld]
# Add your other custom settings here, e.g.:
# character-set-server=utf8mb4
# collation-server=utf8mb4_unicode_ci

9. Start MySQL/MariaDB Normally

Start the MySQL service with the clean configuration and data directory.

sudo systemctl start mysqld

Verify that it started without errors:

sudo systemctl status mysqld
sudo journalctl -u mysqld -f

10. Import Databases

Once the fresh MySQL instance is running, import the SQL dump you created earlier.

mysql < /root/all_databases_recovery_dump.sql

This process will recreate all databases, tables, data, users, and grants from your dump file.

11. Post-Recovery Steps

• Verify Data Integrity: Log into MySQL and check your databases, tables, and data. Run application-specific tests to ensure everything is functioning as expected.

  mysql -u root -p
  # Then in MySQL shell:
  SHOW DATABASES;
  USE your_database;
  SHOW TABLES;
  SELECT COUNT(*) FROM your_table;
  

• Check Users and Grants: If you rely on specific users and grants, verify they were restored correctly. If any users are missing or permissions are incorrect, you may need to recreate them manually.
• Run mysql_upgrade (if applicable): If this was a major version upgrade or if you encounter any unexpected issues with system tables, running mysql_upgrade can sometimes resolve them.

  sudo mysql_upgrade -u root -p
  

• Investigate Root Cause: Review system logs (/var/log/messages, dmesg, journalctl) for signs of hardware issues, power events, or file system problems that might have caused the corruption. Consider running a disk health check (smartctl) and memory test (memtest86).
• Implement Robust Backup Strategy: If you didn't have one, or if your existing one failed, implement a reliable automated backup solution (e.g., Percona XtraBackup for hot backups, regular mysqldump to offsite storage).
• Monitor Disk Space: Ensure adequate disk space is available for future operations.

By following these steps, you should be able to recover most, if not all, of your data from a corrupted InnoDB instance on CentOS Stream or Rocky Linux. Remember that proactive monitoring and robust backup strategies are key to preventing and quickly recovering from such catastrophic failures.