Efficient MySQL Large Table Archiving with a Rebuilt Percona pt-archiver

Background: In many applications, large tables continuously grow, eventually exhausting disk space. DBAs need to archive old data while keeping recent rows accessible.

Problem: Using Percona's pt-archiver to move historical rows one by one can be slow when millions of rows need deletion, especially when only a small recent subset (e.g., 7 days) must be retained.

Solution Overview: Reconstruct the archiving process by copying the desired recent data to a temporary table, swapping table names, and using triggers to keep the temporary table synchronized during the operation. This reduces lock time and avoids massive row‑by‑row deletions.

Prerequisites

The table must not have triggers, foreign keys, lack a primary key, or use a non‑ROW binlog format; otherwise the tool aborts.

Step‑by‑Step Procedure

Create an empty temporary table with the same structure as the original:

create table IF NOT EXISTS ${mysql_table}_tmp like ${mysql_table}

Define three triggers on the original table to mirror INSERT, UPDATE, and DELETE operations to the temporary table:

DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_insert;
CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_insert AFTER INSERT ON ${mysql_table}
FOR EACH ROW REPLACE INTO ${mysql_database}.${mysql_table}_tmp ($column) VALUES ($new_column);

DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_update;
CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_update AFTER UPDATE ON ${mysql_table}
FOR EACH ROW REPLACE INTO ${mysql_database}.${mysql_table}_tmp ($column) VALUES ($new_column);

DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_delete;
CREATE TRIGGER pt_archiver_${mysql_database}_${mysql_table}_delete AFTER DELETE ON ${mysql_table}
FOR EACH ROW DELETE IGNORE FROM ${mysql_database}.${mysql_table}_tmp WHERE id = OLD.id;

Batch‑copy the data that should be kept (e.g., the last 7 days) from the original table to the temporary table, using primary‑key ranges to limit lock time:

INSERT LOW_PRIORITY IGNORE INTO ${mysql_database}.${mysql_table}_tmp SELECT * FROM ${mysql_database}.${mysql_table} WHERE id >= ${begin_Id} AND id < ${begin_Id + $limit_chunk} LOCK IN SHARE MODE;

Swap the tables:

RENAME TABLE ${mysql_table} TO ${mysql_table}_bak, ${mysql_table}_tmp TO ${mysql_table};

Drop the three synchronization triggers:

DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_insert;
DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_update;
DROP TRIGGER IF EXISTS pt_archiver_${mysql_database}_${mysql_table}_delete;

Result: The original large table is replaced by a lightweight version containing only the recent data, while the archived rows remain in the backup table, which can be moved to another server or converted to a storage‑efficient engine such as TokuDB.

Safety Note: The script does not delete archived data from the original table; it only renames tables, so accidental data loss is avoided.

$mysql_server='10.10.159.31';
$mysql_username='admin';
$mysql_password='123456';
$mysql_database='test';
$mysql_port='3306';
$mysql_table='sbtest1';
$where_column='id>=99900000';
$limit_chunk='1000';  // rows per batch
$insert_sleep='1';    // seconds to sleep between batches