Back to Scenarios

Data Deleted by Mistake?

Recover Deleted Records

Scenario Description

A DELETE used an incorrect or missing condition and committed without a directly restorable backup. If the corresponding ROW Binlog, complete transactions and matching definitions survive, analyze pre-delete row images and generate recovery SQL.

Possible Causes

  • A DELETE condition was missing or too broad.
  • A batch job or application deleted the wrong rows.
  • The transaction committed and cannot be rolled back directly.

Challenges

Conventional recovery often requires complete files, a running instance or a usable backup. In these situations, choose a method based on the evidence that survives.

Common Errors and Symptoms

  • - Target rows are missing and business queries are incomplete.
  • - Logs contain DELETE row events for the target table.
  • - Recovery must select actual committed transactions or time ranges.

Why Traditional Methods Fail

  • - ROLLBACK cannot undo the deletion after commit.
  • - Restoring an entire backup may overwrite legitimate subsequent changes.
  • - Missing complete row images or log chains prevent complete recovery data.

PDU Solution

PDU scans a ROW Binlog copy that is no longer being written and records committed XIDs, row images and log positions. After you confirm the deletion range, restore del generates recovery SQL for selected transactions or the scanned range. You decide whether to import the results.

Recovery Principle

  1. Preserve Binlog and Definitions
  2. Scan DELETE Events
  3. Select Committed Transactions
  4. Generate Recovery SQL

PDU Core Capabilities

  • Check log CRC and rotation continuity.
  • Read DELETE row images with matching definitions.
  • Use committed XIDs reported by the scan.
  • Select by transaction or UTC commit time.
  • Retain reasons for missing images and incomplete transactions.
  • Generate SQL without writing to the business database automatically.

Recovery Steps

  1. 1

    Preserve the Data Files and Binlog Source

    Copy complete numbered logs, the .index list, data directory and table definitions. Logs must be closed; confirm CRC checks, rotation continuity and transaction completeness before scanning.

  2. 2

    Configure pdu.ini

    If only one table definition remains, use param ddl to specify a matching CREATE TABLE file.

  3. 3

    Start PDU, Initialize, and Check Binlog

    Finish the Binlog Check Before Scanning

    When logs or required row images are missing, retain errors and obtain the missing evidence. Do not change parameters to bypass missing sources.

  4. 4

    Execute the Scan Command

    Use the corresponding command example below, replacing paths, objects and identity parameters with evidence from this run.

  5. 5

    Understanding the Scan Output

    The TxID reported by this scan is the committed XID. Preserve log ranges, schema versions and native counts; do not confuse it with an InnoDB physical transaction ID.

  6. 6

    Restore Deleted Data

    Use restore del all; to export the scanned range, or replace all with an actual TxID reported by the scan.

    Important

    Recovery depends on preserved row images and schema. Automatic rollback of arbitrary transactions across incompatible DDL is incomplete; column migration does not make every DDL change reversible.

  7. 7

    Restore Specific Transaction

    Replace the example ID with an actual TxID.

  8. 8

    Recover by Time Range or Target Historical Time

    Time filtering uses UTC commit time and retains complete transactions. Historical snapshots use resmode history, restype all, ENDPOS and the DDL for the target time. They require continuous ROW logs with an observed CREATE/TRUNCATE starting point. Replace the example ranges and positions with actual source values.

Quick Command Examples

Preserve frozen sources and launch PDU outside them. Write configuration snippets to pdu.ini and execute PDU commands after startup. Replace example paths, versions, times and task identities with actual evidence.

# Configure pdu.ini

MYSQL_DATADIR=/recovery-input/mysql/data
BINLOG=/recovery-input/mysql/binlog
RESMODE=tx
RESTYPE=delete

# Start PDU, Initialize, and Check Binlog

b;
use appdb;
param binlog /recovery-input/mysql/binlog;
ckbinlog;

# Execute the Scan Command

param restype delete;
param resmode tx;
scan orders;

# Restore Deleted Data

restore del all;

# Restore Specific Transaction

restore del 12345;

# Recover by Time Range or Target Historical Time

param resmode time;
param starttime "2026-09-08 00:00:00";
param endtime "2026-09-09 00:00:00";
scan orders;

Export Results

Recovery SQL

Saved by table and recovery type in the directory where PDU was started. Use the filenames reported by PDU.

Transactions and Sources

Retain scanned transactions, selected ranges, row-image evidence and original errors.

Tasks and Checkpoints

.pdu-work retains native task state; recovery SQL is not executed automatically.

After export, record PDU’s native success and failure counts and original errors, retaining checkpoints. Resume uses native records and file metadata without rereading exported data or automatic trial import.

Prerequisites

  • Preserve complete Binlog covering the incident range and matching table definitions.
  • Old images, transaction boundaries and required fields must be evidenced by logs and observed row history.
  • Time parameters use UTC commit time with an inclusive start and exclusive end.
  • Prepare a matching program, license and sufficient space. Keep original evidence read-only and place the PDU launch directory outside the sources.

Important Notes

  • ROW logs and required row images must survive; disabled or purged logs cannot be recreated.
  • Arbitrary transactions spanning incompatible DDL cannot be reversed as one recovery.
  • restore del all covers the scanned range rather than every historical deletion in the instance.
View Detailed Steps for This Recovery Method
MySQL 场景案例