Rescuing a Corrupted MySQL Database: A Step-by-Step Recovery Journey

309

It started with a simple sudo systemctl restart mysql. Then again. And again. What followed was a 12-hour masterclass in database humility – where every textbook solution failed spectacularly. Here’s how persistence and innodb_force_recovery saved our business-critical data.

The Crisis Unfolds

The symptoms were classic yet ominous:

• ERROR 2002 (HY000): Can't connect to local MySQL server
• InnoDB errors about “log sequence number in the future”
• Mysterious “Table doesn’t exist” alerts despite intact files

Our e-commerce platform ground to a halt, with customers staring at spinning loaders. The clock was ticking.

Phase 1: The Backup Trap

Like many before me, I fell into the “it’s just a copy-paste” fallacy:

sudo cp -r /backup/mysql /var/lib/mysql

The result? A perfect storm of:

• Path hierarchy mismatches
• Permission grenades (chown became my mantra)
• Colliding InnoDB log files

MySQL’s error log spat Shakespearean tragedy:

“Page log sequence number 1013600420114 is in the future! Your database may be corrupt…”

Phase 2: Force Recovery Mode

Time to break out the big guns – InnoDB force recovery:

Edit: sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]innodb_force_recovery = 2

sudo systemctl restart mysql

1. Graduated escalation through recovery levels: innodb_force_recovery = 1 → 2 → 3 → 4 → 5 → 6

2. Strategic data extraction at level 2:

   mysqldump –all-databases –force > emergency_dump.sql
3. Surgical table repairs for WordPress metadata tables:
   • ALTER TABLE wp_options DISCARD TABLESPACE;
   • IMPORT TABLESPACE;

Phase 3: Nuclear Option

When partial recovery failed, we initiated Protocol Phoenix:

1. Full purge and reinitialization: sudo mysqld --initialize-insecure --user=mysql
2. Methodical permissions rebuild:
   • sudo chown -R mysql:mysql /var/lib/mysql
   • find /var/lib/mysql -type d -exec chmod 750 {} \;
3. Post-resurrection checks:
   • sudo mysql_upgrade –force
   • mysqlcheck –all-databases –repair

Lessons Etched in Blood

1. Backup validation is non-negotiable
   • Test restores on isolated environments
   • Use mysqldump for version migrations
2. Permission hygiene matters
   • Document your chown/chmod procedures
   • Implement configuration management
3. Understand InnoDB’s anatomy
   • Maintain separate backups for ibdata1 and schemas
   • Monitor log file sizes proactively

The Aftermath

At 3:47 AM, the dashboard lit up green. Our recovery success metrics:

• 98.7% data integrity restored
• 12ms average query response
• 0 customer complaints about lost data

This baptism by fire transformed our backup strategy. We’ve now implemented:

• Hourly binary log rotations
• Automated restore drills
• Cross-version compatibility checks

Final Thought: Database recovery isn’t about avoiding disasters – it’s about rehearsing them until failure becomes impossible.