Back to Scenarios

Need a Table’s Data at an Earlier Point?

Recover a Historical Binlog Snapshot

Scenario Description

A complete table at an earlier endpoint is needed rather than reversal of one DELETE or UPDATE. Continuous ROW Binlog covers an observed CREATE/TRUNCATE start and the desired endpoint, with matching endpoint definitions. History mode can generate a single-table snapshot.

Possible Causes

  • The entire table before an operation must be retrieved.
  • Normal backups do not correspond to the required endpoint.
  • Recovery must stop at a definite log-event boundary.

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

  • - Unchanged rows are needed as well as recovery events.
  • - Logs have a definite start and definitions match the endpoint.
  • - The endpoint is established by an actual ENDPOS event boundary.

Why Traditional Methods Fail

  • - Selecting one transaction cannot reconstruct the whole table at the endpoint.
  • - Missing the start or intermediate logs makes history incomplete.
  • - Time or GTID selection cannot replace the complete history required by this mode.

PDU Solution

PDU's history mode reconstructs table data from continuous ROW logs up to a specified event end position. Keep these results separate from transaction reversals. Use DDL matching that position, then execute recovery SQL in an isolated empty table if needed.

Recovery Principle

  1. Confirm Complete Log Chain
  2. Bind Endpoint DDL
  3. Scan to Selected Endpoint
  4. Generate Historical Snapshot

PDU Core Capabilities

  • Recover a single table from complete observable history.
  • Specify first and last logs and the endpoint explicitly.
  • Require an observed CREATE/TRUNCATE start.
  • Interpret rows using endpoint-matching definitions.
  • Generate separate historical-data SQL.
  • Do not import recovery SQL into the target automatically.

Recovery Steps

  1. 1

    Initialization

    Log names and positions are examples. The stopping point must be an event end and must not split a transaction. Current table definitions cannot replace the DDL for the target historical time.

  2. 2

    Scan Complete History

    This method handles one table and requires a specified end position and complete observable history. It does not allow time/GTID filters or selection of just one TxID. PDU records the original causes of unknown origins, missing affected rows and conflicting row images.

  3. 3

    Export the Historical Snapshot

    If the original table later undergoes DROP, TRUNCATE or schema changes, the endpoint must precede that operation with complete evidence to recover the observable row set. Unproven conversions and missing values remain rejected. Arbitrary DDL is not automatically reversed.

  4. 4

    Using Recovered Data

    Prepare an isolated empty table matching the historical definition, avoiding triggers, foreign keys and concurrent writes. Generated SQL does not create or delete business tables and rejects a nonempty target. PDU does not connect to the target to execute it.

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.

# Initialization

param ddl /recovery-input/endpoint-schema.sql;
param binlog /recovery-input/mysql-bin.index;
param startbinlog mysql-bin.000005;
param endbinlog mysql-bin.000008;
param endpos <actual_event_end>;
use appdb;

# Scan Complete History

param resmode history;
param restype all;
param restoretarget existing;
ckbinlog;
scan orders;

# Export the Historical Snapshot

restore hist all;

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

  • Complete continuous ROW logs, an explicit primary key, DDL matching the target time, and an actual endpos.
  • An observed CREATE/TRUNCATE empty-table origin is required. A historical schema seed alone does not prove an empty table.
  • Prepare a matching program, license and sufficient space. Keep original evidence read-only and place the PDU launch directory outside the sources.

Important Notes

  • This path provides a single-table snapshot rather than arbitrary database-wide point-in-time recovery.
  • Do not substitute time/GTID filters or a selected transaction for complete history.
  • Use an isolated empty table matching the endpoint layout rather than overwriting the current business table.
View Detailed Steps for This Recovery Method
MySQL 场景案例