How to Archive Old Data from MySQL Without Downtime

Start with a retention rule, not a DELETE

Your MySQL audit_events table contains years of history. The application normally reads recent events, but older records must remain available for investigations. Archiving can run alongside application traffic when selection, export and cleanup are bounded. “Without downtime” is an operating goal, not a guarantee: an unsuitable index, an overloaded replica or a large transaction can still disrupt requests.

Use an illustrative append-only table with a unique BIGINT id, an immutable UTC created_at and an event_type column. Choose a cutoff once for the run, identify holds and active references, and record exclusions. The examples use 1 January 2025 and a batch size of 1,000; neither is a recommended production default. Start on a disposable copy with representative data and workload.

Make the selection indexable

A (created_at, id) index is a candidate for a timestamp range ordered by a unique tie-breaker. Evaluate the actual plan, not just whether an index exists. Fetch only the fields the archive needs; a wide payload may require additional table reads even with a suitable index.

The first-batch query below is read-only. If EXPLAIN reveals an expensive plan, investigate before running a long export. Adding an index to a large table is its own operational change with resource and locking implications. Test that change separately rather than making schema creation an automatic step of the archival job.

SET @cutoff = '2025-01-01 00:00:00';
EXPLAIN
SELECT id, created_at, event_type
FROM audit_events
WHERE created_at < @cutoff
ORDER BY created_at, id
LIMIT 1000;

Resume from a key, not a growing OFFSET

For the next batch, persist both the timestamp and the identifier of the last completed row. Rows can share a timestamp; resuming on timestamp alone can skip their peers. A strict comparison against the compound cursor provides a deterministic continuation when the selection fields remain stable.

Persist the original cutoff alongside the cursor. Do not recompute it from the current clock after a restart. The example cursor is illustrative: replace it with the last key from a completed batch. A process crash must not advance this key merely because it fetched records into memory.

SET @last_time = '2024-06-01 12:00:00';
SET @last_id = 42000;
SELECT id, created_at, event_type
FROM audit_events
WHERE created_at < @cutoff
  AND (created_at > @last_time
       OR (created_at = @last_time AND id > @last_id))
ORDER BY created_at, id
LIMIT 1000;

Choose how the export sees concurrent changes

A fixed cutoff and a keyset cursor do not create a snapshot. An old event inserted behind the cursor can be missed; an update can move a row out of the selected range or change its exported values. Prefer a write-stable historical dataset where that assumption can be established. Otherwise use a suitable consistency mechanism and reconciliation before accepting the archive as complete.

InnoDB consistent reads depend on transaction isolation. A repeatable-read snapshot can support a consistent export, but keeping a transaction open for a long run has operational costs and cannot simply survive a lost connection. Separately committed batches may observe different source states. Document which model you use and what a restart means for completeness.

Keep transactions and export work bounded

Fetch and write manageable batches with backpressure rather than accumulating an entire table in memory. Observe application latency, source I/O, replication lag and export failures while adjusting pace. A replica may move read work away from the primary, but lag and consistency still need explicit treatment; do not infer a primary deletion decision from a stale export.

Separate a database read from remote upload time where the consistency model permits it. Record each batch’s bounds, identifiers, schema mapping and file locations. Test decimal precision, timestamp interpretation, unsigned values, nulls and binary columns through the reader used for retrieval.

• Why Parquet works well for database archives →

Make completion and checkpointing recoverable

Write an incomplete file under a temporary identity. Finish the write, confirm it is durable, then publish a manifest that identifies the completed batch. The journal must distinguish fetched, written, published and verified states. Remote objects and local journal updates are separate operations; retries need a way to recognize work already published.

The pseudocode describes the sequencing, not a ready-made exporter. Use deterministic batch identities and idempotent recovery. If a crash happens after publication but before checkpointing, inspect the existing manifest and verify it instead of blindly producing another copy.

batch = read_after(saved_cursor, saved_cutoff)
write_archive_to_temporary_location(batch)
make_archive_durable_and_publish_manifest(batch)
verify_files_ids_and_values(batch)
commit_completed_cursor(batch.last_key)
# Optional deletion has its own journal and authorization.

Verify the retained copy before any removal

Check that every expected batch completed, the manifest points to readable files, identifiers and counts match the selection, and representative values round-trip correctly. A file checksum can detect corruption; it cannot establish that the retention predicate was correct. Matching counts alone cannot detect a duplicated row replacing a missing one.

Test a historical query before approving cleanup. For mutable rows, include a source-version comparison or reconciliation that proves the archived version is still the version being removed. Verification and deletion are different journal states; a verified export is not an instruction to delete.

Delete the verified row set in separate, small operations

If cleanup is approved, use identifiers from verified batches, recheck eligibility and skip changed rows. Never rerun a broad date predicate and assume it selects precisely what was exported. Review foreign keys, triggers and cascading effects. Bound transaction size and stop on unexpected affected counts, lock waits, deadlocks or replica lag.

Keep deletion restartable independently of export. A successful deletion should be recorded against its verified batch, and a retry must distinguish already-absent rows from unexpected source changes. Removing rows can make InnoDB space reusable without reducing file size; file shrinking is a separate maintenance decision, not a guaranteed archival result.

Finish with a retrieval and recovery contract

An online archival design is complete only when it has a repeatable selection, bounded work, a durable journal, checked data and a tested retrieval path. Keep backups for recovering the operational database. An archive of old events will not recover the current state after a database failure.

ColdCTL implements PostgreSQL, MySQL 8.4 and MongoDB 8.0 archival through separate connectors, with local Parquet, S3 and Azure Blob destinations, verification and explicitly authorized source deletion. Its customer-hosted Archive Gateway supports browser retrieval and bounded downloads. Engine-specific limits and production qualification still apply. The design guide below explains the archival workflow; its SQL examples are general MySQL patterns, not ColdCTL CLI commands.

• MySQL archival architecture and current ColdCTL scope →
• Database archiving versus backup →