How to Recover from a Corrupted Database

Tudásbázis

Database corruption is stressful but often recoverable if you respond correctly. This guide covers diagnosing corruption and the recovery approaches available across major database systems.

Common Causes of Database Corruption

Unexpected power loss or hard shutdown during a write operation
Disk hardware failure or file system corruption
A bug in the database software itself (rare, but not impossible)
Manual manipulation of database files while the database process was running

Step 1 — Stop Making Changes Immediately

Don't attempt further writes to a suspected-corrupted database — additional operations can worsen the damage or overwrite data that might otherwise be recoverable.

Step 2 — Check for the Most Recent Good Backup First

Before attempting in-place repair, verify you have a recent backup (see How to Back Up and Restore MySQL/MariaDB Databases or the PostgreSQL equivalent) — restoring from a known-good backup is almost always safer and more reliable than attempting to repair corruption in place.

MySQL/MariaDB: Checking for Corruption

mysqlcheck -u root -p --all-databases --check

MySQL/MariaDB: Attempting Repair

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

Works for MyISAM tables reasonably well; InnoDB corruption often requires different approaches (see below), since InnoDB's repair mechanisms differ from MyISAM's.

MySQL/MariaDB: InnoDB Recovery Mode

innodb_force_recovery = 1

Add to configuration and restart — allows MySQL to start despite corruption, in a mode prioritizing data extraction over normal operation. Increase the value (up to 6) incrementally if lower values don't allow startup, but higher values risk further data loss; the goal is extracting data to a backup, then rebuilding.

PostgreSQL: Checking for Corruption

SELECT * FROM pg_catalog.pg_stat_database;

Look for unusual error counts; also check server logs for specific corruption-related error messages providing more diagnostic detail.

PostgreSQL: Using pg_dump to Salvage Data

pg_dump --data-only --no-owner mydb > salvage.sql

Even from a partially corrupted database, attempting a dump can sometimes salvage most data, skipping only genuinely unreadable portions.

MongoDB: Repair Command

mongod --repair --dbpath /var/lib/mongodb

Attempts to rebuild collections and indexes, discarding any unrecoverable data — always have a backup before attempting this, since it can result in data loss for genuinely corrupted portions.

Checking Underlying Disk Health

sudo smartctl -a /dev/sda

If corruption resulted from underlying disk issues, verify disk health before simply restoring data onto the same potentially-failing storage — recurring corruption on the same hardware suggests a deeper infrastructure problem.

After Recovery: Root Cause Analysis

Understand what caused the corruption (unexpected power loss, disk failure, and similar) and address the underlying cause — otherwise the same corruption risk remains present for the future.

Preventing Future Corruption

Ensure proper, graceful shutdown procedures (never abruptly power off a running database server)
Monitor disk health proactively
Maintain regular, verified-working backups as your primary safety net

Common Errors

Repair commands don't fully resolve the issue — if in-place repair genuinely isn't working, restoring from your most recent good backup is the more reliable path forward; don't spend excessive time on uncertain repair attempts when a clean backup restore is available.

Continue Reading

Browse more articles in Databases.

Tudásbázis

Kategóriák

Kategóriák