MySQL Crashed: Causes and Solutions – Comparison and Best Practices

Understanding MySQL Crash Categories

MySQL crashes fall into three categories: resource exhaustion, data corruption, and process termination. Each category requires different diagnostic and recovery approaches.

Resource exhaustion crashes occur when MySQL runs out of memory, disk space, or file descriptors. The operating system's OOM killer terminates the mysqld process, or queries fail with 'Cannot allocate memory' errors. These crashes are clean but disruptive.

Corruption crashes happen when table files, indexes, or transaction logs become inconsistent. You'll see 'Table is marked as crashed' or 'InnoDB: Database page corruption' in error logs. These require repair tools and may involve data loss.

Process termination crashes result from signals, kernel panics, or improper shutdowns. Power failures, hardware faults, or kill -9 commands leave MySQL in an inconsistent state. InnoDB's crash recovery handles most cases, but MyISAM tables need manual repair.

Comparing Storage Engine Recovery: InnoDB vs MyISAM

InnoDB and MyISAM handle crashes differently. InnoDB uses write-ahead logging and automatic crash recovery, while MyISAM requires manual table repair. Your storage engine choice determines your recovery workflow.

InnoDB recovery is automatic. On restart, MySQL replays the redo log to restore committed transactions and rolls back incomplete ones. The process takes seconds to minutes depending on log size. Use innodb_force_recovery levels 1-6 only when automatic recovery fails. Level 1 tolerates corrupt pages, level 3 skips transaction rollback, and level 6 enables read-only mode for emergency dumps.

MyISAM recovery is manual. Crashed MyISAM tables stay inaccessible until you run CHECK TABLE and REPAIR TABLE, or use myisamchk on stopped servers. The repair process can take hours for large tables and may lose rows if indexes are severely corrupted. Always copy table files before attempting repair.

For mixed environments, prioritize InnoDB tables during recovery. Check error logs for 'Table is marked as crashed' messages to identify MyISAM tables needing repair. Convert MyISAM to InnoDB after recovery using ALTER TABLE table_name ENGINE=InnoDB to gain automatic recovery benefits.

• InnoDB: Automatic recovery via transaction logs, roll-forward and rollback support, typically completes in under 5 minutes
• MyISAM: Manual repair required, CHECK TABLE followed by REPAIR TABLE, may lose uncommitted data
• Recommendation: Use InnoDB for production databases to minimize manual intervention during crashes

Diagnosing Root Causes: Memory, Corruption, and Configuration

Start diagnosis by checking three areas: system memory pressure, table integrity, and MySQL configuration limits. Each points to different root causes and solutions.

Check system logs first. Run dmesg | grep -i mysql or journalctl -u mysql to find OOM killer messages, segmentation faults, or kernel panics. OOM kills indicate insufficient memory or misconfigured buffer pools. Adjust innodb_buffer_pool_size to 60-70% of available RAM, not total RAM.

Examine MySQL error logs at /var/log/mysql/error.log. Look for 'InnoDB: Database page corruption', 'Table is marked as crashed', or 'Assertion failure' messages. Corruption messages require integrity checks with CHECK TABLE. Assertion failures often indicate bugs or hardware issues requiring MySQL version updates or hardware diagnostics.

Review configuration limits with SHOW VARIABLES. Compare max_connections, table_open_cache, and open_files_limit against current usage with SHOW STATUS. Exhausted limits cause connection refusal and table cache thrashing. Increase limits incrementally and monitor memory impact before applying permanently.

Step-by-Step Recovery Procedures

Follow this sequence for safe MySQL recovery. Always take backups before repair attempts to prevent permanent data loss.

First, attempt normal startup. Run systemctl start mysql or mysqld_safe and monitor error logs. If InnoDB recovery completes without errors, verify table access with SELECT COUNT(*) queries on critical tables. This works for 80% of crashes.

If startup hangs or fails, enable innodb_force_recovery. Stop MySQL, add innodb_force_recovery=1 to my.cnf under [mysqld], and restart. If successful, dump databases with mysqldump, remove innodb_force_recovery, recreate the instance, and restore dumps. If level 1 fails, increment to level 2, then 3. Do not use levels 4-6 without taking full backups first.

For MyISAM corruption, stop MySQL and run myisamchk -r /var/lib/mysql/database/table.MYI on each crashed table. The -r flag repairs data files. For severe corruption, use myisamchk -o for extended repair with row sorting. After repair, restart MySQL and run CHECK TABLE to verify integrity.

For emergency access when authentication fails, start MySQL with mysqld --skip-grant-tables. This bypasses password checks, letting you reset credentials or dump data. Restrict access to localhost only and shut down immediately after completing recovery tasks.

• Backup first: Copy /var/lib/mysql to a safe location before any repair attempt
• Try clean restart: Stop MySQL cleanly, remove stale PID files, restart with standard configuration
• Use innodb_force_recovery incrementally: Start at level 1, increase only if lower levels fail
• Document recovery steps: Record which level worked and what data was affected for post-incident review

Prevention Strategies: Monitoring and Configuration

Preventing crashes is more effective than perfecting recovery. Focus on three areas: resource monitoring, safe configuration limits, and regular maintenance.

Implement memory monitoring with tools like Prometheus, Zabbix, or CloudWatch. Alert when system memory usage exceeds 80% or MySQL buffer pool usage exceeds 90%. Set innodb_buffer_pool_size conservatively to leave 30-40% RAM for the operating system, connections, and temporary tables. A 16GB server should use 10-11GB for InnoDB, not 14GB.

Enable binary logging with log_bin and sync_binlog=1 for point-in-time recovery. Configure automated backups using mysqldump or Percona XtraBackup daily, with retention policies matching your RTO requirements. Test restore procedures monthly to verify backup integrity.

Schedule maintenance windows for OPTIMIZE TABLE on MyISAM tables and ANALYZE TABLE on InnoDB tables. Fragmentation increases I/O pressure and crash risk. Monitor slow query logs and add indexes to reduce table scan load. High query load on unindexed columns triggers memory pressure and potential crashes.

Use replication or clustering for high availability. A single-server setup means every crash causes downtime. Multi-server replication with ProxySQL or HAProxy provides automatic failover when the primary crashes, reducing recovery urgency and allowing thorough diagnosis.

Choosing the Right Approach for Your Environment

Your recovery strategy depends on your storage engine mix, downtime tolerance, and data criticality. Here's how to choose.

For InnoDB-only environments, rely on automatic recovery for most crashes. Keep innodb_force_recovery as an emergency option, not a first step. Invest in monitoring to catch memory and disk issues before they cause crashes. This suits production web applications where uptime matters more than immediate diagnosis.

For legacy MyISAM environments, allocate time for manual repairs and plan migration to InnoDB. Keep offline backups of table files before each repair. MyISAM's simpler structure makes corruption easier to diagnose but harder to recover from. This works for low-traffic sites where scheduled maintenance is acceptable.

For mixed environments, prioritize InnoDB migration while maintaining MyISAM repair skills. Use separate buffer pools and cache settings for each engine. Monitor both engine types differently since their failure modes differ. This applies to inherited systems during gradual modernization.

For critical production systems, implement high availability with replication or Galera Cluster. Crashes become failover events rather than recovery emergencies. Focus prevention efforts on monitoring and capacity planning rather than repair procedures. This fits e-commerce, SaaS platforms, and services with strict SLA requirements.