Portable Recovery Toolbox: Lightweight MySQL Repair & Data Recovery Solutions

MySQL Recovery Toolbox: Step-by-Step Guide to Fix Corruption and Restore Data

Overview

A focused, practical guide that walks a MySQL administrator through diagnosing corruption, choosing recovery methods, and restoring data with minimal downtime. Covers storage-engine specifics (InnoDB, MyISAM), common corruption symptoms, recovery tools and commands, and prevention practices.

When to use this guide

• Database errors (crashes, I/O failures)
• Corrupted tables or indexes
• Lost or inconsistent rows after a crash
• Failed replication due to corrupted relay/master data
• When backups are missing or partial

Quick checklist (first 10 minutes)

1. Isolate: Stop application writes or put the DB in read-only mode.
2. Snapshot: Make a file-level copy or LVM snapshot of the data directory and binary logs.
3. Preserve logs: Secure MySQL error log and binary logs (binlog).
4. Document: Record MySQL version, OS, storage engine, and exact error messages.
5. Don’t run risky commands (DROP, REPAIR without backup) until snapshot confirmed.

Diagnosis

• Check MySQL error log for messages like “InnoDB: page corruption”, “Got error 145”, or “Table is marked as crashed”.
• Use SHOW ENGINE INNODB STATUS\G for InnoDB internals.
• For MyISAM, run CHECK TABLE table_name;
• For InnoDB, try SELECT COUNT(*) on suspicious tables (may fail).

Recovery techniques by engine

InnoDB

1. Try a clean shutdown (SERVICE mysql stop) and restart; InnoDB crash recovery may auto-fix.
2. If startup fails, start MySQL with:
   • innodb_force_recovery=1,2,… up to 6 (incrementally). Use lowest value that allows export.
   • Note: values ≥4 make InnoDB read-only; use only to dump data.
3. Once MySQL starts, use mysqldump to export affected databases/tables:
   • mysqldump –single-transaction –routines –triggers dbname > dump.sql
4. If dump succeeds, recreate a fresh MySQL instance and import dump.
5. If innodb_force_recovery cannot start server:
   • Use Percona Data Recovery Toolkit (undrop-for-innodb tools), or
   • Use page-level recovery: extract pages from ibdata/ibd with tools like innodb_ruby or undrop-for-innodb, then rebuild tablespace.
6. If using file-per-table (innodb_file_per_table=ON) and .ibd exists, try:
   • CREATE TABLE with same structure in fresh instance, DISCARD TABLESPACE, replace .ibd, then IMPORT TABLESPACE (requires matching metadata; use tools to rebuild .frm/.ibd if needed).

MyISAM

1. Run myisamchk on table files (.MYI/.MYD) from stopped server or with the engine offline:
   • myisamchk -r -q /path/to/table.MYI
2. Use myisamchk –safe-recover for complex cases.
3. If myisamchk fails, copy .MYD/.MYI to a safe place and attempt recovery on copies.
4. Consider using SELECT … INTO OUTFILE from a running server if table can be accessed read-only.

Recovering from missing binary logs or point-in-time needs

• If binlogs exist: use mysqlbinlog to replay events up to desired timestamp.
• If binlogs missing: rely on latest full backup plus logical exports; consider forensic log reconstruction only as last resort.

Restoring when backups exist

1. Restore latest full backup to a separate host.
2. Apply incremental backups or binary logs for point-in-time recovery.
3. Validate data integrity and application behavior before cutover.

Tools and commands (practical)

• mysqldump, mysqlpump
• Percona XtraBackup (hot physical backups for InnoDB)
• mydumper/myloader (fast logical backups)
• myisamchk (MyISAM repair)
• mysqlbinlog
• innodb_force_recovery (my.cnf or startup option)
• Percona Toolkit, innodb_ruby, undrop-for-innodb

Testing and validation

• Run CHECK TABLE and spot-check rows.
• Use checksum tools (pt-table-checksum) for replication consistency.
• Run application tests against restored data before making it live.

Prevention best practices

• Use Percona XtraBackup or regular logical backups.
• Enable binary logging with appropriate retention.
• Use replication with a delayed slave for quick recovery from accidental deletes.
• Monitor disk health and set up alerts on MySQL errors.
• Test restore procedures regularly and keep runbooks updated.

Example quick recovery flow (InnoDB)

1. Stop writes; snapshot data dir and binlogs.
2. Try normal restart.
3. If fails, set innodb_force_recovery=1 and restart; increase incrementally until it starts.
4. Dump databases with mysqldump.
5. Build fresh server, import dumps, reconfigure replication if needed.

If you want, I can produce a one-page printable runbook of these steps or a tailored recovery checklist for your MySQL version and environment.