Create Range Partition on existing large MySQL table

Question

I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 days can be deleted.

I'm looking to partition this table by range (preferably by day), then run an event to keep things tidy by dropping partitions outside of the retention period.

The data in my table goes back around 6 months, so the first thing I'm going to do is run a manual delete on anything older than 60 days so I''m starting with a clean slate.

Then I need to create the partitions, but do I really have to list all my partitions in the alter table statement? i.e.

ALTER TABLE AuditData
PARTITION BY RANGE( TO_DAYS(date) ) ( 
PARTITION part1 VALUES LESS THAN( TO_DAYS('2018-01-01 00:00:00') ), 
PARTITION part2 VALUES LESS THAN( TO_DAYS('2018-02-01 00:00:00') ), 
PARTITION part3 VALUES LESS THAN( TO_DAYS('2018-03-01 00:00:00') ), 
PARTITION part4 VALUES LESS THAN( TO_DAYS('2018-04-01 00:00:00') ), 
PARTITION part5 VALUES LESS THAN( TO_DAYS('2018-05-01 00:00:00') ),

... and so on

Is there a way to create the partition just by saying, partition the existing data by day?

I understand I will need to run a daily job to create a partition.

Thanks

Answer 1

Unfortunately, no shortcut.

Do have an empty partition for "future" data; REORGANIZE to tomorrow and a fresh future. More discussion.

The ALTER will copy the entire table over, so be prepared for not being able to use the table for some time.