Best Practice to Archive Old Data in MySQL

Question

I'm using a MySQL database to store logs from multiple servers. The database's growth on average is 50 GB per day. Its total size is now 1.7 TB.

It has ID and datetime columns.

I want to move data older than one week to another database on the same server.

What are the steps needed for this?

As of now I do this:

1. Create the Archive Database and table.
2. Insert into ArchiveDB.table select * from old_tbl where datetime <= Archivedate
3. Then DELETE FROM old_tbl WHERE datetime <= Archivedate

I want to implement this with the following conditions:

• Insert and delete via batches.
• Reclaim the space after deleting from the old database.
• Update stats without downtime.

...Or if you know of any best practice then let me know.

Answer 1

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

Answer 2

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).