9 Proven MySQL Data Recovery Techniques Every DBA Should Know

DBAs often face MySQL data loss caused by accidental UPDATE/DELETE statements, server crashes, disk failures, or ineffective backups; the article presents nine recovery approaches for such scenarios.

Note: High‑risk operations should be tested before applying to production environments.

Tool 1: Full backup + binlog

If you have a complete backup and binary logs, you can restore data to any point in time, covering most loss cases; the procedure is straightforward and therefore omitted here.

Tool 2: Business‑logic reverse recovery for UPDATE mistakes

This method uses application logic to reconstruct the correct state after an erroneous UPDATE.

Example 1 – a developer missed the member_id condition, corrupting many rows. Because the table contains last_update_time, you can revert the change:

update t1 set status=1 where member_id=10001 and status=0;

update t1 set status=1 where status=0;

update t1 set status=0 where status=1 and last_update_time='2017-03-20 11:30:27';

Example 2 – a missing WHERE clause updated the whole table. After the mistake, retrieve the last known status from a historical recharge table and apply it back to the main table; this approach works for small‑scale recoveries.

Tool 3: MySQL flashback

Open‑source tools such as mysqlbinlog_flashback and binlog2sql parse binlog entries to reconstruct original UPDATE/DELETE statements, enabling rollback of erroneous operations.

Tool 4: innodb_force_recovery

When InnoDB files are corrupted and the server cannot start, adding innodb_force_recovery (values 1‑6) to [mysqld] forces a read‑only start, allowing you to dump data.

SRV_FORCE_IGNORE_CORRUPT – ignore corrupt pages.

SRV_FORCE_NO_BACKGROUND – stop background thread.

SRV_FORCE_NO_TRX_UNDO – skip transaction undo.

SRV_FORCE_NO_IBUF_MERGE – skip insert‑buffer merge.

SRV_FORCE_NO_UNDO_LOG_SCAN – treat uncommitted transactions as committed.

SRV_FORCE_NO_LOG_REDO – skip redo.

Increase the value gradually until MySQL starts, then dump the tables.

Tool 5: DISCARD / IMPORT TABLESPACE

Useful for repairing damaged .frm files or recovering from ibd corruption when a physical backup exists.

Scenario A – backup present, table not recreated

innobackupex --apply-log

cp test.ibd test.bak

alter table test discard tablespace;

cp /bak/test.ibd /data/test/; chown mysql:mysql /data/test/test.ibd

alter table test import tablespace;

Scenario B – table dropped but backup present

Recreate the table structure, then use Percona’s recovery tool to restore the original space ID.

innobackupex --apply-log

create table test(id int);

Stop MySQL.

Replace the new table's .ibd with the backup file.

~/percona-data-recovery-tool-for-innodb-0.5/ibdconnect -o /data/ibdata1 -f /data/test/test.ibd -d test -t test

~/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /data/ibdata1

Repeat step 6 until no output appears.

Start MySQL.

Tool 6: Manual ibd file modification

When only the .ibd file and table definition survive, create a matching table, edit the ibd header to align space and index IDs, and then dump the data.

Create the database and table structure.

Open the ibd file in binary mode: vim -b tmp.ibd then :%!xxd to view hex.

Adjust the header values to match the new table’s IDs.

Convert back: :%!xxd -r and save with :wq.

Replace the new table’s ibd with the modified file and set MySQL ownership.

Restart MySQL with innodb_force_recovery.

Dump the recovered data.

Tool 7: extundelete

A Linux file‑system utility that can recover deleted .ibd files as long as the underlying disk blocks have not been overwritten; it cannot recover from DML or TRUNCATE operations.

Tool 8: Percona Data Recovery Tool for InnoDB

Although no longer maintained, this tool reads raw InnoDB pages and extracts rows into CSV‑like files given the table schema; the output may require cleaning before re‑import.

These eight (nine) techniques form a comprehensive toolbox for MySQL data recovery in various failure scenarios.