MySQL point-in-time recovery needs a consistent backup, its correct binary-log starting boundary and every required event through the chosen stopping boundary. Establish those three facts before replaying anything. A recent dump plus a directory of binary logs is not automatically a coherent recovery set.1, 3, 4
This guide uses MySQL 8.4 and a logical full-backup design. It explains the recovery contract and evidence, not an executable restore pipeline. No dump, binary-log replay or database test was performed. MariaDB and other MySQL-compatible products require their own version-specific assessment.
Start with the backup's actual consistency
Record the source server identity/version, capture tool/version, table engines, dump options, completion result and intended database scope. Keep the dump's metadata and warnings with the artifact. Do not infer consistency merely because the file can be read.
For mysqldump --single-transaction, MySQL documents a consistent transactional view for InnoDB. It does not provide that same guarantee for MyISAM or MEMORY tables changing during the dump. The reference also warns against specified concurrent DDL, including table alteration, creation, deletion, renaming and truncation, because those operations can invalidate the dump's contents or coordinates.2
Therefore, an application using mixed engines needs a backup design that accounts for each one. A successful InnoDB snapshot does not make separately changing nontransactional data consistent with it. Likewise, a deployment that changes schema during capture belongs in the consistency assessment.
Avoid describing a dump as completely nonblocking. When coordinate capture is combined with --source-data and --single-transaction, the documented behaviour includes a short global read lock at the beginning. Privilege requirements also depend on options and GTID state.2 Those operational effects need review before a real capture, although no capture is requested here.
Preserve the starting coordinate with the backup
The recovery start must come from the backup method's recorded boundary. Taking an unrelated status reading later cannot establish the position corresponding to the earlier consistent data snapshot.
MySQL's --source-data option records binary-log filename and position in a CHANGE REPLICATION SOURCE TO statement in the dump. With value 2, that statement is commented and informational; with value 1, it is active when the dump is loaded.2 That distinction matters when the same artifact is used for recovery rather than replica provisioning.
Inspect the actual dump and options instead of assuming the default was harmless. The recovery plan should explicitly identify whether loading the artifact will alter replication configuration, GTID state or binary-logging behaviour.
MySQL's event-position recovery guide warns that the log position displayed by InnoDB after restore/startup is not a reliable substitute for the recovery tool's boundary, because DDL or non-InnoDB changes may occur outside what that value reflects.4 Use the documented backup metadata and completed replay boundary, not a convenient startup log line.
Keep GTID state distinct from file positions
A global transaction identifier, or GTID, identifies a transaction in the replication history. A binary-log filename/position identifies a location in a particular log sequence. Both can be relevant, but they are not interchangeable labels for the same recovery plan.
mysqldump --set-gtid-purged controls inclusion of GTID-state statements and also affects whether the dump includes a session statement disabling binary logging during load. The default AUTO behaviour depends on the source's GTID configuration.2
That means loading a dump can affect the destination's transaction-history state in addition to recreating data. Have the DBA inspect the generated statements, target GTID history and intended replication role before restoration. Do not remove a GTID-related error by resetting history or editing statements without understanding why they conflict.
A partial database dump is particularly easy to overinterpret. Its data scope and the server's recorded GTID history need explicit review; the presence of GTID metadata is not proof that every transaction represented by that history has corresponding application data in a selected subset.
This article deliberately withholds GTID-reset, skip and purging commands. Their consequences extend beyond the immediate restore and can affect downstream replicas or later recovery decisions.
Verify binary-log coverage
Confirm binary logging was enabled during the interval to recover. MySQL 8.4 enables it by default, but the actual server configuration and retained files are the evidence that matters.3
Inventory the complete sequence from the backup's boundary to the target. Record server identity, filenames, positions and any encryption/access dependencies of the storage method. A filename alone does not prove the file came from the right server or that retention preserved all required predecessors.
Distinguish files retrieved from the actual recovery archive from files still available only on the production server. A design intended to survive server loss needs access independent of that server. Do not claim that a replica is a complete archive without checking what it actually received and retained.
Keep originals unchanged while inspecting. Binary-log output can contain sensitive statements and data, so store diagnostic extracts with backup-level access controls. A support screenshot should not expose customer values merely to show an event boundary.
Find the stopping event precisely
Start from the business incident time and independent application evidence, then locate the relevant events in the binary log. MySQL recommends datetime options for finding the area of interest, but advises using exact event positions rather than those datetime options to define the segment applied during recovery.4
Time-zone interpretation, clock differences and multiple transactions near the same time make “just before 10:30” an incomplete instruction. Record the exact file and boundary, whether the unwanted transaction is excluded, and the last expected good business operation.
Do not cut an arbitrary section out of decoded output and assume it remains a valid transaction sequence. Preserve transaction and session context. MySQL also recommends using a single connection when applying multiple binary-log files, rather than replaying each independently through unrelated sessions.3
This guide supplies no pipe into the mysql client. Such a pipeline executes database changes. Inspection and replay must remain visibly separate steps in the approved runbook, with the destination identity checked before any execution.
Build the recovery decision record
The runbook should identify:
| Item | Required evidence |
|---|---|
| Base backup | Consistency method, scope, tool version and completed artifact. |
| Start boundary | Backup-associated file/position and reviewed GTID context. |
| Replay interval | Complete retained files and exact selected transaction boundary. |
| Destination | Isolated instance, supported version and deliberate replication/logging state. |
| Acceptance | Known records, schema/routine dependencies and external reconciliation. |
Include stored programmes, triggers, events and other required objects in the backup-scope review. Their inclusion depends on the chosen capture options.2 A table-only recovery may be adequate for a data-extraction task but cannot be presented as full application restoration without those dependencies.
Rehearse without repeating production effects
Use a disposable destination and synthetic records before, at and after the intended boundary. The rehearsal should prove that the base and logs join without a gap or duplicate application and that the selected bad operation is excluded as intended.
Keep schedulers and external integrations controlled until the recovered copy is accepted. Replaying SQL cannot undo a payment or message already accepted elsewhere. The application owner must reconcile those effects independently.
Retain the full restore output and error status. A server that starts or a table that returns rows is not enough; inspect representative application reads and writes, required objects and data relationships. No such runtime evidence exists for this article.
If a required binary log is missing, state the last demonstrated recovery point. A new backup can improve future protection but cannot reconstruct an unavailable historical interval. Do not quietly skip the missing events and label the result point-in-time recovery.
Learning notes
- Backup consistency and replay coordinates must describe the same capture boundary.
- GTID state is part of the recovery design, not an error to reset away.
- Exact event positions and application acceptance provide stronger evidence than a timestamp or successful process exit.
Acceptance checklist
- [ ] Backup consistency and replay coordinates describe the same capture boundary.
- [ ] The required binary-log sequence is independently retained and readable.
- [ ] GTID state and the exact replay endpoint are reviewed before recovery.
- [ ] Isolated recovery validates business records and reconciles transactions around the target boundary.
References
- MySQL :: MySQL 8.4 Reference Manual :: 9.5 Point-in-Time (Incremental) Recovery. Accessed 2026-11-01.
- MySQL :: MySQL 8.4 Reference Manual :: 6.5.4 mysqldump — A Database Backup Program. Accessed 2026-11-01.
- MySQL :: MySQL 8.4 Reference Manual :: 9.5.1 Point-in-Time Recovery Using Binary Log. Accessed 2026-11-01.
- MySQL :: MySQL 8.4 Reference Manual :: 9.5.2 Point-in-Time Recovery Using Event Positions. Accessed 2026-11-01.