Best Practice to Archive Old Data in MySQL

With InnoDB and PARTITIONs, you could set up daily PARTITION BY RANGE(TO_DAY(...)) and use "Transportable tablespaces" to disassociate a day from the table and move it separately. This will be a lot faster than the queries it would take to do the INSERT...SELECT and DELETE. Once detached, the partition (now a table in its own right) can be moved to another database or server without impacting the ingestion of more data.

One drawback: InnoDB's disk footprint is a lot more than Archive's.

What kinds of queries do you apply to the logs?

Blogs: partitioning and chunking lengthy deletes


I suggest using a combination of Percona's pt-archiver (used to archive rows from a MySQL table into another table or a file) and Percona's pt-online-schema-change (can be run "online" with a neutral ALTER to reclaim the disk space, assuming you're running with innodb_file_per_table=ON).