Re: best way to take backup for old partition and create new one?

[Rick James]

For rolling date-based partitions, I like to keep an empty `future` partition, then REORGANIZE PARTITIONS to split `future` into a new partition plus an empty `future`. I do that just before the new partition is needed. This REORG will be very fast. Further details:

http://mysql.rjweb.org/doc.php/partitionmaint

As for how to schedule it...
Unix: One way is a Perl script that runs via cron.
MySQL: You could put it in an EVENT.