Do not make a deliberately divergent replica the only home for historical data. A replica is an availability mechanism, not an archive. Copy finalized rows into a separate archive database or storage tier, back that archive up independently, verify it, and only then remove rows from production.
Last updated: October 6, 2026.
INSERT INTO history.transactions
(id, account_id, status, amount, created_at)
SELECT id, account_id, status, amount, created_at
FROM app.transactions
WHERE created_at < '2026-07-01'
AND status IN ('SUCCESS', 'FAILED', 'CANCELLED');
SELECT COUNT(*) AS rows_to_archive,
SUM(amount) AS amount_to_archive,
MIN(id) AS first_id,
MAX(id) AS last_id
FROM app.transactions
WHERE created_at < '2026-07-01'
AND status IN ('SUCCESS', 'FAILED', 'CANCELLED');Run the same reconciliation query against the archive and compare its results before deleting anything. Use an immutable cutoff, record the batch in an audit table, and make the copy idempotent with a primary key or unique key on the archive table.
Separate retention from replication
Disabling binary logging for a partition drop can leave a replica with rows that no longer exist on its source. That topology is harder to rebuild, fail over, and reason about. A newly provisioned replica will not automatically regain the retained history. Keep the archive outside the normal failover set and give it its own backups, restore tests, access controls, and retention policy.
MySQL documents that dropping a partition deletes every row in it. Treat that operation as the final cleanup step, never as the archival mechanism.
Delete only after a verified recovery point
For a nonpartitioned table, delete in small primary-key ranges so transactions and replica lag remain bounded. For a table already partitioned by the retention date, a verified monthly partition can be dropped quickly. In either case, retain at least two recoverable copies of the archive and test a restore before the first production purge.
Direct historical queries to a read-only archive service instead of an operational replica. Monitor copy lag, reconciliation failures, archive capacity, and the age of the last successful backup. For related maintenance, see measuring MySQL table size, MySQL backup and restore, and moving a MySQL database with minimal downtime.
