MySQL Database Corruption: Diagnosis & Repair Guide

MySQL database corruption can bring your application to a grinding halt. Whether you are running a production web application, an internal business system, or a development environment, a corrupted MySQL database demands immediate attention. This guide walks you through diagnosing corruption, repairing both InnoDB and MyISAM tables, and implementing preventive measures to keep your data safe.

MySQL corruption typically occurs when data written to disk does not match what the database engine expects to read back. The result can range from a single unreadable row to an entirely unmountable database.

Common Causes of MySQL Database Corruption

Understanding why corruption happens is the first step toward both fixing and preventing it. The most frequent causes include:

• Unexpected server shutdown — Power failures, kernel panics, or the OOM killer terminating mysqld mid-write leave data files in an inconsistent state.
• Disk hardware failures — Bad sectors, degraded RAID arrays, and worn-out SSDs can silently flip bits in your .ibd or .MYD files.
• Disk space exhaustion — When MySQL runs out of space during a write operation, partial pages get flushed to disk, corrupting table data or indexes.
• Improper process termination — Using kill -9 on mysqld instead of mysqladmin shutdown bypasses the clean shutdown sequence.
• File system corruption — Underlying ext4, XFS, or ZFS issues propagate upward into MySQL data files.
• MySQL bugs — Rare but real. Certain versions have known bugs that can corrupt specific table types under edge-case workloads.

Diagnosing MySQL Corruption

Before attempting any repair, confirm that corruption actually exists and identify which tables are affected.

CHECK TABLE

The simplest way to verify a specific table is to run CHECK TABLE from the MySQL client:

CHECK TABLE your_database.your_table;

The output will report one of several statuses: OK, Table is already up to date, or an error message describing the corruption. To check every table in a database:

-- Check all tables in the current database
CHECK TABLE table1, table2, table3;

mysqlcheck Utility

For bulk operations across multiple databases, mysqlcheck is more practical than running individual SQL statements:

# Check all tables in a specific database
mysqlcheck --check your_database -u root -p

# Check all tables across all databases
mysqlcheck --check --all-databases -u root -p

# Quick check (faster, less thorough)
mysqlcheck --quick --all-databases -u root -p

Reading the Error Log

MySQL logs corruption-related errors to its error log (typically /var/log/mysql/error.log or /var/log/mysqld.log). Look for messages like:

[ERROR] InnoDB: Database page corruption on disk or a failed file read
[ERROR] Table './mydb/orders' is marked as crashed and should be repaired
[ERROR] InnoDB: Page [page id: space=5, page number=12] log sequence number 123456 is in the future!

These entries pinpoint exactly which tables and pages are affected.

Repairing MyISAM Tables

MyISAM tables store data in .MYD (data) and .MYI (index) files. They are more prone to corruption than InnoDB because MyISAM does not use transactional logging.

REPAIR TABLE (Online)

If MySQL is running, use the REPAIR TABLE statement:

-- Standard repair
REPAIR TABLE your_database.crashed_table;

-- Extended repair (slower but more thorough)
REPAIR TABLE your_database.crashed_table EXTENDED;

-- Quick repair (only fixes index tree)
REPAIR TABLE your_database.crashed_table QUICK;

myisamchk (Offline)

For severe corruption or when MySQL cannot start, use myisamchk directly on the data files. Stop MySQL first to avoid conflicts:

# Stop MySQL
sudo systemctl stop mysql

# Check the table
myisamchk /var/lib/mysql/your_database/crashed_table.MYI

# Repair with recovery
myisamchk --recover /var/lib/mysql/your_database/crashed_table.MYI

# Safe recover (slower, handles more corruption types)
myisamchk --safe-recover /var/lib/mysql/your_database/crashed_table.MYI

# Force repair (last resort, may lose data)
myisamchk --force --recover /var/lib/mysql/your_database/crashed_table.MYI

# Restart MySQL
sudo systemctl start mysql

Important: Never run myisamchk while MySQL is running on the same data files. This will cause additional corruption.

mysqlcheck for Batch Repair

To repair all MyISAM tables across all databases in one command:

mysqlcheck --repair --all-databases -u root -p

Repairing InnoDB Tables

InnoDB is the default storage engine in modern MySQL. It uses a transactional architecture with redo logs, making it more resilient than MyISAM — but also more complex to repair when things go wrong.

Step 1: Try a Normal Restart

InnoDB has built-in crash recovery. After an unclean shutdown, simply restarting MySQL triggers automatic recovery using the redo log (ib_logfile0, ib_logfile1). Check the error log to see if recovery completes successfully.

Step 2: innodb_force_recovery

If MySQL refuses to start normally, use innodb_force_recovery to force it into a read-only diagnostic mode. Edit your MySQL configuration file (/etc/mysql/my.cnf or /etc/my.cnf):

[mysqld]
innodb_force_recovery = 1

Start with level 1 and increase only if MySQL still fails to start:

Level	Behavior
1	Skip corrupt pages during crash recovery (SRV_FORCE_IGNORE_CORRUPT)
2	Prevent background operations from running (SRV_FORCE_NO_BACKGROUND)
3	Skip transaction rollback after crash recovery (SRV_FORCE_NO_TRX_UNDO)
4	Prevent insert buffer merge operations (SRV_FORCE_NO_IBUF_MERGE)
5	Skip undo log processing during startup (SRV_FORCE_NO_UNDO_LOG_SCAN)
6	Prevent redo log roll-forward at startup (SRV_FORCE_NO_LOG_REDO)

Step 3: Dump and Reimport

Once MySQL starts in recovery mode, immediately dump all data:

# Dump all databases
mysqldump --all-databases --routines --triggers --events > full_backup.sql

# Or dump a specific database
mysqldump your_database > your_database_backup.sql

Then restore into a clean MySQL instance:

# Stop MySQL, remove old data
sudo systemctl stop mysql

# Remove innodb_force_recovery from my.cnf
# Then remove corrupted data files (back them up first!)
sudo mv /var/lib/mysql/ib_logfile* /tmp/mysql_backup/
sudo mv /var/lib/mysql/ibdata1 /tmp/mysql_backup/

# Reinitialize MySQL data directory
sudo mysqld --initialize-insecure --user=mysql

# Start MySQL and reimport
sudo systemctl start mysql
mysql -u root < full_backup.sql

Warning: Levels 4-6 are increasingly aggressive and may cause additional data loss. Always dump your data at the lowest recovery level that allows MySQL to start.

Prevention: Keeping Your MySQL Databases Healthy

Repairing corruption is stressful and time-consuming. A solid prevention strategy is far more cost-effective than any recovery effort.

Backup Strategy

• Automate daily backups using mysqldump, Percona XtraBackup, or MySQL Enterprise Backup.
• Verify backups regularly by restoring them to a test server and running CHECK TABLE on critical tables.
• Store backups off-site — a backup on the same disk as your database is not a real backup.

Hardware and Infrastructure

• Use ECC memory to prevent bit-flip errors from propagating into data files.
• Deploy UPS (Uninterruptible Power Supply) units to prevent unclean shutdowns during power outages.
• Monitor disk health with SMART tools (smartctl -a /dev/sda) and replace drives proactively.
• Use RAID with battery-backed write cache for production database servers.

MySQL Configuration Best Practices

[mysqld]
# Enable doublewrite buffer (default in MySQL 8.0+)
innodb_doublewrite = ON

# Flush logs at each transaction commit for maximum durability
innodb_flush_log_at_trx_commit = 1

# Use O_DIRECT to bypass OS cache and reduce double-buffering
innodb_flush_method = O_DIRECT

# Enable checksums to detect silent corruption
innodb_checksum_algorithm = crc32

# Sync binary log to disk at every commit
sync_binlog = 1

Operational Discipline

• Always use mysqladmin shutdown or systemctl stop mysql — never kill -9.
• Schedule weekly CHECK TABLE runs on critical tables via cron.
• Monitor disk space and set alerts at 80% usage to prevent space exhaustion.
• Keep MySQL updated to the latest stable release to benefit from bug fixes.

🙋 Frequently Asked Questions

Q: How do I know if my MySQL database is corrupted?

Common signs include: queries returning unexpected errors (Table is marked as crashed), mysqld crashing repeatedly, SELECT queries returning partial or garbled data, and SHOW TABLE STATUS showing non-zero Data_free or negative values. Run CHECK TABLE on suspected tables to confirm.

Q: Can a corrupted MySQL database be repaired?

Yes, in most cases. For MyISAM tables, use REPAIR TABLE or myisamchk. For InnoDB, use innodb_force_recovery mode to dump data, then reimport into a fresh database. Success rates are typically 80-95% for logical corruption and 50-70% for physical disk-level corruption.

Q: What causes MySQL database corruption?

Common causes include: unexpected server shutdown (power loss, OOM killer), disk hardware failures (bad sectors, SSD wear), running out of disk space during writes, MySQL bugs, OS-level file system corruption, and improper mysqld termination (kill -9).

📚 Related Articles

• Complete Database File Repair Guide — Covers SQL Server, MySQL, and Access repair in depth
• Understanding File Corruption Causes — Why files become corrupted and how to prevent it
• Complete File Recovery Guide — General strategies for recovering lost and damaged files
• Best File Repair Tools 2026 — Comprehensive tool comparison and recommendations
• Storage Device Repair Center — Hard drive, SSD, and media recovery guides

---

Need Help With Corrupted Files?

Magic Leopard™ specializes in advanced photo repair, but we also provide comprehensive file repair guides for databases, documents, and more. If you have corrupted images alongside your database issues, give our tool a try.

Try Magic Leopard™ Photo RepairBrowse All Guides