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.
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:
- Create the Archive Database and table.
Insert into ArchiveDB.table select * from old_tbl where datetime <= Archivedate
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.