Archive Large MySQL Tables Without Downtime

Managing rapidly growing MySQL tables is crucial for maintaining database performance and controlling storage costs. This article explores automated strategies to safely archive historical data from large MySQL tables without causing application downtime or locking production databases. We will cover industry-standard techniques, including partition rotation, utility tools like pt-archiver, and scripted batch migrations, enabling you to keep your production environment running at peak performance.

1. Database Partitioning and Partition Rotation

Table partitioning physically divides a single logical table into multiple self-contained segments based on a specified column, usually a timestamp or auto-incrementing ID.

• How it works: By utilizing RANGE partitioning, MySQL automatically directs writes to the appropriate active partition.
• The Archiving Process: Dropping a partition (ALTER TABLE ... DROP PARTITION) is an instantaneous DDL operation that frees up disk space immediately without scanning or locking rows. Alternatively, you can use ALTER TABLE ... EXCHANGE PARTITION to swap an old partition with a standalone table, which can then be exported or backed up.
• Automation: You can write a stored procedure paired with the MySQL Event Scheduler to automatically create new partitions ahead of time and drop or exchange partitions older than a specific retention period (e.g., older than 90 days).

2. Utilizing Percona Toolkit’s pt-archiver

For tables that cannot be partitioned, Percona’s pt-archiver is the gold standard command-line utility for moving old data safely.

• How it works: It selects rows in small, configurable chunks, inserts them into an archive table (or writes them to a file), and then deletes them from the source table.
• Preventing Downtime: The tool is designed to work online. It uses low-priority queries, can pause if replica lag exceeds a certain threshold, and sleeps between chunks to allow the primary database to handle normal application traffic.
• Automation: Set up pt-archiver as a scheduled Cron job or a Kubernetes CronJob to run during off-peak hours daily or weekly.

3. Scheduled Chunk-Based Scripted Migrations

If third-party tools cannot be installed, you can build a custom, automated script (using Python, Go, or Node.js) to migrate data in small batches.

• How it works: Instead of running a massive DELETE statement—which locks the table, inflates the undo log, and causes replication lag—the script performs operations in small batches (e.g., 1,000 to 5,000 rows at a time).
• The Pattern:
  1. Identify the primary key range of the expired data.
  2. Copy the batch to the archive table: INSERT INTO archive_table SELECT * FROM source_table WHERE id BETWEEN x AND y;
  3. Delete the batch from the source table: DELETE FROM source_table WHERE id BETWEEN x AND y;
  4. Introduce a short sleep interval (e.g., 50–100 milliseconds) between batches to prevent CPU spikes and lock contention.
• Automation: Deploy this script using workflow orchestrators like Apache Airflow, temporal.io, or standard system schedulers to ensure reliable, monitored execution.

4. Trigger-Based Shadow Tables

For highly sensitive environments, you can use a trigger-based approach to duplicate write operations to an archive structure while slowly draining the primary table.

• How it works: You create temporary database triggers (AFTER INSERT, AFTER UPDATE, AFTER DELETE) on the active table that sync changes to a secondary table structure.
• The Archiving Process: Once the background sync process catches up to the desired retention threshold, you perform an atomic table rename (RENAME TABLE active TO old, shadow TO active). The older data can then be pruned from the offline table without affecting live queries.
• Automation: This is typically managed via automated deployment pipelines or specialized orchestration tools (such as gh-ost or pt-online-schema-change logic customized for archiving).