Back to Scenarios

Data Updated by Mistake?

Recover Updated Records

Scenario Description

An UPDATE used an incorrect or missing condition, changed multiple fields and committed. Recovery should retrieve original values while respecting legitimate later changes. It requires corresponding before and after row images rather than simply restoring the entire database from an older backup.

Possible Causes

  • An UPDATE condition was missing or targeted the wrong table.
  • A batch job assigned incorrect values to multiple fields.
  • Legitimate business writes continued after the incident.

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

  • - Original values were overwritten while the rows still exist.
  • - Binlog retains before and after images for the UPDATE.
  • - The same row may have several updates, requiring transaction ordering.

Why Traditional Methods Fail

  • - An ordinary ROLLBACK cannot reverse a committed update.
  • - Writing old values by primary key alone may overwrite legitimate later changes.
  • - Multi-column updates, duplicate matches and after-image conflicts require handling.

PDU Solution

PDU uses matching table definitions to analyze UPDATE before and after images, then generates recovery SQL in reverse operation order. Execution checks for conflicts: if current data differs from an after image or a record matches several rows, the entire batch is rejected.

Recovery Principle

  1. Preserve Before and After Images
  2. Scan Update Transactions
  3. Confirm Recovery Scope
  4. Generate Reverse-order SQL

PDU Core Capabilities

  • Rescan after setting RESTYPE to update.
  • Interpret row images with matching historical definitions.
  • Retain transaction order across repeated updates.
  • Generate reverse recovery for multiple fields.
  • After-image conflicts and multiple matches reject the complete batch.
  • Export recovery SQL for execution at the user’s discretion.

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

    Switch Recovery Type to Update

    UPDATE recovery uses before and after images and generates recovery SQL in reverse order. The generated recovery procedure rejects the complete batch when after images conflict or match multiple rows.

  5. 5

    Execute the Scan Command

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

  6. 6

    Restore Updated Data

    Use restore upd 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

    Transaction Mode for Precise Recovery

    Switch to transaction mode and scan again, then use the reported TxID to recover a selected 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=update

# Start PDU, Initialize, and Check Binlog

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

# Switch Recovery Type to Update

param restype update;

# Execute the Scan Command

param restype update;
param resmode tx;
scan orders;

# Restore Updated Data

restore upd all;

# Transaction Mode for Precise Recovery

param resmode tx;
scan orders;
restore upd 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

  • Without required before images, original values cannot be claimed as recovered.
  • Conflict rejection does not mean data was restored; record SQL export and target execution separately.
  • Time selection uses UTC commit time, a start-inclusive/end-exclusive interval and complete transactions.
View Detailed Steps for This Recovery Method
MySQL 场景案例