Skip to main content

point-in-time recovery

Learning Focus

Use this lesson to understand point-in-time recovery with practical syntax and examples.

Concept Overview​

What is Point-in-Time Recovery?​

Point-in-Time Recovery (PITR) in MySQL is the process of restoring a database to the exact state it was at a specific moment in time. This is typically achieved by restoring a full database backup and then applying binary logs up to a desired timestamp.

Why is PITR Important?​

  • Minimizes Data Loss: Ideal when accidental deletions or erroneous updates occur.
  • Supports Disaster Recovery: Helps revert to a precise moment before a system failure.
  • Flexible Recovery Options: Allows recovery of partial data or specific time segments.

Where It Fits in Database Operations​

PITR is part of a broader backup and recovery strategy, which includes:

  • Full backups
  • Incremental backups
  • Binary logging
  • Automated recovery scripts

Basic Syntax & Rules​

Essential Requirements​

  1. Full Backup File (e.g., from mysqldump or mysqlbackup)
  2. Binary Logs Enabled (log_bin must be ON)
  3. Accurate Time or Log Position to Recover To

Enabling Binary Logging in my.cnf​

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW

Example Commands​

# Restore the full backup
mysql -u root -p < full_backup.sql

# Apply binary logs up to the target time
mysqlbinlog --stop-datetime="2025-07-01 14:30:00" mysql-bin.000001 | mysql -u root -p

Key Parameters​

  • -stop-datetime="YYYY-MM-DD HH:MM:SS": Stop log application at this time.
  • -start-datetime="...": (Optional) To narrow down log application.
  • -stop-position=N: Stop at a specific log position (advanced).

Limitations​

  • You must have binary logging enabled before the data loss.
  • PITR can only recover up to the timestamp of the last binary log.
  • Time is based on server time, ensure NTP sync.

Step-by-Step Examples​

Example 1: Basic PITR from Timestamp​

Scenario:​

A customer record was mistakenly deleted at 2025-07-01 14:35:00. We want to recover up to 14:30:00.

Step 1: Full Backup Before Deletion​

mysqldump -u root -p --all-databases > full_backup.sql

Step 2: Restore Full Backup​

mysql -u root -p < full_backup.sql

Step 3: Apply Binary Logs up to 14:30​

mysqlbinlog --stop-datetime="2025-07-01 14:30:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

Expected Output​

Query OK, 1 row affected
...
Restored up to 2025-07-01 14:30:00

Example 2: Recovery by Position (Advanced)​

mysqlbinlog --start-position=107 --stop-position=325 mysql-bin.000001 | mysql -u root -p

Practical Use Cases​

1. Accidental DELETE or UPDATE​

Restore data just before a faulty operation.

Example: Marketing team deletes 10k customer records. PITR brings data back without needing to restore an entire day's worth of transactions.

2. Logical Corruption​

When a bug in application logic introduces corrupt data.

3. Data Migration Errors​

Rolling back partial schema changes or failed imports.

4. Financial Reconciliation​

Restore sales database to a precise cut-off for audits.

5. Ransomware/Attacks​

If malicious commands are run, PITR can restore to just before the breach.

Common Mistakes & Troubleshooting​

Mistake 1: Binary Logs Not Enabled​

Fix: Edit my.cnf and restart MySQL:

[mysqld]
log_bin=mysql-bin

Mistake 2: Wrong Time Zone​

Fix: Always verify server time with:

SELECT NOW();

Mistake 3: Overwriting Good Data​

Fix: Restore to new schema or use staging before full merge.

Mistake 4: Skipping Log Integrity​

Fix: Always check binary log file sequence:

ls -lh /var/lib/mysql/mysql-bin.*

Best Practices​

  • Enable Binary Logging from Day 1.
  • Schedule daily full backups + retention of binary logs.
  • Store backups on separate storage or cloud.
  • Validate backups with periodic restore tests.
  • Always record server time for critical operations.

Hands-On Practice​

Setup Sample Data​

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  position VARCHAR(100)
);

INSERT INTO employees VALUES (1, 'Alice', 'Manager'), (2, 'Bob', 'Developer');

Exercise 1: Restore After Deletion (Easy)​

  1. Delete a row: DELETE FROM employees WHERE id=2;
  2. Note server time.
  3. Perform PITR to a minute before.

Solution Hint:

Use --stop-datetime for binary log replay.

Exercise 2: Rollback Faulty Update (Medium)​

UPDATE employees SET position='CEO' WHERE id=1;

Perform PITR to revert only that update.

Exercise 3: PITR to Separate Database (Advanced)​

  1. Restore backup to a new DB.
  2. Replay logs with filtered database using -database=company.

Command:

mysqlbinlog --database=company --stop-datetime="..." mysql-bin.000001 | mysql -u root -p new_db

Connection to Other Concepts​

Related ConceptHow It Connects
BackupsPITR relies on full backups.
Binary LoggingBinary logs are the foundation for PITR.
ReplicationBinary logs are also used in replication.
User ManagementUseful when recovering deleted users.
Data AuditingComplements auditing by enabling rollback.

What to Learn Next​

  • Replication & GTID
  • Incremental Backups
  • Automated PITR with Percona XtraBackup
  • Time-based Recovery in Cloud Environments

Concept Map​

flowchart LR
    A[Schema Context] --> B[point-in-time recovery]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls​

PitfallConsequencePrevention
Running destructive commands without contextDatabases or tables are dropped unexpectedlyConfirm target with SELECT DATABASE(); and run in a controlled environment first
Skipping backups before schema/data changesRecovery window becomes long or impossibleCreate a backup snapshot and test restore before production changes
Not validating privileges and locksOperations fail midway or block trafficCheck grants, active sessions, and maintenance window constraints

Quick Reference​

mysqlbinlog --start-datetime="2026-02-10 09:00:00" --stop-datetime="2026-02-10 09:30:00" binlog.000123 > recovery.sql
mysql -u root -p app_db < recovery.sql

What's Next​