Contents
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 = 2sudo systemctl restart mysql
- Graduated escalation through recovery levels:
innodb_force_recovery = 1 → 2 → 3 → 4 → 5 → 6
Strategic data extraction at level 2:
mysqldump –all-databases –force > emergency_dump.sql- 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:
- Full purge and reinitialization:
sudo mysqld --initialize-insecure --user=mysql
- Methodical permissions rebuild:
- sudo chown -R mysql:mysql /var/lib/mysql
- find /var/lib/mysql -type d -exec chmod 750 {} \;
- Post-resurrection checks:
- sudo mysql_upgrade –force
- mysqlcheck –all-databases –repair
Lessons Etched in Blood
- Backup validation is non-negotiable
- Test restores on isolated environments
- Use
mysqldump
for version migrations
- Permission hygiene matters
- Document your
chown
/chmod
procedures - Implement configuration management
- Document your
- Understand InnoDB’s anatomy
- Maintain separate backups for
ibdata1
and schemas - Monitor log file sizes proactively
- Maintain separate backups for
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.