Re: Backup and trimming data idea
PARTITIONing...
0. Read the online docs on PARTITIONing. Read the PARTITION forum. Google it on Yahoo. Etc.
1. Decide whether it is worth doing at all. Design how to partition. See the PARTITION section of Rick's RoTs (link given above). As for "daily" vs "weekly" vs "monthly" -- I recommend no more than, say, 50 partitions in a table. Daily partitions for 2 months is ok, but stretching it. Two year's worth of data could be done in monthly, not weekly, and certainly not daily, partitions. (731 partitions is permitted, but there will be issues with such.)
2. ALTER TABLE ADD PARTITIONS... This can be done before adding data, at which point it can be part of the CREATE TABLE statement. Or at any time afterwards, but more data = longer time to do the ALTER.
3. Write a script(s) to do the periodic DROP PARTITION (to delete old data) and REORGANIZE PARTITION (to split the 'future' partition into next day/month + 'future').
Do not embark on this until you are comfortable with the steps, or the need for DELETE performance forces you into it.
Test everything on a separate copy (possibly out-of-date) of the live data. DROP PARTITION is a clear win over DELETE, but other reasons for PARTITIONing may turn out to be useless, or even hurt performance.
The PRIMARY KEY and other INDEXes may need to change when you go to PARTITIONing. This is part of the design / experimentation you need to do.
Until 5.6(?) there is no convenient way to move a single partition to another table. This might be handy for the "backup and trimming data" implied in this thread's title.