MySQL InnoDB Recovery Mode: Force Recovery and Table Repair

# MySQL InnoDB Recovery Mode: Force Recovery and Table Repair

MySQL fails to start due to InnoDB corruption:

[ERROR] InnoDB: Page checksum 12345678, stored checksum 87654321
[ERROR] InnoDB: Corrupt page [page id: seq no=12345] in file ./db/table.ibd
[ERROR] InnoDB: Assertion failure in thread 14000 file buf0buf.cc line 1234
[ERROR] InnoDB: We intentionally generate a crash to print a stack trace

InnoDB force recovery mode allows you to start MySQL in a degraded state to salvage data.

Understanding InnoDB Force Recovery

Force recovery (innodb_force_recovery) has 6 levels (1-6):

Always start with level 1 and increase only if needed.

Step 1: Enable Recovery Mode

Edit MySQL configuration:

# /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
innodb_force_recovery = 1

Start MySQL:

systemctl start mysqld
systemctl status mysqld

Check error log:

tail -100 /var/log/mysql/error.log

Look for successful startup message:

[Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
[Note] InnoDB: Database was not shutdown normally!
[Note] InnoDB: Starting crash recovery.
[Note] mysqld: ready for connections.

Step 2: Test Access Level

```sql -- Test basic connectivity SELECT 1;

-- List databases SHOW DATABASES;

-- Try to access a table USE your_database; SELECT COUNT(*) FROM your_table LIMIT 1; ```

If you see errors like:

ERROR 1030 (HY000): Got error 168 from storage engine
ERROR 2013 (HY000): Lost connection to MySQL server during query

Increase the recovery level and restart.

Step 3: Progressive Recovery Level Increase

If level 1 doesn't work, progressively increase:

# Stop MySQL
systemctl stop mysqld

# Update configuration
[mysqld]
innodb_force_recovery = 2

# Start MySQL
systemctl start mysqld
mysql -e "SELECT 1;"

Continue increasing to 3, 4, 5, or 6 as needed.

At level 3 and above:

-- You can only SELECT, not INSERT/UPDATE/DELETE
INSERT INTO test VALUES (1);
-- ERROR 1030 (HY000): Got error -1 from storage engine

Step 4: Export Data Immediately

Once MySQL is running, export all data:

```bash # Full backup (best option) mysqldump --all-databases --routines --triggers --events \ --single-transaction --quick \ --user=root --password > recovery_backup_$(date +%Y%m%d).sql

# If single-transaction fails mysqldump --all-databases --routines --triggers --events \ --skip-lock-tables --quick \ --user=root --password > recovery_backup.sql

# Per-database backup for db in $(mysql -N -e "SHOW DATABASES" | grep -v -E '^(information_schema|performance_schema|mysql|sys)$'); do echo "Dumping $db..." mysqldump --skip-lock-tables "$db" > "${db}_$(date +%Y%m%d).sql" done ```

Step 5: Handle Individual Corrupted Tables

If a specific table causes issues:

-- Find which table is corrupted
CHECK TABLE database.table1 EXTENDED;
CHECK TABLE database.table2 EXTENDED;

Or check all tables:

mysqlcheck --all-databases --check --extended --user=root --password

For a corrupted table:

```sql -- Create a duplicate table structure CREATE TABLE table_recovered LIKE corrupted_table;

-- Try to copy data in chunks INSERT INTO table_recovered SELECT * FROM corrupted_table LIMIT 10000;

-- If that fails, try smaller chunks INSERT INTO table_recovered SELECT * FROM corrupted_table WHERE id <= 1000; INSERT INTO table_recovered SELECT * FROM corrupted_table WHERE id > 1000 AND id <= 2000;

-- Find where corruption starts SELECT * FROM corrupted_table WHERE id BETWEEN 5000 AND 6000;

-- Skip the corrupted range INSERT INTO table_recovered SELECT * FROM corrupted_table WHERE id < 5500; INSERT INTO table_recovered SELECT * FROM corrupted_table WHERE id > 5600; ```

Step 6: Handle Foreign Key Issues

At higher recovery levels, foreign key checks may fail:

```sql -- Temporarily disable foreign key checks SET FOREIGN_KEY_CHECKS = 0;

-- Export data SELECT * FROM parent_table; SELECT * FROM child_table;

-- Re-enable after export SET FOREIGN_KEY_CHECKS = 1; ```

Step 7: Drop Corrupted Tables

If a table is unrecoverable:

```sql -- At recovery level 1-2, you can drop tables DROP TABLE corrupted_table;

-- If DROP fails, you may need to remove the file manually -- Stop MySQL first ```

```bash # Stop MySQL systemctl stop mysqld

# Remove the tablespace file rm /var/lib/mysql/database/corrupted_table.ibd

# Remove the table definition rm /var/lib/mysql/database/corrupted_table.frm

# Start MySQL systemctl start mysqld

# Clean up in MySQL DROP TABLE IF EXISTS database.corrupted_table; ```

Step 8: Full Rebuild (If Necessary)

If recovery at level 6 still fails:

```bash # Stop MySQL systemctl stop mysqld

# Backup everything cp -r /var/lib/mysql /var/lib/mysql.backup.$(date +%Y%m%d)

# Remove InnoDB files rm -f /var/lib/mysql/ibdata1 rm -f /var/lib/mysql/ib_logfile* rm -f /var/lib/mysql/ib_buffer_pool

# Remove innodb_force_recovery from config vi /etc/my.cnf # Remove or comment: innodb_force_recovery

# Reinitialize mysqld --initialize --user=mysql

# Start MySQL systemctl start mysqld

# Restore from backup mysql -u root -p < recovery_backup.sql ```

Step 9: Disable Recovery Mode After Fix

Never run in recovery mode permanently. After data export:

# Comment out or remove innodb_force_recovery
[mysqld]
# innodb_force_recovery = 1

systemctl restart mysqld

Recovery Mode Side Effects

Step 10: Verify Data Integrity After Recovery

After restoring from backup or rebuild:

```sql -- Check row counts match expectations SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database';

-- Check for orphaned records SELECT child.id FROM orders child LEFT JOIN customers parent ON child.customer_id = parent.id WHERE parent.id IS NULL;

-- Verify auto-increment values SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'important_table';

-- Check foreign key constraints SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'your_database';

-- Run table checks OPTIMIZE TABLE table1, table2; ANALYZE TABLE table1, table2; ```

Step 11: Test Application Functionality

```sql -- Test typical queries EXPLAIN SELECT * FROM your_table WHERE id = 1;

-- Test joins SELECT * FROM table1 JOIN table2 ON table1.id = table2.ref_id LIMIT 10;

-- Test stored procedures CALL your_stored_procedure();

-- Test triggers (by performing insert/update) INSERT INTO test_table VALUES (1, 'test'); DELETE FROM test_table WHERE id = 1; ```

Common Recovery Scenarios

Scenario 1: Single Corrupted Table

```sql -- At recovery level 1 USE affected_database;

-- Identify corruption CHECK TABLE problematic_table EXTENDED;

-- Create replacement CREATE TABLE problematic_table_new LIKE problematic_table;

-- Copy good data INSERT INTO problematic_table_new SELECT * FROM problematic_table WHERE id < 100000;

-- Swap tables DROP TABLE problematic_table; RENAME TABLE problematic_table_new TO problematic_table; ```

Scenario 2: System Tablespace Corrupted

```bash # Need to rebuild ibdata # At recovery level 1, dump all data mysqldump --all-databases > full_backup.sql

# Stop and rebuild systemctl stop mysqld rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

# Reinitialize and restore mysqld --initialize --user=mysql systemctl start mysqld mysql < full_backup.sql ```

Scenario 3: Redo Log Corrupted

```bash # Try level 6 to skip redo log # /etc/my.cnf [mysqld] innodb_force_recovery = 6

systemctl restart mysqld

# Export immediately - data since last checkpoint may be lost mysqldump --all-databases > backup.sql ```

Prevention Measures

```ini [mysqld] # Enable proper crash recovery innodb_flush_log_at_trx_commit = 1 sync_binlog = 1

# Use file-per-table to isolate corruption innodb_file_per_table = 1

# Adequate buffer pool innodb_buffer_pool_size = 4G

# Proper log size innodb_log_file_size = 512M

# Doublewrite for page protection innodb_doublewrite = 1 ```

Recovery Checklist

• [ ] Start with innodb_force_recovery = 1
• [ ] Check error log for specific errors
• [ ] Test SELECT queries
• [ ] Increase level if needed (2-6)
• [ ] Export all accessible data immediately
• [ ] Identify corrupted tables
• [ ] Recover or drop corrupted tables
• [ ] Rebuild InnoDB files if needed
• [ ] Disable recovery mode after fix
• [ ] Verify data integrity
• [ ] Test application functionality
• [ ] Set up monitoring and backups