Understanding Aurora MySQL Backtrack

9 min readMay 23, 2026

Introduction

You execute a query against your production database and realize it immediately. No WHERE clause. DELETE FROM orders just wiped every row. Or suppose you accidentally dropped the wrong table.

The traditional recovery option — Point-in-Time Restore (PITR) — spins up a new DB cluster and restores from backup, which can take hours depending on data volume.

Aurora MySQL has a feature called Backtrack that can resolve this situation in minutes. It rewinds the existing cluster in place — no new cluster, no backup deployment.

I built this hands-on to verify how Backtrack actually behaves, and I’m sharing the results here for anyone who hasn’t used it before.

What is Aurora Backtrack?

Backtrack “rewinds” an Aurora MySQL DB cluster to a specific point in time. Unlike restoring from a backup, the existing cluster itself goes back in time.

Comparison with PITR:

PITR: launches a new cluster and restores from backup → can take hours depending on data volume
Backtrack: rewinds the existing cluster → minutes

You can also move forward and backward repeatedly. If you want to pinpoint when data corruption occurred, you can go back three hours, forward one hour, and so on.

Supported engine: Aurora MySQL only (Aurora PostgreSQL is not supported)
Supported versions: Aurora MySQL 2, 3, 8.4
Maximum lookback: Up to 72 hours.

What is PITR? (Reference)

PITR is a standard Aurora feature (both MySQL and PostgreSQL) that restores the database to any point in time using automated backups and transaction logs. Triggered from the console via Actions → Restore to point in time, it always launches as a new cluster.

The Backup retention period (1–35 days) in the Backup section of the cluster creation screen determines how far back PITR can go. With 7 days set, you can restore to any point within the past 7 days.

How It Works: Why It’s Faster Than Backup Restore

Aurora MySQL uses a distributed, log-structured storage system. Every change to the database is recorded as a new log record identified by a Log Sequence Number (LSN).

Enabling Backtrack provisions a FIFO buffer in the cluster for storing LSNs. This allows the cluster to retain change records while enabling fast access and rollback.

Understanding the Backtrack Window

There are two types of windows with Backtrack.

Target Backtrack Window

The amount of time you want to be able to backtrack. Setting 24 hours means you’re targeting the ability to rewind up to one day. Maximum: 72 hours.

Actual Backtrack Window

The actual amount of time you can backtrack, which can be smaller than the target. Under heavy write workloads, the change record storage fills up before the target window is reached.

Pricing

Backtrack pricing is hourly pay-as-you-go based on change record storage, billed separately from instance costs.

Unit price (as of May 2026): $0.000000014/CR-Hr (Tokyo region)

Two factors determine cost:

Target Backtrack Window length: longer windows retain each record longer
Write workload volume: more statements generate more change records

The formula used by AWS Pricing Calculator:

Monthly cost (USD) = statements/sec × 3,600 × 730
                   × change records per statement
                   × window (hours)
                   × $0.000000014

The default value for change records per statement is 0.38 (Pricing Calculator default). SELECT statements generate no change records, so a higher write ratio increases this value.

Cost Estimates (Tokyo region, CR/statement = 0.38)

Light workload (10 stmt/sec, 24h window)

Statements/month: 10 × 3,600 × 730 = 26,280,000
Monthly cost: 26,280,000 × 0.38 × 24 × $0.000000014 = $3.36

Medium workload (100 stmt/sec, 24h window)

Statements/month: 100 × 3,600 × 730 = 262,800,000
Monthly cost: 262,800,000 × 0.38 × 24 × $0.000000014 = $33.55

Heavy workload (1,000 stmt/sec, 24h window)

Statements/month: 1,000 × 3,600 × 730 = 2,628,000,000
Monthly cost: 2,628,000,000 × 0.38 × 24 × $0.000000014 = $335.54

Extending the window from 24 hours to 72 hours (the maximum) triples the cost. For a medium workload with a 72-hour window, that’s $100.65/month.

You can estimate your own environment at AWS Pricing Calculator. Under Amazon Aurora MySQL-Compatible → Backtrack, enter Average statements and Target Backtrack window.

Monitoring with CloudWatch

Searching “Backtrack” in the CloudWatch section of the console shows four metrics.

BacktrackChangeRecordsCreationRate — Number of change records created over 5 minutes. Useful for estimating cost against your target backtrack window.
BacktrackChangeRecordsStored — Actual number of change records in use by the DB cluster (billed).
BacktrackWindowActual — Shows whether there is a difference between the target and actual backtrack window. For example, if target is 120 minutes and this metric shows 100 minutes, the actual window is smaller than the target.
BacktrackWindowAlert — How often the actual window has been smaller than the target window for a given period.

Note: BacktrackChangeRecordsCreationRate and BacktrackChangeRecordsStored were not visible immediately after cluster creation in my verification, and appeared after some time.

Key Constraint: Must Be Enabled at Cluster Creation

This is the most critical operational constraint with Backtrack.

Backtrack must be enabled at cluster creation time. You cannot enable it on an existing cluster after the fact.

Get Yoshiyuki Watanabe’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

What you can do on an existing cluster:

Change the Target Backtrack Window on a Backtrack-enabled cluster
Disable Backtrack by setting the window to 0 (but once disabled, re-enabling is not possible)

If you want to use Backtrack, you need to enable it when creating a new cluster or when restoring from a snapshot.

Configuration

Enabling at Cluster Creation in the Console

When creating a new Aurora MySQL cluster, select “Enable Backtrack” in the Backtrack section and set the Target Backtrack Window.

Since workload is unknown at creation time, the console shows estimated costs based on typical workload. Actual costs vary with your workload.

Managing with Terraform

If you use Terraform, set the backtrack_window argument on the aws_rds_cluster resource. The value is in seconds, maximum 259200 (72 hours).

resource "aws_rds_cluster" "main" {
  # backtrack_window must be between 0 and 259200 (72 hours)
  backtrack_window = 86400
}

Note: Performing a backtrack (the rewind operation itself) is not supported as a Terraform resource. Terraform manages the steady state of infrastructure, and “rewind to a specific time right now” is an ephemeral operation outside its scope. Perform the actual rewind during an incident using the AWS CLI or console.

Hands-On: Recovery from Accidental Deletion

Here’s the actual flow of deleting data and recovering with Backtrack to verify it really works.

Step 1: Prepare test data

Create a table and insert verifiable data.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100),
  amount DECIMAL(10, 2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (customer_name, amount) VALUES
  ('Alice Johnson', 5000.00),
  ('Bob Smith', 12000.00),
  ('Carol Williams', 8500.00);

SELECT * FROM orders;

Step 2: Record the current timestamp

Note the current time just before the deletion. This is the time you’ll rewind to.

SELECT NOW();
-- Example: 2026-05-23 07:06:12

Step 3: Execute the DELETE without a WHERE clause

-- Intentional mistake
DELETE FROM orders;

SELECT * FROM orders;
-- Empty set (0 rows) — everything is gone

Step 4: Run Backtrack

Via the console:

Open “Databases” in the RDS console
Select the cluster (aurora-backtrack-demo)
Actions → Backtrack

Enter the timestamp from Step 2 (a few minutes before the DELETE) in the Date and Time fields, then click “Backtrack DB cluster”. The dialog also shows “Earliest restorable time” — confirm your target time falls within that range.

During execution, the database pauses, open connections are closed, and uncommitted reads and writes are discarded. Be careful not to run this at a time when the database cannot afford to go down.

For reference, via AWS CLI:

# When SELECT NOW() returned 2026-05-23 07:06:12
aws rds backtrack-db-cluster \
    --db-cluster-identifier aurora-backtrack-demo \
    --backtrack-to 2026-05-23T07:06:12+09:00

In my verification, the “Successfully backtracked” banner appeared and the status returned to Available in about 5 minutes.

Step 5: Confirm the data is restored

Once the cluster returns to available, reconnect and verify.

SELECT * FROM orders;

Existing connections are dropped during Backtrack. If a query is running when it disconnects, you’ll first see ERROR 2013 (HY000): Lost connection to MySQL server during query, then ERROR 2006 (HY000): MySQL server has gone away on the next SELECT → auto-reconnect.

The mysql client auto-reconnects, so running SELECT again will return results.

If all 3 records are back, the verification is complete.

You can review the Backtrack history (what time, when executed, status) via CLI. Console-based history viewing is not currently supported.

aws rds describe-db-cluster-backtracks \
    --db-cluster-identifier aurora-backtrack-demo

Output from my verification:

CLI output timestamps always return in UTC regardless of the DB timezone setting. BacktrackTo: 2026-05-22T22:06:12+00:00 is the same time as 2026-05-23T07:06:12+09:00 in JST — matching the time specified in Step 4.

Note that although the times matched in my verification, the time specified via the console or --backtrack-to in the AWS CLI and BacktrackTo may not always match exactly.

The official documentation states:

When you specify a time for a backtrack, Aurora automatically chooses the nearest possible consistent time. This approach means that the completed backtrack might not exactly match the time you specify

It appears Aurora automatically selects the nearest consistent point to the specified time.

Backtrack Limitations

Aurora MySQL only — Aurora PostgreSQL is not supported
Can only be enabled at cluster creation — cannot be added after the fact
72-hour maximum window
Applies to the entire cluster — you cannot selectively backtrack a single table or a specific update
Backtrack with binary logging enabled results in an error — binlog replication itself can be configured on a Backtrack-enabled cluster, but executing Backtrack while binlog is enabled will error (force is possible but breaks downstream read replicas and blue/green deployments)
Cross-region read replicas cannot be created (binlog-based replication can be configured)
Brief pause during execution — connections are closed, uncommitted reads and writes are discarded
Cannot rewind before clone creation time (the original cluster can rewind)
Cannot rewind before a major version upgrade (e.g., in-place upgrade from Aurora MySQL 2→3 cannot be backtracked across)

Summary

Aurora Backtrack is an Aurora MySQL-specific feature that enables recovery from human errors within 72 hours in minutes. Its LSN-based log design allows rewinding an existing cluster without launching a new one — fundamentally different from traditional PITR.

Cost scales with write workload and window length, so don’t underestimate it. Once disabled, you cannot re-enable Backtrack on the existing cluster — if you disable it to cut costs and later realize you need it again, rebuilding via snapshot restore (with downtime) is your only option.

Combined with the constraint that it can only be enabled at cluster creation, the strong recommendation is: decide whether you need Backtrack and whether you can accept the cost before creating a new Aurora MySQL cluster.

References

Thank you for reading this far!

Yoshiyuki Watanabe

Understanding Aurora MySQL Backtrack

Introduction

What is Aurora Backtrack?

What is PITR? (Reference)

How It Works: Why It’s Faster Than Backup Restore

Understanding the Backtrack Window

Pricing

Cost Estimates (Tokyo region, CR/statement = 0.38)

Monitoring with CloudWatch

Key Constraint: Must Be Enabled at Cluster Creation

Get Yoshiyuki Watanabe’s stories in your inbox

Configuration

Enabling at Cluster Creation in the Console

Managing with Terraform

Hands-On: Recovery from Accidental Deletion

Step 1: Prepare test data

Step 2: Record the current timestamp

Step 3: Execute the DELETE without a WHERE clause

Step 4: Run Backtrack

Step 5: Confirm the data is restored

Backtrack Limitations

Summary

References

Written by Yoshiyuki Watanabe

No responses yet

More from Yoshiyuki Watanabe

Auto-Generating AWS Architecture Diagrams with the draw.io MCP Server

Introduction

Why git clone Fails with “Password authentication is not supported” on AWS CloudShell

Introduction

Let Anthropic’s claude-code-setup Recommend Hooks and Skills for a Repo with Almost No Claude Code…

Introduction

Getting Started with DefectDojo: Managing SAST and DAST Findings in One Place

Unify SAST and DAST with DefectDojo to streamline vulnerability visibility and tracking — a strong first step in your security journey

Recommended from Medium

What Wealthy People Do Before 9 AM That Broke People Do Never

A five-year study tracked 233 millionaires and 128 people struggling financially. The differences weren’t income. They weren’t lucky. There…

How I Design Database Schemas That Scale — Normalisation, Denormalisation, and When to Break the…

Normalise until it hurts, denormalise until it works. A code-heavy, opinionated field guide to schema design — the decisions that determine…

Where does an INSERT go when your Postgres lives in S3?

The Lakebase paper (VLDB 2026), followed one transaction at a time.

How do movie ticket seat selection systems prevent overselling when 100,000 people are vying for…

System Design Deep Dive: Redis Lua Scripts, MySQL Optimistic Locking, Distributed Messaging, and Idempotency

OAuth and OIDC: The Flow You Should Use and the Token You Should Not Trust

Database Read Replicas Are Lying to Your Application: How to Handle Stale Reads

You create a user.

No responses yet

More from Yoshiyuki Watanabe

Auto-Generating AWS Architecture Diagrams with the draw.io MCP Server

Introduction

Why git clone Fails with “Password authentication is not supported” on AWS CloudShell

Introduction

Let Anthropic’s claude-code-setup Recommend Hooks and Skills for a Repo with Almost No Claude Code…

Introduction

Getting Started with DefectDojo: Managing SAST and DAST Findings in One Place

Unify SAST and DAST with DefectDojo to streamline vulnerability visibility and tracking — a strong first step in your security journey

Recommended from Medium

What Wealthy People Do Before 9 AM That Broke People Do Never

A five-year study tracked 233 millionaires and 128 people struggling financially. The differences weren’t income. They weren’t lucky. There…

How I Design Database Schemas That Scale — Normalisation, Denormalisation, and When to Break the…

Normalise until it hurts, denormalise until it works. A code-heavy, opinionated field guide to schema design — the decisions that determine…

Where does an INSERT go when your Postgres lives in S3?

The Lakebase paper (VLDB 2026), followed one transaction at a time.

How do movie ticket seat selection systems prevent overselling when 100,000 people are vying for…

System Design Deep Dive: Redis Lua Scripts, MySQL Optimistic Locking, Distributed Messaging, and Idempotency

OAuth and OIDC: The Flow You Should Use and the Token You Should Not Trust

Database Read Replicas Are Lying to Your Application: How to Handle Stale Reads

You create a user.