MySQL Crash Recovery: Restoring After Unexpected Shutdown

# MySQL Crash Recovery: Restoring After Unexpected Shutdown

Your server experienced an unexpected shutdown or power failure. Now MySQL won't start, or it starts but reports errors:

[ERROR] InnoDB: Database page corruption on disk or a failed file read of tablespace
[ERROR] InnoDB: Page [page id: seq no=12345] log sequence number is in the future

This guide walks through recovering your MySQL database after a crash.

Understanding Crash Recovery

MySQL uses several mechanisms to protect data:

1. 1.InnoDB Buffer Pool: Modified pages in memory not yet written to disk
2. 2.Redo Log (ib_logfile0, ib_logfile1): Records all changes for recovery
3. 3.Undo Log: Tracks uncommitted transactions for rollback
4. 4.Doublewrite Buffer: Protects against partial page writes

1. 1.When MySQL crashes, the recovery process:
2. 2.Reads redo logs to replay committed transactions
3. 3.Reads undo logs to rollback uncommitted transactions
4. 4.Validates page checksums

Step 1: Assess the Damage

Check if MySQL can start normally:

```bash systemctl start mysqld systemctl status mysqld

# Check error log immediately tail -100 /var/log/mysql/error.log ```

Look for specific crash indicators:

[ERROR] InnoDB: Page checksum 12345678, stored checksum 87654321
[ERROR] InnoDB: Corrupt page [page id: ...] of file ./database/table.ibd
[Warning] InnoDB: A long semaphore wait has occurred
[ERROR] InnoDB: Assertion failure in thread 12345 file fil0fil.cc line 456

Step 2: Start in Recovery Mode

If MySQL won't start, use InnoDB force recovery:

# Add to my.cnf [mysqld] section
[mysqld]
innodb_force_recovery = 1
innodb_purge_threads = 0

Recovery levels explained:

Start with level 1 and increase only if needed:

```bash # Try level 1 first systemctl start mysqld

# Check if it started mysql -e "SHOW DATABASES;" ```

Step 3: Dump All Data (Critical!)

As soon as MySQL starts in recovery mode, backup your data:

```bash # Dump all databases mysqldump --all-databases --routines --triggers --events \ --single-transaction --quick --lock-tables=false \ --user=root --password > crash_recovery_backup_$(date +%Y%m%d).sql

# Dump specific tables that are accessible mysqldump database_name table1 table2 > specific_tables_backup.sql ```

If some tables fail to dump:

```sql -- Identify corrupted tables CHECK TABLE database.table_name QUICK;

-- Try repair for MyISAM REPAIR TABLE database.table_name;

-- For InnoDB, you may need to extract data row by row SELECT * FROM corrupted_table LIMIT 1000; ```

Step 4: Identify Corrupted Tables

```sql -- Check all tables in a database USE your_database; SHOW TABLES;

-- Check each table CHECK TABLE table1 EXTENDED; CHECK TABLE table2 EXTENDED;

-- Or check all tables at once mysqlcheck --all-databases --check --user=root --password ```

If a table fails check:

```sql -- See how many rows are accessible SELECT COUNT(*) FROM corrupted_table;

-- Try to find the corrupt section SELECT MIN(id), MAX(id) FROM corrupted_table; SELECT * FROM corrupted_table WHERE id BETWEEN 1 AND 10000; ```

Step 5: Recover Corrupted Tables

For InnoDB Tables

If the table is corrupted beyond repair:

```sql -- Get table structure SHOW CREATE TABLE corrupted_table;

-- Create new table with same structure CREATE TABLE recovered_table LIKE corrupted_table;

-- Insert what data you can salvage INSERT INTO recovered_table SELECT * FROM corrupted_table WHERE id < 50000;

-- Drop the corrupted table DROP TABLE corrupted_table;

-- Rename recovered table RENAME TABLE recovered_table TO corrupted_table; ```

If you can't SELECT from the table at all:

```bash # Try dumping with --force to skip errors mysqldump --force database_name corrupted_table > partial_backup.sql

# If that fails, you may need to recreate the table # Use the .frm file if available to recover structure ```

For MyISAM Tables

```bash # Check and repair myisamchk -r /var/lib/mysql/database/table_name.MYI

# For severe corruption myisamchk -o /var/lib/mysql/database/table_name.MYI

# Or from MySQL REPAIR TABLE database.table_name USE_FRM; ```

Step 6: Rebuild InnoDB Tablespace

If ibdata1 is corrupted:

```bash # Stop MySQL systemctl stop mysqld

# Backup existing files cp -r /var/lib/mysql /var/lib/mysql.backup cp /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.backup cp /var/lib/mysql/ib_logfile* /var/lib/mysql/ib_logfile.backup/

# Remove InnoDB files (only if you have a good dump) rm -f /var/lib/mysql/ibdata1 rm -f /var/lib/mysql/ib_logfile*

# Remove innodb_force_recovery from my.cnf # Or set to 0

# Start MySQL (will recreate InnoDB files) systemctl start mysqld

# Restore data from dump mysql --user=root --password < crash_recovery_backup.sql ```

Step 7: Handle Binary Log Corruption

Crash can corrupt binary logs:

```bash # Check for binary log errors ls -la /var/lib/mysql/mysql-bin.*

# Check binary log integrity mysqlbinlog mysql-bin.000123 > /dev/null 2>&1 echo $? # 0 = OK, non-zero = corrupted ```

If binary logs are corrupted:

```sql -- Reset binary logs RESET MASTER;

-- Or purge up to a specific log PURGE BINARY LOGS TO 'mysql-bin.000124'; ```

Step 8: Verify Data Integrity

After recovery, thoroughly check your data:

```sql -- Check table counts match expected values SELECT COUNT(*) FROM critical_table;

-- Check foreign key integrity SELECT child.id FROM child_table child LEFT JOIN parent_table parent ON child.parent_id = parent.id WHERE parent.id IS NULL;

-- Check for data anomalies SELECT * FROM orders WHERE created_at > NOW(); SELECT * FROM users WHERE email IS NULL OR email = '';

-- Run full check mysqlcheck --all-databases --check --optimize --user=root --password ```

Step 9: Rebuild from Backup (Last Resort)

If recovery fails, restore from your latest backup:

```bash # Stop MySQL systemctl stop mysqld

# Remove or move corrupted data mv /var/lib/mysql /var/lib/mysql.corrupted

# Reinitialize mysqld --initialize --user=mysql

# Start MySQL systemctl start mysqld

# Get temporary password grep 'temporary password' /var/log/mysql/error.log

# Restore backup mysql -u root -p < /path/to/backup.sql ```

Step 10: Post-Recovery Tasks

After successful recovery:

```sql -- Rebuild indexes OPTIMIZE TABLE table1, table2, table3;

-- Analyze tables for optimizer statistics ANALYZE TABLE table1, table2;

-- Check replication if applicable SHOW SLAVE STATUS\G START SLAVE;

-- Rebuild stored procedures if needed SHOW PROCEDURE STATUS WHERE Db = 'your_database'; ```

Monitoring for Future Issues

```bash # Set up monitoring script cat > /usr/local/bin/mysql_health_check.sh << 'EOF' #!/bin/bash mysqladmin ping -u monitor -ppassword || \ echo "MySQL not responding" | mail -s "MySQL Alert" admin@example.com

# Check for crashed tables mysqlcheck --all-databases --check --silent -u monitor -ppassword

# Check InnoDB status mysql -u monitor -ppassword -e "SHOW ENGINE INNODB STATUS\G" | grep -i "error|corrupt" EOF

chmod +x /usr/local/bin/mysql_health_check.sh

# Add to cron echo "*/5 * * * * /usr/local/bin/mysql_health_check.sh" | crontab - ```

Prevention Best Practices

1. 1.Enable binary logging for point-in-time recovery
2. 2.Use UPS for servers to prevent power failure crashes
3. 3.Regular backups with verified restore procedures
4. 4.Monitor disk health with SMART monitoring
5. 5.Use InnoDB instead of MyISAM for crash safety

# Recommended settings for crash safety
[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog_format = ROW
log_error_verbosity = 3

Recovery Checklist

• [ ] Check error logs for crash cause
• [ ] Start with innodb_force_recovery = 1
• [ ] Dump all accessible data immediately
• [ ] Identify corrupted tables with CHECK TABLE
• [ ] Attempt table repair or data extraction
• [ ] Rebuild InnoDB files if necessary
• [ ] Verify data integrity after recovery
• [ ] Set up monitoring to prevent future issues